SQL繁琐的 COUNT语句，重复出现次数统计

2016-04-05 11:01:38 660 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

别再手动数数了！用R语言dplyr的count函数，5分钟搞定数据频次统计本文介绍了R语言dplyr包中的count函数如何快速实现数据频次统计，替代传统手动计数方法。通过基础用法、进阶技巧和真实案例，展示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

*/

...全文

手把手教你用Microsoft AEC Challenge数据集训练自己的回声消除模型（附完整代码）本文详细介绍了如何使用Microsoft AEC Challenge数据集训练专业的回声消除模型，包括环境准备、数据清洗、模型架构设计、训练流程和实际部署注意事项。通过完整的代码示例和实战指南，帮助开发者快速掌握AEC技术，提升语音通信系统的回声消除效果。阅读详情

RICHEER COCA

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

RICHEER COCA 2016-04-06

打赏
举报

谢谢楼上所有的大神们

学习提高中，受益匪浅，结贴ing

RICHEER COCA 2016-04-06

打赏
举报

引用 6 楼 spiritofdragon 的回复:

这是递归写法,union all 前后保持字段一致. 倒数第二列是取出本次分数倒数第一列是取字符串剩余部分递归条件:递归到剩余字符串中没有空格为止.

谢谢！受益匪浅

唐诗三百首 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

打赏
举报

引用 3 楼 spiritofdragon 的回复:

;
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

先说声谢谢，请教，这句代码没看懂


select t.*
	,LEFT(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)-1)
	,SUBSTRING(cte.LeftStr,CHARINDEX(' ',cte.LeftStr)+1,50)
from cte

RICHEER COCA 2016-04-06

打赏
举报

引用 2 楼 OrchidCat 的回复:

;
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;

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;

格式掉了，另外这个在2005以上版本使用。如果lz是2005一下版本，可以搜索一下分割列。就那么几种方法

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*/

SQL繁琐的 COUNT语句，重复出现次数统计

相关推荐