请教SQL语句，麻烦指教一下。
现在有个销售历史明细表，包括shd_saledate(日期），shd_dept(服装类别：包括10，20，30，40，50...），shd_sku（服装条码：每个条码都属于一个服装类别），shd_amount(销售金额），我只能一次查询一个shd_dept(服装类别)每天的销售额，如下：
select shd_saledate 日期,sum(shd_amount) 10部类销售额
from salehistorydtl
where shd_dept like '10 '
and
shd_saledate between '2007-07-28 ' and '2007-08-04 '
group by shd_saledate
order by shd_saledate asc
现在请教各位大师：如何一次查出每个shd_dept(服装类别)在2007-07-28 '和 '2007-08-04 '时间段的销售额？
[解决办法]
select sum(case when shd_dept=10 then shd_amount else 0 end )as '10 ',
sum(case when shd_dept=20 then shd_amount else 0 end )as '20 ',
sum(case when shd_dept=30 then shd_amount else 0 end )as '30 ',
sum(case when shd_dept=40 then shd_amount else 0 end )as '40 ',
sum(case when shd_dept=50 then shd_amount else 0 end )as '50 ',
.......
from t
where shd_saledate between '2007-07-28 ' and '2007-08-04 '
[解决办法]
select shd_saledate 日期,sum(shd_amount) 10部类销售额
from salehistorydtl
where shd_dept like '10 '
and
shd_saledate between '2007-07-28 ' and '2007-08-04 '
group by shd_saledate
order by shd_saledate asc
将所有的shd_dept(服装类别)每天的销售额select出来，然后所有union起来，试试行不行，不过这个办法太笨了。
[解决办法]
--类似于下面(如果你给出的Select语句正确的话)：
select shd_saledate 日期,sum(shd_amount) 10部类销售额
from salehistorydtl
where shd_dept like '10 '
and
shd_saledate between '2007-07-28 ' and '2007-08-04 '
group by shd_saledate
order by shd_saledate asc
Union
select shd_saledate 日期,sum(shd_amount) 10部类销售额
from salehistorydtl
where shd_dept like '10 '
and
shd_saledate between '2007-07-28 ' and '2007-08-04 '
group by shd_saledate
order by shd_saledate asc
Union ……
[解决办法]
--如果部类是固定的話
select
shd_saledate 日期,
sum(case when shd_dept like '10% ' Then shd_amount Else 0 End) As [10部类销售额],
sum(case when shd_dept like '20% ' Then shd_amount Else 0 End) As [20部类销售额],
sum(case when shd_dept like '30% ' Then shd_amount Else 0 End) As [30部类销售额]
...
from
salehistorydtl
where
shd_saledate between '2007-07-28 ' and '2007-08-04 '
group by
shd_saledate
order by
shd_saledate asc
[解决办法]
--如果部类不是固定的話
Declare @S Varchar(8000)
Select @S = ' select shd_saledate 日期 '
Select @S = @S + ' , sum(case when shd_dept like ' ' ' + 部类 + '% ' ' Then shd_amount Else 0 End) As [ ' + 部类 + '部类销售额] '
from (Select Distinct Left(shd_dept, 2) as shd_dept from salehistorydtl) A Order By shd_dept
Select @S = @S + ' from salehistorydtl where shd_saledate between ' '2007-07-28 ' ' and ' '2007-08-04 ' ' group by shd_saledate order by shd_saledate asc '
EXEC(@S)
