基于计算列筛选CTE的结果集
基于计算列筛选CTE的结果集
提问于 2020-08-05 09:48:13
我正在尝试查找在给定数据库的多个表中出现的列。我已经成功地提出了一个结果集，该结果集给出了列名、表名和额外的排名列，并为多次出现提供了一个标志(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 回答 2
Stack Overflow用户
回答已采纳
发布于 2020-08-05 09:50:32
只需使用子查询/派生表，例如
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;Stack Overflow用户
发布于 2020-08-05 09:50:40
为什么不直接使用information_schema视图呢？
select column_name, string_agg(table_name, ',') as table_names
from information_schema.columns
group by column_name
having count(*) > 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63257279
复制相关文章
相似问题
腾讯云开发者
