CREATE TABLE [test]
([ywSJ] [datetime] NOT NULL ,
[XinXiLY] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )
insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','老师'
insert into test select '2012-01-01','老师'
insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','学生'
insert into test select '2012-01-02','学生'
insert into test select '2012-01-02','老师'
insert into test select '2012-01-02','老师'
insert into test select '2012-01-02','学生'
insert into test select '2012-01-02','工人'
insert into test select '2012-01-02','工人'
insert into test select '2012-01-03','学生'
insert into test select '2012-01-03','工人'
insert into test select '2012-01-03','学生'
select * from test
select convert(char(10),ywsj,120) as 业务日期,ISNULL(XinXiLY,'') AS 来源途径,count(*) as 人数
from test where convert(char(10),ywsj,120) between '2012-01-01' and '2012-01-03'
GROUP BY ISNULL(XinXiLY,''),convert(char(10),ywsj,120)
我想得到的结果是
业务日期 工人 老师 学生
2011-01-01 3 2 4
2011-01-02 2 2 2
2011-01-03 1 0 2
要得到这样的结果 语句该怎么写啊？
------解决方案--------------------
- SQL code
select ywsj,sum(case XinXiLY when '学生' then 1 else 0 end) [学生], sum(...) [], sum(...) []from tbgroup by ywsj
------解决方案--------------------
- SQL code
create TABLE [test]([ywSJ] [datetime] NOT NULL ,[XinXiLY] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )insert into test select '2012-01-01',N'学生'insert into test select '2012-01-01',N'老师' insert into test select '2012-01-01',N'老师' insert into test select '2012-01-01',N'学生' insert into test select '2012-01-01',N'工人' insert into test select '2012-01-01',N'工人' insert into test select '2012-01-01',N'学生' insert into test select '2012-01-01',N'工人' insert into test select '2012-01-01',N'学生'insert into test select '2012-01-02',N'学生'insert into test select '2012-01-02',N'老师' insert into test select '2012-01-02',N'老师' insert into test select '2012-01-02',N'学生' insert into test select '2012-01-02',N'工人' insert into test select '2012-01-02',N'工人' insert into test select '2012-01-03',N'学生' insert into test select '2012-01-03',N'工人' insert into test select '2012-01-03',N'学生' select * from(select * from test) ppivot( count(Xinxily) for XinxiLy in ([学生],[工人],[老师]))as pvt/*ywSJ 学生 工人 老师2012-01-01 00:00:00.000 4 3 22012-01-02 00:00:00.000 2 2 22012-01-03 00:00:00.000 2 1 0*/
------解决方案--------------------
同意一楼的！！
------解决方案--------------------
业务日期 工人 老师 学生
2012-01-01 3 2 4
2012-01-02 2 2 2
2012-01-03 1 0 2
------解决方案--------------------
--生成测试数据
CREATE TABLE [test]
([ywSJ] [datetime] NOT NULL ,
[XinXiLY] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )
insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','老师'
insert into test select '2012-01-01','老师'
insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','学生'