34,590
社区成员
发帖
与我相关
我的任务
分享求条统计SQL
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',3
insert 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',4
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','缺场'--想统计的结果是 一个班里面有多少个提前到场 多少个正常到场 多少迟到 多少没有到
--结果
班级名称 提前到场 正常到场 迟到 缺场
班级1 2 1 3 0
班级2 1 2 1 1
...全文
请发表友善的回复…
发表回复
我腫了 2012-05-15
- 打赏
- 举报
--班级名称 提前到场 正常到场 迟到 缺场
--班级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',3
insert 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',4
IF 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
我腫了 2012-05-15
- 打赏
- 举报
--班级名称 提前到场 正常到场 迟到 缺场
--班级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',3
insert 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',4
IF 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
-狙击手- 2012-05-15
- 打赏
- 举报
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')
十三门徒 2012-05-15
- 打赏
- 举报
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)
开着拖拉机泡妞 2012-05-15
- 打赏
- 举报
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',3
insert 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',4
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','缺场'
--动态
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
*/
fengyufengxinzi 2012-05-15
- 打赏
- 举报
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',3
insert 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',4
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 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
