34,593
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[a] int,[b] int,[c] int,[d] int)
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
select ids,count(*)
from #t unpivot ( ids for val in([a],[b],[c],[d])) pt
where ids in(select id from #t)
group by ids
ids counts
----------- -----------
123 3
126 2
SELECT ID,COUNT(*) AS AMOUNT
FROM
(SELECT A AS ID FROM TABLE
UNION ALL
SELECT B FROM TABLE
UNION ALL
SELECT C FROM TABLE
UNION ALL
SELECT D FROM TABLE) AS A
WHERE A.ID IN
(SELECT ID FROM TABLE)
GROUP BY ID
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id INT,a INT,b INT,c INT,d INT)
GO
INSERT INTO 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'
SELECT col AS [value],COUNT(1) AS cnt FROM (
SELECT id,a AS col FROM t
UNION ALL
SELECT id,b AS col FROM t
UNION ALL
SELECT id,c AS col FROM t
UNION ALL
SELECT id,d AS col FROM t
) AS tt
GROUP BY col
ORDER BY cnt
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[a] int,[b] int,[c] int,[d] int)
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
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
) 重复次数
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
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,
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