求条统计SQL

Question

create  table tb 
(
classId int ,
className varchar(20),
StudentName varchar(20),
StudentType int --1 表示提前到场   2  正常到场   3  迟到  4  缺场
)
insert into tb select '1','班级1','张三001',1
insert into tb select '1','班级1','张三002',1
insert into tb select '1','班级1','张三003',2
insert into tb select '1','班级1','张三004',3insert into tb select '2','班级2','李四001',1
insert into tb select '2','班级2','李四002',2
insert into tb select '2','班级2','李四003',3
insert into tb select '2','班级2','李四004',2
insert into tb select '2','班级2','李四005',4create table tbtype
(
tid int,
tname varchar(20)
)
insert into tbtype select '1','提前到场'
insert into tbtype select '2','正常到场'
insert into tbtype select '3','迟到'
insert into tbtype select '4','缺场'
--想统计的结果是 一个班里面有多少个提前到场 多少个正常到场 多少迟到 多少没有到
--结果
班级名称 提前到场 正常到场 迟到 缺场
班级1 2 1 3 0
班级2 1 2 1 1

Answer 1

create  table tb 
(
    classId int ,
    className varchar(20),
    StudentName varchar(20),
    StudentType    int            --1 表示提前到场   2  正常到场   3  迟到  4  缺场
)
insert into tb select '1','班级1','张三001',1
insert into tb select '1','班级1','张三002',1
insert into tb select '1','班级1','张三003',2
insert into tb select '1','班级1','张三004',3insert into tb select '2','班级2','李四001',1
insert into tb select '2','班级2','李四002',2
insert into tb select '2','班级2','李四003',3
insert into tb select '2','班级2','李四004',2
insert into tb select '2','班级2','李四005',4create table tbtype
(
    tid int,
    tname varchar(20)    
)
insert into tbtype select '1','提前到场'
insert into tbtype select '2','正常到场'
insert into tbtype select '3','迟到'
insert into tbtype select '4','缺场'
--动态
declare @str varchar(2000)
set @str=''
select   
    @str=@str+','+tname+'=sum( case when tname='+QUOTENAME(tname,'''')
    +' then 1 else 0 end)'
from
    (
select 
     * 
from 
     tb a 
inner join 
     tbtype b 
on 
     a.StudentType=b.tid
    )t
group by
     tname
 print @str
exec('select className'+@str+'
from (
select 
     * 
from 
     tb a 
inner join 
     tbtype b 
on 
     a.StudentType=b.tid
)t
group by className
')
/*
className 迟到 缺场 提前到场 正常到场
-----------------------------------------------------
班级1 1 0 2 1
班级2 1 1 1 2
*/

Answer 2

declare @sql varchar(max)set @sql='select classname '
select @sql=@sql+',isnull(sum(case tname when '''+tname+''' then 1 end),0) as ['+tname+']'
from tbtype 
set @sql=@sql+'from (select * from tb a join tbtype b on a.StudentType=b.tid) a group by classname'
print @sql
exec(@sql)

Answer 3

declare @s varchar(8000)select @s = isnull(@s + ',','')+ '['+ltrim(tname)+']' 
from (select tname from tbtype  ) a exec('SELECT classId,className,'+@s+' 
FROM  
   (select classid,classname,tname,count(1) as cnt 
    from tb 
        left join tbtype on StudentType = tid
    group by classid,classname,tname)  p 
PIVOT 
   ( sum (cnt)  
    FOR tname IN ('+@s+') 
   )AS unpvt') 

Answer 4

--班级名称 提前到场 正常到场 迟到 缺场
--班级1 2 1 3 0
--班级2 1 2 1 1
IF object_id('tb ')IS NOT NULL
DROP TABLE tb 


create  table tb 
(
    classId int ,
    className varchar(20),
    StudentName varchar(20),
    StudentType    int            --1 表示提前到场   2  正常到场   3  迟到  4  缺场
)
insert into tb select '1','班级1','张三001',1
insert into tb select '1','班级1','张三002',1
insert into tb select '1','班级1','张三003',2
insert into tb select '1','班级1','张三004',3insert into tb select '2','班级2','李四001',1
insert into tb select '2','班级2','李四002',2
insert into tb select '2','班级2','李四003',3
insert into tb select '2','班级2','李四004',2
insert into tb select '2','班级2','李四005',4IF object_id('tbtype')IS NOT NULL
DROP TABLE tbtype
create table tbtype
(
    tid int,
    tname varchar(20)    
)
insert into tbtype select '1','提前到场'
insert into tbtype select '2','正常到场'
insert into tbtype select '3','迟到'
insert into tbtype select '4','缺场'
;WITH c AS(
SELECT a.classId,b.tname FROM tb AS a
INNER JOIN tbtype AS b ON a.StudentType=b.tid
)
SELECT 
classId
,提前到场
,正常到场
,迟到
,缺场 
FROM c
PIVOT (
COUNT(tname) FOR tname IN(提前到场,正常到场,迟到,缺场)
) p

Answer 5

--班级名称 提前到场 正常到场 迟到 缺场
--班级1 2 1 3 0
--班级2 1 2 1 1
IF object_id('tb ')IS NOT NULL
DROP TABLE tb 


create  table tb 
(
    classId int ,
    className varchar(20),
    StudentName varchar(20),
    StudentType    int            --1 表示提前到场   2  正常到场   3  迟到  4  缺场
)
insert into tb select '1','班级1','张三001',1
insert into tb select '1','班级1','张三002',1
insert into tb select '1','班级1','张三003',2
insert into tb select '1','班级1','张三004',3insert into tb select '2','班级2','李四001',1
insert into tb select '2','班级2','李四002',2
insert into tb select '2','班级2','李四003',3
insert into tb select '2','班级2','李四004',2
insert into tb select '2','班级2','李四005',4IF object_id('tbtype')IS NOT NULL
DROP TABLE tbtype
create table tbtype
(
    tid int,
    tname varchar(20)    
)
insert into tbtype select '1','提前到场'
insert into tbtype select '2','正常到场'
insert into tbtype select '3','迟到'
insert into tbtype select '4','缺场'
;WITH c AS(
SELECT a.classId,b.tname FROM tb AS a
INNER JOIN tbtype AS b ON a.StudentType=b.tid
)
SELECT 
classId
,提前到场
,正常到场
,迟到
,缺场 
FROM c
PIVOT (
COUNT(tname) FOR tname IN(提前到场,正常到场,迟到,缺场)
) p

Answer 6

create table class 
(
    classId int ,
    className varchar(20),
    StudentName varchar(20),
    StudentType    int            --1 表示提前到场   2  正常到场   3  迟到  4  缺场
)
insert into class select '1','班级1','张三001',1
insert into class select '1','班级1','张三002',1
insert into class select '1','班级1','张三003',2
insert into class select '1','班级1','张三004',3insert into class select '2','班级2','李四001',1
insert into class select '2','班级2','李四002',2
insert into class select '2','班级2','李四003',3
insert into class select '2','班级2','李四004',2
insert into class select '2','班级2','李四005',4create table tbtype
(
    tid int,
    tname varchar(20)    
)
insert into tbtype select '1','提前到场'
insert into tbtype select '2','正常到场'
insert into tbtype select '3','迟到'
insert into tbtype select '4','缺场';WITH t AS
(
SELECT * FROM class INNER JOIN tbtype ON class.StudentType=tbtype.tid
)
,m AS
(
SELECT className,[提前到场],[正常到场],[迟到],[缺场]
FROM t 
PIVOT
(
count(tname) FOR tname IN([提前到场],[正常到场],[迟到],[缺场])
)AS p
)
SELECT className,SUM([提前到场]) AS [提前到场],sum([正常到场]) AS [正常到场],sum([迟到]) AS [迟到],sum([缺场]) AS                  [缺场]
FROM m
GROUP BY className