select b.leibie,
sum(case a.shibie when '早市' then shuliang*je else 0 end) as 早市,
sum(case a.shibie when '中市' then shuliang*je else 0 end) as 中市,
sum(case a.shibie when '晚市' then shuliang*je else 0 end) as 晚市
from 表1 a,表2 b
where convert(char(8),b.time,108) between a.kaishi and a.jieshu
group by b.leibiesql中能否只存时间,不存日期啊
只能用字符串这样保存,datetime类型不能
sum(case a.shibie when '早市' then shuliang*je else 0 end) as 早市,
sum(case a.shibie when '中市' then shuliang*je else 0 end) as 中市,
sum(case a.shibie when '晚市' then shuliang*je else 0 end) as 晚市
from 表1 a,表2 b
where convert(char(8),b.time,108) between a.kaishi and a.jieshu
group by b.leibiesql中能否只存时间,不存日期啊
只能用字符串这样保存,datetime类型不能
sum(case a.shibie when '早市' then shuliang*je else 0 end) as 早市,
sum(case a.shibie when '中市' then shuliang*je else 0 end) as 中市,
sum(case a.shibie when '晚市' then shuliang*je else 0 end) as 晚市,
sum(shuliang*je) as 合计
from 表1 a,表2 b
where convert(char(8),b.time,108) between a.kaishi and a.jieshu
group by b.leibie
declare @sql varchar(8000)
set @sql='select b.leibie'
select @sql=@sql+',
sum(case a.shibie when '''+rtrim(shibie)+''' then shuliang*je else 0 end) as [''+rtrim(shibie)+']' from 表1
set @sql=@sql+' ,
sum(shuliang*je) as 合计
from 表1 a,表2 b
where convert(char(8),b.time,108) between a.kaishi and a.jieshu
group by b.leibie'exec(@sql)
set @sql='select b.leibie'
select
@sql=@sql+',sum(case a.shibie when '''+rtrim(shibie)+''' then shuliang*je else 0 end) as ['+rtrim(shibie)+']'
from 表1set @sql=@sql+' ,sum(shuliang*je) as 合计
from 表1 a,表2 b
where convert(char(8),b.time,108) between a.kaishi and a.jieshu
group by b.leibie'exec(@sql)