基于计算列的cte筛选结果集

7kqas0il 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(289)

我试图找到在给定数据库的多个表中出现的列。我已经成功地生成了一个结果集，它给出了列名、表名和额外的秩列，并为多个引用提供了一个标志（0或1）。
我只是想知道是否有可能根据上一栏过滤这个cte

;with CTE as
(
    SELECT c.name AS ColName, t.name AS TableName, ROW_NUMBER() over (partition by c.name order by c.name) as NumberofOcuurance
    FROM sys.columns c
    JOIN sys.tables t 
    ON c.object_id = t.object_id    
)    
select *, 
    case
    when    LEAD(NumberofOcuurance,1) over (order by colName) = NumberofOcuurance then  0
    when    LEAD(NumberofOcuurance,1) over (order by colName) <> NumberofOcuurance then  1
    End as Morethan1    
from CTE

sql sql-server

来源：https://stackoverflow.com/questions/63257279/filter-result-set-of-a-cte-based-on-a-computed-column

2条答案

按热度按时间

nlejzf6q1#

只需使用子查询/派生表，例如。

WITH cte AS (
    SELECT c.[name] AS ColName, t.[name] AS TableName, ROW_NUMBER() OVER (PARTITION BY c.[name] ORDER BY c.[name]) AS NumberofOccurrance
    FROM sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
)
SELECT *
FROM (
    SELECT * 
        , CASE
        WHEN LEAD(NumberofOccurrance, 1) OVER (ORDER BY colName) = NumberofOccurrance THEN 0
        WHEN LEAD(NumberofOccurrance, 1) OVER (ORDER BY colName) <> NumberofOccurrance THEN 1
        END AS Morethan1
    FROM cte 
) x
WHERE x.Morethan1 = 1;

赞(0）回复(0）举报 2021-07-24

rfbsl7qr2#

为什么不直接使用 information_schema 意见？

select column_name, string_agg(table_name, ',') as table_names
from information_schema.columns
group by column_name
having count(*) > 1

赞(0）回复(0）举报 2021-07-24

我来回答

基于计算列的cte筛选结果集

2条答案

相关问题

热门标签

最新问答