日期 姓名 件数2009-07-01 小A 50
2009-07-01 小B 60
2009-07-01 小C 45
2009-07-02 小A 62
2009-07-02 小B 55
2009-07-02 小C 42
2009-07-03 小A 38
2009-07-03 小B 46
2009-07-03 小C 57
--------------------------------------
日期 小A 小B 小C 总计2009-07-01 50 60 45 155
2009-07-02 62 55 42 159
2009-07-03 38 46 57 141表结构为上面的内容,如何查询出下面的结果
2009-07-01 小B 60
2009-07-01 小C 45
2009-07-02 小A 62
2009-07-02 小B 55
2009-07-02 小C 42
2009-07-03 小A 38
2009-07-03 小B 46
2009-07-03 小C 57
--------------------------------------
日期 小A 小B 小C 总计2009-07-01 50 60 45 155
2009-07-02 62 55 42 159
2009-07-03 38 46 57 141表结构为上面的内容,如何查询出下面的结果
-- Author:happyflystone
-- Version:V1.001
-- Date:2009-07-11 17:29:18
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(日期 smalldatetime,姓名 nvarchar(2),件数 int)
Go
Insert into ta
select '2009-07-01','小A',50 union all
select '2009-07-01','小B',60 union all
select '2009-07-01','小C',45 union all
select '2009-07-02','小A',62 union all
select '2009-07-02','小B',55 union all
select '2009-07-02','小C',42 union all
select '2009-07-03','小A',38 union all
select '2009-07-03','小B',46 union all
select '2009-07-03','小C',57
Go
--Start
declare @s varchar(2000)
select @s = isnull(@s+',','')+'['+姓名+']=max(case when 姓名 = '''+姓名+''' then 件数 else 0 end)'
from (select distinct 姓名 from ta) a
exec('select 日期,'+@s+',sum(件数) 总计 from ta group by 日期')
--Result:
/*日期 小A 小B 小C 总计
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-07-01 00:00:00 50 60 45 155
2009-07-02 00:00:00 62 55 42 159
2009-07-03 00:00:00 38 46 57 141
*/
--End
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================If object_id('ta') is not null
Drop table ta
Go
Create table ta(日期 smalldatetime,姓名 nvarchar(2),件数 int)
Go
Insert into ta
select '2009-07-01','小A',50 union all
select '2009-07-01','小B',60 union all
select '2009-07-01','小C',45 union all
select '2009-07-02','小A',62 union all
select '2009-07-02','小B',55 union all
select '2009-07-02','小C',42 union all
select '2009-07-03','小A',38 union all
select '2009-07-03','小B',46 union all
select '2009-07-03','小C',57
Go
select 日期,
小A=SUM(case when 姓名='小A' then 件数 else 0 end),
小B=SUM(case when 姓名='小B' then 件数 else 0 end),
小C=SUM(case when 姓名='小C' then 件数 else 0 end),
总计=SUM(isnull(件数,0))
from ta
group by 日期
/*------------(9 行受影响)
日期 小A 小B 小C 总计
----------------------- ----------- ----------- ----------- -----------
2009-07-01 00:00:00 50 60 45 155
2009-07-02 00:00:00 62 55 42 159
2009-07-03 00:00:00 38 46 57 141(3 行受影响)-------*/
insert into @tb select '2009-07-01','小A',50
union all select '2009-07-01','小B',60
union all select '2009-07-01','小C',45
union all select '2009-07-02','小A',62
union all select '2009-07-02','小B',55
union all select '2009-07-02','小C',42
union all select '2009-07-03','小A',38
union all select '2009-07-03','小B',46
union all select '2009-07-03','小C',57
select a.* ,b.件数 as 总件数
from (select * from @tb a pivot ( sum(件数) for 姓名 in (小A,小B,小C)) b) a
join (select 日期,SUM(件数)件数 from @tb group by 日期) b
on a.日期=b.日期 (9 行受影响)
日期 小A 小B 小C 总件数
----------------------- ----------- ----------- ----------- -----------
2009-07-01 00:00:00.000 50 60 45 155
2009-07-02 00:00:00.000 62 55 42 159
2009-07-03 00:00:00.000 38 46 57 141(3 行受影响)
不知道pivot 和拼SQL哪个效率高。谁有大数据量的库测一下
如何把上面SQL的改为ACCESS类型的
用IIF代替CASE WHEN
谢谢大家