数据:
class, student
---------------
1,a
1,b
1,a
2,c
2,a
2,b
3,a
3,a
3,c
统计student在class中出现的次数,同个class多次出现,算1次,
结果应该为:
a,3
b,2
c,2SQL咋写?
class, student
---------------
1,a
1,b
1,a
2,c
2,a
2,b
3,a
3,a
3,c
统计student在class中出现的次数,同个class多次出现,算1次,
结果应该为:
a,3
b,2
c,2SQL咋写?
AS (SELECT 1 class, 'a' student
UNION ALL
SELECT 1,'b'
UNION ALL
SELECT 1,'a'
UNION ALL
SELECT 2,'c'
UNION ALL
SELECT 2,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'a'
UNION ALL
SELECT 3,'a'
UNION ALL
SELECT 3,'c')
SELECT student,COUNT(1) [次数]
FROM (
SELECT * FROM test GROUP BY class,student) a GROUP BY student
/*
student 次数
------- -----------
a 3
b 2
c 2
(3 行受影响)
*/
CREATE TABLE BT
(
class varchar(10),
student varchar(10)
)
INSERT INTO BT
SELECT '1','a' UNION ALL
SELECT '1','b' UNION ALL
SELECT '1','a' UNION ALL
SELECT '2','c' UNION ALL
SELECT '2','a' UNION ALL
SELECT '2','b' UNION ALL
SELECT '3','a' UNION ALL
SELECT '3','a' UNION ALL
SELECT '3','c'select T.student,Count(T.student) CountStudent from (select Distinct class,student from BT) T group by T.student
/*
student CountStudent
---------- ------------
a 3
b 2
c 2(3 行受影响)
*/
SELECT student,COUNT(*)
FROM (SELECT * FROM tb GROUP BY class,student) t
GROUP BY student+1
go
create table [tb]([class] int,[student] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 1,'a' union all
select 2,'c' union all
select 2,'a' union all
select 2,'b' union all
select 3,'a' union all
select 3,'a' union all
select 3,'c'
goSELECT STUDENT,COUNT(DISTINCT CLASS) AS CNT
FROM TB
GROUP BY STUDENT/**
STUDENT CNT
------- -----------
a 3
b 2
c 2(3 行受影响)
**/
AS (
SELECT 1 class, 'a' student
UNION ALL
SELECT 1,'b'
UNION ALL
SELECT 1,'a'
UNION ALL
SELECT 2,'c'
UNION ALL
SELECT 2,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'a'
UNION ALL
SELECT 3,'a'
UNION ALL
SELECT 3,'c')
,c1 AS
(
SELECT DISTINCT class,student --去掉重复记录
FROM test
)
SELECT student, COUNT(student) 次数
FROM c1
GROUP BY c1.student---看不见SQL code的图标了,只能这样了