34,591
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id VARCHAR(10),姓名 NVARCHAR(10),a INT,b INT,c INT,d INT,甲 NVARCHAR(10),乙 NVARCHAR(10))
GO
INSERT INTO t
SELECT '123','张三','555','666','777','888','爸爸','公司'
UNION SELECT '124','李四','123','126','777','888','爸爸','学校'
UNION SELECT '125','王五','123','999','126','555','阿姨','国家'
UNION SELECT '126','赵六','555','123','777','888','妈妈','国家'
UNION SELECT '127','钱八','444','445','446','447','儿子','城市'
;WITH cte AS (
SELECT a AS col FROM t
UNION ALL
SELECT b AS col FROM t
UNION ALL
SELECT c AS col FROM t
UNION ALL
SELECT d AS col FROM t
)
SELECT t.id
,t.姓名
,(SELECT COUNT(1) FROM cte WHERE cte.col=t.id) AS [id重复次数]
,(SELECT COUNT(1) FROM t AS b WHERE b.甲=t.甲)-1 AS [甲字段重复次数]
,(SELECT COUNT(1) FROM t AS b WHERE b.乙=t.乙)-1 AS [乙字段重复次数]
FROM t
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(id int,name varchar(10),a int,b int,c int,d int,e varchar(10),f varchar(10))
Insert #T
select 123,'张三',555,666,777,888,'爸爸','公司' union all
select 124,'李四',123,126,777,888,'爸爸','学校' union all
select 125,'王五',123,999,126,555,'阿姨','国家' union all
select 126,'赵六',555,123,777,888,'妈妈','国家' union all
select 127,'钱八',333,445,446,447,'儿子','城市'
Go
SELECT A.ID,A.NAME,B.QTY,A.E_FREQUENCY-1,A.F_FREQUENCY-1
FROM
(SELECT *,
COUNT(1) OVER (PARTITION BY e) AS E_FREQUENCY,
COUNT(1) OVER (PARTITION BY f) AS F_FREQUENCY
FROM #T) AS A
LEFT JOIN
(SELECT ID,COUNT(*) AS QTY
FROM
(SELECT A AS ID FROM #T
UNION ALL
SELECT B FROM #T
UNION ALL
SELECT C FROM #T
UNION ALL
SELECT D FROM #T) AS A
GROUP BY ID) AS B ON A.ID=B.ID
WHERE ISNULL(B.ID,'')<>''
OR E_FREQUENCY>1
OR F_FREQUENCY>1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[姓名] nvarchar(22),[a] int,[b] int,[c] int,[d] int,[甲] nvarchar(22),[乙] nvarchar(22))
Insert #T
select 123,N'张三',555,666,777,888,N'爸爸',N'公司' union all
select 124,N'李四',123,126,777,888,N'爸爸',N'学校' union all
select 125,N'王五',123,999,126,555,N'阿姨',N'国家' union all
select 126,N'赵六',555,123,777,888,N'妈妈',N'国家' union all
select 127,N'钱八',444,445,446,447,N'儿子',N'城市'
Go
--测试数据结束
SELECT
t1.id,
t1.姓名,
SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
) 重复次数,
SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 甲字段重复次数,
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 乙字段重复次数
FROM
#T t1,
#T t2
GROUP BY
t1.id,t1.姓名
HAVING SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
)>0 OR SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0 OR
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0
ORDER BY
t1.id
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[姓名] nvarchar(22),[a] int,[b] int,[c] int,[d] int,[甲] nvarchar(22),[乙] nvarchar(22))
Insert #T
select 123,N'张三',555,666,777,888,N'爸爸',N'公司' union all
select 124,N'李四',123,126,777,888,N'爸爸',N'学校' union all
select 125,N'王五',123,999,126,555,N'阿姨',N'国家' union all
select 126,N'赵六',555,123,777,888,N'妈妈',N'国家' union all
select 127,N'钱八',444,445,446,447,N'儿子',N'城市'
Go
--测试数据结束
SELECT
t1.id,
t1.姓名,
SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
) 重复次数,
SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 甲字段重复次数,
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 乙字段重复次数
FROM
#T t1,
#T t2
GROUP BY
t1.id,t1.姓名
HAVING SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
)>0 OR SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0 OR
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0
ORDER BY
t1.id
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id VARCHAR(10),姓名 NVARCHAR(10),a INT,b INT,c INT,d INT,甲 NVARCHAR(10),乙 NVARCHAR(10))
GO
INSERT INTO t
SELECT '123','张三','555','666','777','888','爸爸','公司'
UNION SELECT '124','李四','123','126','777','888','爸爸','学校'
UNION SELECT '125','王五','123','999','126','555','阿姨','国家'
UNION SELECT '126','赵六','555','123','777','888','妈妈','国家'
UNION SELECT '127','钱八','444','445','446','447','儿子','城市'
;WITH cte AS (
SELECT a AS col FROM t
UNION ALL
SELECT b AS col FROM t
UNION ALL
SELECT c AS col FROM t
UNION ALL
SELECT d AS col FROM t
)
,cte2 AS (
SELECT t.id
,t.姓名
,(SELECT COUNT(1) FROM cte WHERE cte.col=t.id) AS [id重复次数]
,(SELECT COUNT(1) FROM t AS b WHERE b.甲=t.甲)-1 AS [甲字段重复次数]
,(SELECT COUNT(1) FROM t AS b WHERE b.乙=t.乙)-1 AS [乙字段重复次数]
FROM t
)
SELECT * FROM cte2
WHERE [id重复次数]>0 OR [甲字段重复次数]>0 OR [乙字段重复次数]>0
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[姓名] nvarchar(22),[a] int,[b] int,[c] int,[d] int,[甲] nvarchar(22),[乙] nvarchar(22))
Insert #T
select 123,N'张三',555,666,777,888,N'爸爸',N'公司' union all
select 124,N'李四',123,126,777,888,N'爸爸',N'学校' union all
select 125,N'王五',123,999,126,555,N'阿姨',N'国家' union all
select 126,N'赵六',555,123,777,888,N'妈妈',N'国家' union all
select 127,N'钱八',444,445,446,447,N'儿子',N'城市'
Go
--测试数据结束
SELECT
t1.id,
SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
) 重复次数,
SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 甲字段重复次数,
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
) 乙字段重复次数
FROM
#T t1,
#T t2
GROUP BY
t1.id
HAVING SUM( CASE
WHEN t1.id = t2.a
OR t1.id = t2.b
OR t1.id = t2.c
OR t1.id = t2.d
THEN 1
ELSE
0
END
)>0 OR SUM( CASE
WHEN t1.甲 = t2.甲
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0 OR
SUM( CASE
WHEN t1.乙 = t2.乙
AND t1.id <> t2.id
THEN 1
ELSE
0
END
)>0
ORDER BY
t1.id