不懂,求教ID Date 111 2011-01-01从表 ID Eid ItemID 222 '111' 101101
项目表 ID Name level 101 生长发育 0 101101 身高 1 101102 体重 2 102 眼科 0 102101 视力 1 统计level为0的 日期 项目 数 2011-01-01 生长发育 1 ...select year(t0.date), case substring(convert(char(20),t1.ItemID),1,3) when '101' then SUM(t1.ID) else 0 end as '生长发育', case substring(convert(char(20),t1.ItemID),1,3) when '102' then SUM(t1.ID) else 0 end as '眼科' from tb1 t0 inner join tb2 t1 on t0.ID=t1.Eid where t1.Status=5 and LEN(t1.ItemID)=3 and YEAR(t0.date)=2014 group by year(t0.date),t1.ItemID 只能想到这了
if not object_id('Class') is null drop table Class Go Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int) Insert Class select N'张三',N'语文',78 union all select N'张三',N'数学',87 union all select N'张三',N'英语',82 union all select N'张三',N'物理',90 union all select N'李四',N'语文',65 union all select N'李四',N'数学',77 union all select N'李四',N'英语',65 union all select N'李四',N'物理',85 Go --2000方法: 动态:
declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' from Class group by [Course] exec('select [Student]'+@s+' from Class group by [Student]')这些行转列是基于一个表的,你可以把中间结果也就是3个表的数据存到一个临时表中,然后替换掉上面例子中的class表
项目表 ID Name level 101 生长发育 0 101101 身高 1 101102 体重 2 102 眼科 0 102101 视力 1
统计level为0的 日期 项目 数 2011-01-01 生长发育 1 ...
select year(t0.date), t1.ItemID, count(0) as cnt from tb1 t0 inner join tb2 t1 on t0.ID=t1.Eid where t1.Status=5 and LEN(t1.ItemID)=3 and YEAR(t0.date)=2014 group by year(t0.date),t1.ItemID类似这样,就是行数据,然后做行列转换
可以了,之前就是卡在项目多级情况下统计数上了create table testd( createdate int, itemname nvarchar(20), totalcount int ) select * from testd insert into testd(createdate,itemname,totalcount) select T.createdate,T.name,sum(T.totalcount) from( select year(t0.createdate) as createdate, (select name from CM_ExamineItem where Level=0 and SUBSTRING(convert(char(20),t1.ItemID),1,3)=convert(char(20),ID) group by name)name, COUNT(t1.id) as totalcount from tb1 t0 inner join tb2 t1 on t0.ID=t1.CertificateID inner join tb3 t2 on t1.ItemID=t2.ID where t1.Status=5 and YEAR(t0.createdate)=2014-- and t2.Level=0 group by year(t0.createdate),t1.ItemID,name )T group by T.createdate,T.name SELECT * FROM ( SELECT createdate, itemname , totalcount FROM testd ) p PIVOT ( SUM(totalcount) FOR itemname IN ( [项目1],[项目2]) ) AS pvt ORDER BY pvt.createdate; 谢谢
111 2011-01-01从表
ID Eid ItemID
222 '111' 101101
项目表
ID Name level
101 生长发育 0
101101 身高 1
101102 体重 2
102 眼科 0
102101 视力 1 统计level为0的
日期 项目 数
2011-01-01 生长发育 1
...select year(t0.date),
case substring(convert(char(20),t1.ItemID),1,3) when '101' then SUM(t1.ID) else 0 end as '生长发育',
case substring(convert(char(20),t1.ItemID),1,3) when '102' then SUM(t1.ID) else 0 end as '眼科'
from tb1 t0 inner join tb2 t1 on t0.ID=t1.Eid
where t1.Status=5 and LEN(t1.ItemID)=3 and YEAR(t0.date)=2014
group by year(t0.date),t1.ItemID
只能想到这了
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by [Course]
exec('select [Student]'+@s+' from Class group by [Student]')这些行转列是基于一个表的,你可以把中间结果也就是3个表的数据存到一个临时表中,然后替换掉上面例子中的class表
有兴趣可以看下http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html
ID Date
111 2011-01-01
从表
ID Eid ItemID
222 '111' 101101
项目表
ID Name level
101 生长发育 0
101101 身高 1
101102 体重 2
102 眼科 0
102101 视力 1
统计level为0的
日期 项目 数
2011-01-01 生长发育 1
...
select year(t0.date),
t1.ItemID,
count(0) as cnt
from tb1 t0 inner join tb2 t1 on t0.ID=t1.Eid
where t1.Status=5 and LEN(t1.ItemID)=3 and YEAR(t0.date)=2014
group by year(t0.date),t1.ItemID类似这样,就是行数据,然后做行列转换
3表查询。无非就是join起来,然后取需要的列,如果你连3个表都组合不出来,行转列估计你也不会
createdate int,
itemname nvarchar(20),
totalcount int
)
select * from testd
insert into testd(createdate,itemname,totalcount) select T.createdate,T.name,sum(T.totalcount) from(
select year(t0.createdate) as createdate,
(select name from CM_ExamineItem
where Level=0 and SUBSTRING(convert(char(20),t1.ItemID),1,3)=convert(char(20),ID) group by name)name,
COUNT(t1.id) as totalcount
from tb1 t0 inner join tb2 t1 on t0.ID=t1.CertificateID
inner join tb3 t2 on t1.ItemID=t2.ID
where t1.Status=5 and YEAR(t0.createdate)=2014-- and t2.Level=0
group by year(t0.createdate),t1.ItemID,name
)T
group by T.createdate,T.name
SELECT *
FROM ( SELECT createdate,
itemname ,
totalcount
FROM testd
) p PIVOT
( SUM(totalcount) FOR itemname IN ( [项目1],[项目2]) ) AS pvt
ORDER BY pvt.createdate;
谢谢