22,210
社区成员
发帖
与我相关
我的任务
分享SQL繁琐的 COUNT语句，重复出现次数统计
如何完成学生[6次语文成绩]成绩表里 各个分数的重复出现次数统计
if object_id('tempdb..#test1') is not null drop table #test1
go
create table #test1([姓名] nvarchar(20),[考号] nvarchar(20),[6次语文成绩] nvarchar(50))
insert #test1 --- select * from #test1
select '陈涛','L8001','68 7 28 14 44 16' union all
select '成晓红','L8002','96 98 105 93 87 97' union all
select '杜凤至','L8003','88 92 81 70 80 94' union all
select '冯佳','L8004','86 61 77 33 75 78' union all
select '冯永康','L8005','77 70 52 63 65 60' union all
select '高鹏举','L8006','87 80 41 65 68 73' union all
select '高文博','L8007','62 12 31 26 46 18' union all
select '郭彦愚','L8008','85 57 90 60 72 82' union all
select '何耀武','L8009','74 26 44 34 57 69' union all
select '李彩霞','L8010','62 0 43 20 26 10' union all
select '李婷婷','L8011','90 32 75 28 57 46' union all
select '李文娥','L8012','81 21 71 26 72 60' union all
select '李文娜','L8013','83 23 84 28 75 60' union all
select '李园霞','L8014','85 53 89 37 68 80' union all
select '梁侠','L8015','91 29 87 42 75 71' union all
select '梁云霞','L8016','85 45 60 45 75 48' union all
select '刘丽霞','L8017','86 101 97 73 71 91' union all
select '刘英杰','L8018','75 69 49 31 71 56' union all
select '刘永强','L8019','63 18 26 23 42 32' union all
select '马斌','L8020','58 18 28 22 40 22' union all
select '倪金兰','L8021','90 43 90 52 61 86' union all
select '倪金曌','L8022','77 10 76 24 68 76' union all
select '牛虎斌','L8023','83 44 34 48 58 60' union all
select '庞勇安','L8024','49 3 17 12 34 21' union all
select '王龙','L8025','84 100 83 53 83 77' union all
select '王蓉娜','L8026','61 22 30 28 67 30' union all
select '王涛','L8027','95 85 81 63 72 91' union all
select '王文霞','L8028','89 89 59 55 62 66' union all
select '王艳','L8029','86 26 34 33 64 55' union all
select '王艳霞','L8030','91 67 84 70 74 78' union all
select '效志有','L8031','66 32 33 32 64 30' union all
select '辛艳红','L8032','94 88 103 68 67 96' union all
select '杨博','L8033','80 64 75 49 66 55' union all
select '杨莉','L8034','91 34 94 33 71 80' union all
select '姚文霞','L8035','68 22 49 16 55 28' union all
select '袁存辉','L8036','75 18 44 28 65 29'
举例：结果的格式
/*
分数 重复次数
-----------------
103 3
94 9
90 11
89
88 2
87
85 9
84 11
83
81
80 17
78 22
*/
...全文
请发表友善的回复…
发表回复
RICHEER COCA 2016-04-06
- 打赏
- 举报
谢谢楼上所有的大神们
学习提高中，受益匪浅，结贴ing
学习提高中，受益匪浅，结贴ingRICHEER COCA 2016-04-06
- 打赏
- 举报
谢谢！受益匪浅
唐诗三百首 2016-04-06
- 打赏
- 举报
with t as
(select cast(substring(a.[6次语文成绩],
b.number,
charindex(N' ',a.[6次语文成绩]+N' ',b.number)-b.number) as int) 'score'
from #test1 a
inner join master.dbo.spt_values b on b.number between 1 and len(a.[6次语文成绩])
and substring(N' '+a.[6次语文成绩],b.number,1)=N' '
where b.[type]=N'P')
select '分数'=score,
'重复次数'=count(1)
from t
group by score
order by score desc
/*
分数 重复次数
----------- -----------
105 1
103 1
101 1
100 1
98 1
97 2
96 2
95 1
94 3
93 1
92 1
91 5
90 4
89 3
88 2
87 3
86 4
85 4
84 3
83 4
82 1
81 3
80 5
78 2
77 4
76 2
75 8
74 2
73 2
72 3
71 5
70 3
69 2
68 6
67 3
66 3
65 3
64 3
63 3
62 3
61 3
60 6
59 1
58 2
57 3
56 1
55 4
53 2
52 2
49 4
48 2
46 2
45 2
44 4
43 2
42 2
41 1
40 1
37 1
34 5
33 4
32 4
31 2
30 3
29 2
28 7
26 6
24 1
23 2
22 4
21 2
20 1
18 4
17 1
16 2
14 1
12 2
10 2
7 1
3 1
0 1
(81 row(s) affected)
*/
spiritofdragon 2016-04-06
- 打赏
- 举报
这是递归写法,union all 前后保持字段一致.
倒数第二列是取出本次分数
倒数第一列是取字符串剩余部分
递归条件:递归到剩余字符串中没有空格为止.
RICHEER COCA 2016-04-06
- 打赏
- 举报
先说声谢谢，请教，这句代码没看懂
select t.*
,LEFT(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)-1)
,SUBSTRING(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)+1,50)
from cte
RICHEER COCA 2016-04-06
- 打赏
- 举报
lz是2008版本，先说声谢谢，请教，如何得到正确的输出格式
/*
分数 重复次数
-----------------------------
105 1
103 1
101 1
100 1
98 1
97 2
96 2
95 1
94 3
93 1
92 1
91 5
90 4
89 3
88 2
*/
spiritofdragon 2016-04-06
- 打赏
- 举报
;
with cte as (
select *
,CAST(N'' AS NVARCHAR(50)) CurrStr
,CAST([6次语文成绩]+' ' AS NVARCHAR(50)) LeftStr
from #test1 t
union all
select t.*
,LEFT(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)-1)
,SUBSTRING(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)+1,50)
from cte join #test1 t on cte.考号=t.考号 and CHARINDEX(' ',cte.LeftStr)>0
)
select CAST(CurrStr as int) 分数,COUNT(1) 重复次数
from cte WHERE CurrStr>''
group by CAST(CurrStr as int)
order by CAST(CurrStr as int) desc
Mr_Nice 2016-04-06
- 打赏
- 举报
;
WITH TT
AS ( SELECT A.考号 ,
B.value
FROM ( SELECT 考号 ,
[value] = CONVERT(XML, '<root><v>'
+ REPLACE([6次语文成绩], ' ', '</v><v>')
+ '</v></root>')
FROM dbo.test1
) A
OUTER APPLY ( SELECT [value] = N.v.[value]('.',
'varchar(100)')
FROM A.[value].nodes('/root/v') N ( v )
) B
)
SELECT value ,
COUNT(1) AS 次数
FROM TT
GROUP BY value
ORDER BY value desc;Mr_Nice 2016-04-06
- 打赏
- 举报
[code=c];
WITH TT
AS ( SELECT A.考号 ,
B.value
FROM ( SELECT 考号 ,
[value] = CONVERT(XML, '<root><v>'
+ REPLACE([6次语文成绩], ' ', '</v><v>')
+ '</v></root>')
FROM dbo.test1
) A
OUTER APPLY ( SELECT [value] = N.v.[value]('.',
'varchar(100)')
FROM A.[value].nodes('/root/v') N ( v )
) B
)
SELECT value ,
COUNT(1) AS 次数
FROM TT
GROUP BY value
ORDER BY value desc;
/*
value 次数
98 1
97 2
96 2
95 1
94 3
93 1
92 1
91 5
90 4
89 3
88 2
87 3
86 4
85 4
84 3
83 4
82 1
81 3
80 5
78 2
77 4
76 2
75 8
74 2
73 2
72 3
71 5
70 3
7 1
69 2
68 6
67 3
66 3
65 3
64 3
63 3
62 3
61 3
60 6
59 1
58 2
57 3
56 1
55 4
53 2
52 2
49 4
48 2
46 2
45 2
44 4
43 2
42 2
41 1
40 1
37 1
34 5
33 4
32 4
31 2
30 3
3 1
29 2
28 7
26 6
24 1
23 2
22 4
21 2
20 1
18 4
17 1
16 2
14 1
12 2
105 1
103 1
101 1
100 1
10 2
0 1*/
