表结构内容列1 列2 列3 列4 列5 列6
1 1 1 2 1 1
1 1 1 2 1 1
1 2 1 2 1 1
如何可以过滤出 列1 列2 列3 列4 列5 列6 值相同的记录
目标结果是列1 列2 列3 列4 列5 列6
1 1 1 2 1 1
1 1 1 2 1 1
1 1 1 2 1 1
1 1 1 2 1 1
1 2 1 2 1 1
如何可以过滤出 列1 列2 列3 列4 列5 列6 值相同的记录
目标结果是列1 列2 列3 列4 列5 列6
1 1 1 2 1 1
1 1 1 2 1 1
试试这个?
WITH CTE
AS
(
SELECT *
FROM 表
GROUP BY 列1,列2,列3,列4,列5,列6
HAVING COUNT(1)=1
)
SELECT * FROM 表 EXCEPT CTE
WITH a1 (col1,col2,col3,col4,col5,col6) AS
(
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 2, 1, 2, 1, 1
)
,a2 AS
(
SELECT *
FROM a1
GROUP BY col1,col2,col3,col4,col5,col6
HAVING COUNT(*)>1
)
SELECT a.*
FROM a1 a
JOIN a2 b ON a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.col4=b.col4 and a.col5=b.col5 and a.col6=b.col6
--或者
WITH a1 (col1,col2,col3,col4,col5,col6) AS
(
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 2, 1, 2, 1, 1
)
,a2 AS
(
SELECT *,DENSE_RANK() OVER(ORDER BY col1,col2,col3,col4,col5,col6) re
FROM a1
)
,a3 AS
(
SELECT MAX(re) re
FROM a2
GROUP BY col1,col2,col3,col4,col5,col6
HAVING COUNT(*)>1
)
SELECT col1,col2,col3,col4,col5,col6
FROM a2 a
JOIN a3 b ON a.re=b.re
(
select
*
from
tb
group BY
列1,列2,列3,列4,列5,列6
having count(1)>1)SELECT * FROM TB WHERE CHECKSUM(*) IN (SELECT CHECKSUM(*) FROM f)
;WITH a1 (col1,col2,col3,col4,col5,col6) AS
(
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 1, 1, 2, 1, 1 UNION ALL
SELECT 1, 2, 1, 2, 1, 1
)
,a2 AS
(
SELECT *,
count(*) over(partition by col1,col2,col3,col4,col5,col6) as rownum
FROM a1)select col1,col2,col3,col4,col5,col6
from a2
where rownum >= 2
/*
col1 col2 col3 col4 col5 col6
1 1 1 2 1 1
1 1 1 2 1 1
*/