休闲类 计算机类 英语类,就这三类么?select 日期,sum( case when 种类='休闲类' then 数量 end) as 休闲类,
sum( case when 种类='计算机类' then 数量 end) as 计算机类,
sum( case when 种类='英语类' then 数量 end) as 英语类,
sum(数量) as 总数量.
sum(价钱*数量) as 总价钱
from Month_books
group by 日期
sum( case when 种类='计算机类' then 数量 end) as 计算机类,
sum( case when 种类='英语类' then 数量 end) as 英语类,
sum(数量) as 总数量.
sum(价钱*数量) as 总价钱
from Month_books
group by 日期
select 日期,sum( isnull(case when 种类='休闲类' then 数量 end,0)) as 休闲类,
sum( isnull(case when 种类='计算机类' then 数量 end,0)) as 计算机类,
sum( isnull(case when 种类='英语类' then 数量 end,0)) as 英语类,
sum(数量) as 总数量,
sum(数量*价钱) as 总价钱
from Month_books
group by 日期--结果
日期 休闲类 计算机类 英语类 总数量 总价钱
----------- ----------- ----------- ----------- -----------
2 1 2 3 6 118.3
3 1 0 0 1 38.0
4 0 1 0 1 58.0
--楼主的总价钱是怎么算的?
(所影响的行数为 3 行)
select @sql = @sql + ',sum(isnull(case 种类 when '''+种类+''' then 数量 end,0)) ['+ 种类+']'
from (select distinct 种类 from Month_books) as a
set @sql=@sql+',sum(数量) as 总数量,sum(数量*价钱) as 总价钱'
select @sql = @sql+' from Month_books group by 日期'
exec(@sql)
create table Month_books
(
dates int,
cTYPE varchar(20) ,
cNAME varchar(40),
nSL int,
fMoney float
)
insert into Month_books
select 2,'休闲类','心灵鸡汤',1,14.00 union all
select 2,'英语类','四级听力100题',1,21.50 union all
select 2,'英语类','时事阅读',2,7.50 union all
select 2,'计算机类','java速成',1,19.80 union all
select 2,'计算机类','delphi100招' , 1 , 48.00 union all
select 3,'休闲类','美容一周通', 1, 38.00 union all
select 4,'计算机类', '高级程序员教程',1,58.00 declare @sql nvarchar(4000)
set @sql = 'select dates as 日期 '
select @sql =@sql+ ',isnull(sum(case when cTYPE= '''+cTYPE+''' then nSL end),0) ['+cTYPE+']'
from (select distinct cTYPE from Month_books ) as a
select @sql = @sql+',sum(nSl) as 总数量 ,sum(nSl*fMoney) as 总价钱 from Month_books group by dates '
exec (@sql)------------------------日期 休闲类 计算机类 英语类 总数量 总价钱
2 2 1 3 6 118.3
3 0 1 0 1 38.0
4 1 0 0 1 58.0