mssql 查询重复值,重复次数

[lioujrbang]

例如
select a,b,c,d
from TempUserusejl

我要对 abcd四个值都进行查找重复,并记录重复次数

网上大部分只是能筛选一个字段 如下:
select a,count(*)as num
from TempUserusejl
group by a

但是我想,是这样的结果
select a,count(*),b,count(*),c,count(*),d,count(*),
from TempUserusejl
group by a,b,c,d
就是四个字段都找下重复,都记录重复次数
但是这样写虽然不会报错,但是得到的结果却是错误的.
我想得到的效果图
a num b num c num d num
张 10 北京 2 电信 12 youku 5
李 8 深圳 10 联通 24 qq 9
王 7 上海 9 移动 15 aqiyi 10

求大神告知 百拜!

[lioujrbang]

顶顶顶顶顶顶顶顶顶

[中国风]

记录不一致，你只能打竖显示，横向记录只能显示记录,重复次数只显示一次
如：
e.g.

SELECT COUNT(DISTINCT a) AS aCount,COUNT(DISTINCT b) AS bCount,COUNT(DISTINCT c) AS cCount,COUNT(DISTINCT d) AS dCount FROM TempUserusejl

[中国风]

统计重复可这样显示 e.g.

SELECT a,COUNT(*) AS Count1,'a' AS type FROM TempUserusejl GROUP BY a
UNION ALL
SELECT b,COUNT(*) AS Count1,'b' AS type FROM TempUserusejl GROUP BY b
UNION ALL
SELECT c,COUNT(*) AS Count1,'c' AS type FROM TempUserusejl GROUP BY c
UNION ALL
SELECT d,COUNT(*) AS Count1,'d' AS type FROM TempUserusejl GROUP BY d

[lioujrbang]

大哥,你可能没懂我的意思,你写的得出来的是 ,每个字段不包含重复的总个数,我的想法是,字段中每个值都列出来,再在旁边显示这个值重复的次数.是不是我这种需求实现不了啊?

[卖水果的net]

建议楼主贴一些你的测试出来； 是不是想，把多个 group by 后的结果，横着拼接出来？

[lioujrbang]

引用 5 楼 wmxcn2000 的回复:

建议楼主贴一些你的测试出来； 是不是想，把多个 group by 后的结果，横着拼接出来？

下面4个 就是 4个查询出来的结果, SELECT vtype,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY vtype order by Count1 desc --606行 SELECT uAddr,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY uAddr order by Count1 desc --33行 SELECT user_IP,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY user_IP order by Count1 desc --9492行 SELECT videoId,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY videoId order by Count1 desc --11672行 然后我想把这四张列数相同,行数不同的表 横向合并, 如果不行的话,也可以把这四张表复制给四个dataset, 再把4个datatable横向合并成一个datatable

[卖水果的net]

-- 给你写个例子，效率你自己测试一下

create table test(id int identity, addr varchar(10), sex varchar(10), age int)
go
insert into test(addr, sex, age) values
('北京', '男', 20),('河北', '男', 20),('河南', '男', 50),('山东', '男', 21),
('北京', '女', 30),('辽宁', '女', 20),('河南', '男', 33),('山东', '男', 20),
('北京', '女', 35),('河北', '男', 25),('山西', '女', 33),('山东', '男', 33),
('北京', '男', 35),('河北', '男', 24),('河南', '男', 20),('山东', '女', 20)
go
with m1 as (
select row_number() over(order by count(*) desc) rn1,addr, count(*) c1 
from test group by addr
),
m2 as (
select row_number() over(order by count(*) desc) rn2,sex, count(*) c2
from test group by sex
),
m3 as(
select row_number() over(order by count(*) desc) rn3,age, count(*) c3
from test group by age
)
select coalesce(m1.rn1, m2.rn2, m3.rn3) rn, 
       m1.addr, m1.c1, m2.sex, m2.c2, m3.age, m3.c3
from m1 
full join m2 on m1.rn1 = m2.rn2
full join m3 on coalesce(m1.rn1, m2.rn2) = m3.rn3
go
drop table test 
go

(16 行受影响)
rn                   addr       c1          sex        c2          age         c3
-------------------- ---------- ----------- ---------- ----------- ----------- -----------
1                    北京         4           男          11          20          6
2                    山东         4           女          5           33          3
3                    河北         3           NULL       NULL        35          2
4                    河南         3           NULL       NULL        50          1
5                    辽宁         1           NULL       NULL        21          1
6                    山西         1           NULL       NULL        24          1
7                    NULL       NULL        NULL       NULL        25          1
8                    NULL       NULL        NULL       NULL        30          1

(8 行受影响)

[中国风]

引用 4 楼 u012533386 的回复:

大哥,你可能没懂我的意思,你写的得出来的是 ,每个字段不包含重复的总个数,我的想法是,字段中每个值都列出来,再在旁边显示这个值重复的次数.是不是我这种需求实现不了啊?

上面是竖向显赫，横向这样实现效率低 e.g.

;WITH CTET1
AS
(
SELECT *,RN=ROW_NUMBER()OVER(ORDER BY Count1 DESC),Type='a' FROM (SELECT vtype,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY vtype ) AS t --606行
),CTET2
AS
(
SELECT *,RN=ROW_NUMBER()OVER(ORDER BY Count1 DESC),Type='b' FROM (SELECT uAddr,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY uAddr ) AS t
),CTET3 --33行
AS (
SELECT * ,RN=ROW_NUMBER()OVER(ORDER BY Count1 DESC),Type='c'  FROM (SELECT user_IP,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY user_IP) AS t )
,CTET4 AS (  --9492行

SELECT *,RN=ROW_NUMBER()OVER(ORDER BY Count1 DESC),Type='d'  FROM (SELECT videoId,COUNT(*) AS Count1 FROM TempUserusejl GROUP BY videoId ) AS t 
) --11672行
,CTET
AS
(
SELECT * FROM CTET1
UNION ALL 
SELECT * FROM CTET2
UNION ALL 
SELECT * FROM CTET3
UNION ALL 
SELECT * FROM CTET4
)
SELECT max(CASE WHEN Type='a' THEN  vtype ELSE '' END) AS a,max(CASE WHEN Type='a' THEN  Count1 ELSE 0 END) AS aNum,
max(CASE WHEN Type='b' THEN  vtype ELSE '' END) AS b,max(CASE WHEN Type='b' THEN  Count1 ELSE 0 END) AS bNum,
max(CASE WHEN Type='c' THEN  vtype ELSE '' END) AS c,max(CASE WHEN Type='c' THEN  Count1 ELSE 0 END) AS cNum,
max(CASE WHEN Type='d' THEN  vtype ELSE '' END) AS d,max(CASE WHEN Type='d' THEN  Count1 ELSE 0 END) AS dNum
FROM CTET 
GROUP BY RN