100分求解SQL语句，顺便请邹建同志看看，拜托啦！

Question

版主CrazyFor同志，帮帮手吧！！！！

Answer 1

http://community.csdn.net/Expert/topic/3109/3109432.xml?temp=.369137
大家帮我看看这个帖

Answer 2

嗯， lshuy2001(洋洋) 说得对。
一年就是12个月份，自己一条一条加上去就好了……
感觉就是一条一条的搭建起来……居然还要这样给出小计……
要求比较过分哦

Answer 3

算了，先给两个月的，增强一下信心create table S_order (
Order_ID        Varchar(10),--订单号
Product_ID      Varchar(20),--产品编号
DeliveryDate    Varchar(20),--交货日期
DeliveryQty     Smallint    --交货数量
)
go
insert S_order
select 
'001',       'A001'       ,  '2004-01-01',       1000
union all select
'001' ,      'A001'      ,   '2004-01-10' ,      2000
union all select
'001'  ,     'A001'     ,    '2004-01-20'  ,     5000
union all select
'001'   ,    'B001'    ,     '2004-01-01'   ,    2000
union all select
'002'    ,   'A001'   ,      '2004-02-01'    ,   1000
union all select
'002'     ,  'A001'  ,       '2004-03-15'     ,  1000
union all select
'003'      , 'B001' ,        '2004-02-10'      , 5000
union all select
'004'       ,'B001',         '2004-02-20'       ,3000 
go
create table T_Order(
Product_ID      Varchar(20),--产品编号
Order1          Varchar(10),--1月订单号（交期为1月）
DeliveryDate1   Varchar(20),--1月交期
DeliveryQty1    Smallint,    --1月数量
Order2          Varchar(10),--2月订单号
DeliveryDate2   Varchar(20),--2月交期
DeliveryQty2    Smallint,    --2月数量
Order3          Varchar(10),--3月订单号
DeliveryDate3   Varchar(20),--3月交期
DeliveryQty3    Smallint,    --3月数量
Order4          Varchar(10),--4月订单号
DeliveryDate4   Varchar(20),--4月交期
DeliveryQty4    Smallint    --4月数量
)
goselect isnull(b1.Product_ID,b2.Product_ID) as Product_ID ,
Order1,DeliveryDate1,DeliveryQty1,
Order2,DeliveryDate2,DeliveryQty2
from 
(
select * from (
select 
Product_ID,
Order_ID as Order1,
DeliveryDate as DeliveryDate1,
DeliveryQty as DeliveryQty1,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
union all
select 
Product_ID,
'小计' as Order1,
'' as DeliveryDate1,
sum(DeliveryQty) as DeliveryQty1,
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
group by Product_ID
) as a1
) as b1 full join 
(
select * from (
select 
Product_ID,
Order_ID as Order2,
DeliveryDate as DeliveryDate2,
DeliveryQty as DeliveryQty2,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
union all
select 
Product_ID,
'小计' as Order2,
'' as DeliveryDate2,
sum(DeliveryQty) as DeliveryQty2,
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
group by Product_ID
) as a2
) as b2
on b1.Product_ID=b2.Product_ID
and b1.SortNum=b2.SortNum
order by 
isnull(b1.Product_ID,b2.Product_ID),
isnull(b1.SortNum,b2.SortNum)/*
结果
Product_ID           Order1     DeliveryDate1        DeliveryQty1 Order2     DeliveryDate2        DeliveryQty2 
-------------------- ---------- -------------------- ------------ ---------- -------------------- ------------ 
A001                 001        2004-01-01           1000         002        2004-02-01           1000
A001                 001        2004-01-10           2000         NULL       NULL                 NULL
A001                 001        2004-01-20           5000         NULL       NULL                 NULL
A001                 小计                              8000         小计                              1000
B001                 001        2004-01-01           2000         003        2004-02-10           5000
B001                 NULL       NULL                 NULL         004        2004-02-20           3000
B001                 小计                              2000         小计                              8000（所影响的行数为 7 行）
*/

Answer 4

三个月（看来不能这样继续扩大到12个月了，要用临时表才行）：select isnull(c1.Product_ID,b3.Product_ID) as Product_ID ,
Order1,DeliveryDate1,DeliveryQty1,
Order2,DeliveryDate2,DeliveryQty2,
Order3,DeliveryDate3,DeliveryQty3
from (
select isnull(b1.Product_ID,b2.Product_ID) as Product_ID ,
Order1,DeliveryDate1,DeliveryQty1,
Order2,DeliveryDate2,DeliveryQty2,
isnull(b1.SortNum,b2.SortNum) as SortNum
from 
(
select * from (
select 
Product_ID,
Order_ID as Order1,
DeliveryDate as DeliveryDate1,
DeliveryQty as DeliveryQty1,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
union all
select 
Product_ID,
'小计' as Order1,
'' as DeliveryDate1,
sum(DeliveryQty) as DeliveryQty1,
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
group by Product_ID
) as a1
) as b1 full join 
(
select * from (
select 
Product_ID,
Order_ID as Order2,
DeliveryDate as DeliveryDate2,
DeliveryQty as DeliveryQty2,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
union all
select 
Product_ID,
'小计' as Order2,
'' as DeliveryDate2,
sum(DeliveryQty) as DeliveryQty2,
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
group by Product_ID
) as a2
) as b2
on b1.Product_ID=b2.Product_ID
and b1.SortNum=b2.SortNum
) as c1 full join 
(
select * from (
select 
Product_ID,
Order_ID as Order3,
DeliveryDate as DeliveryDate3,
DeliveryQty as DeliveryQty3,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
union all
select 
Product_ID,
'小计' as Order3,
'' as DeliveryDate3,
sum(DeliveryQty) as DeliveryQty3,
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
group by Product_ID
) as a3
) as b3
on c1.Product_ID=b3.Product_ID
and c1.SortNum=b3.SortNum
order by 
isnull(c1.Product_ID,b3.Product_ID),
isnull(c1.SortNum,b3.SortNum)/*
结果：
Product_ID           Order1     DeliveryDate1        DeliveryQty1 Order2     DeliveryDate2        DeliveryQty2 Order3     DeliveryDate3        DeliveryQty3 
-------------------- ---------- -------------------- ------------ ---------- -------------------- ------------ ---------- -------------------- ------------ 
A001                 001        2004-01-01           1000         002        2004-02-01           1000         002        2004-03-15           1000
A001                 001        2004-01-10           2000         NULL       NULL                 NULL         NULL       NULL                 NULL
A001                 001        2004-01-20           5000         NULL       NULL                 NULL         NULL       NULL                 NULL
A001                 小计                              8000         小计                              1000         小计                              1000
B001                 001        2004-01-01           2000         003        2004-02-10           5000         NULL       NULL                 NULL
B001                 NULL       NULL                 NULL         004        2004-02-20           3000         NULL       NULL                 NULL
B001                 小计                              2000         小计                              8000         NULL       NULL                 NULL（所影响的行数为 7 行）*/

Answer 5

--建临时表
create table #T_Order(
Product_ID      Varchar(20),--产品编号
SortNum         int,    --排序编号
Order1          Varchar(10) null,--1月订单号（交期为1月）
DeliveryDate1   Varchar(20) null,--1月交期
DeliveryQty1    Smallint null,    --1月数量
Order2          Varchar(10) null,--2月订单号
DeliveryDate2   Varchar(20) null,--2月交期
DeliveryQty2    Smallint null,    --2月数量
Order3          Varchar(10) null,--3月订单号
DeliveryDate3   Varchar(20) null,--3月交期
DeliveryQty3    Smallint null,    --3月数量
Order4          Varchar(10) null,--4月订单号
DeliveryDate4   Varchar(20) null,--4月交期
DeliveryQty4    Smallint null,    --4月数量
Order5          Varchar(10) null,--5月订单号
DeliveryDate5   Varchar(20) null,--5月交期
DeliveryQty5    Smallint null,    --5月数量
Order6          Varchar(10) null,--6月订单号
DeliveryDate6   Varchar(20) null,--6月交期
DeliveryQty6    Smallint null,    --6月数量
Order7          Varchar(10) null,--7月订单号
DeliveryDate7   Varchar(20) null,--7月交期
DeliveryQty7    Smallint null,    --7月数量
Order8          Varchar(10) null,--8月订单号
DeliveryDate8   Varchar(20) null,--8月交期
DeliveryQty8    Smallint null,    --8月数量
Order9          Varchar(10) null,--9月订单号
DeliveryDate9   Varchar(20) null,--9月交期
DeliveryQty9    Smallint null,    --9月数量
Order10          Varchar(10) null,--10月订单号
DeliveryDate10   Varchar(20) null,--10月交期
DeliveryQty10    Smallint null,    --10月数量
Order11          Varchar(10) null,--11月订单号
DeliveryDate11   Varchar(20) null,--11月交期
DeliveryQty11    Smallint null,    --11月数量
Order12          Varchar(10) null,--12月订单号
DeliveryDate12   Varchar(20) null,--12月交期
DeliveryQty12    Smallint  null   --12月数量
)--    1月
insert #T_Order(
Product_ID,
Order1,DeliveryDate1,DeliveryQty1,
SortNum
)
select 
Product_ID,
Order_ID,
DeliveryDate,
DeliveryQty,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
union all
select 
Product_ID,
'小计',
'',
sum(DeliveryQty),
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-1-1'
and convert(datetime,DeliveryDate)<'2004-2-1'
group by Product_ID
--    2月
insert #T_Order(
Product_ID,
Order2,DeliveryDate2,DeliveryQty2,
SortNum
)
select 
Product_ID,
Order_ID,
DeliveryDate,
DeliveryQty,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
union all
select 
Product_ID,
'小计',
'',
sum(DeliveryQty),
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-2-1'
and convert(datetime,DeliveryDate)<'2004-3-1'
group by Product_ID
--    3月
insert #T_Order(
Product_ID,
Order3,DeliveryDate3,DeliveryQty3,
SortNum
)
select 
Product_ID,
Order_ID,
DeliveryDate,
DeliveryQty,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
union all
select 
Product_ID,
'小计',
'',
sum(DeliveryQty),
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-3-1'
and convert(datetime,DeliveryDate)<'2004-4-1'
group by Product_ID
--    4月
insert #T_Order(
Product_ID,
Order3,DeliveryDate3,DeliveryQty3,
SortNum
)
select 
Product_ID,
Order_ID,
DeliveryDate,
DeliveryQty,
(select count(*) from S_order
where convert(datetime,DeliveryDate)>='2004-4-1'
and convert(datetime,DeliveryDate)<'2004-5-1'
and Product_ID=a.Product_ID
and convert(datetime,DeliveryDate)<=convert(datetime,a.DeliveryDate)
) as SortNum
from S_order a
where convert(datetime,DeliveryDate)>='2004-4-1'
and convert(datetime,DeliveryDate)<'2004-5-1'
union all
select 
Product_ID,
'小计',
'',
sum(DeliveryQty),
2147483647 as SortNum
from S_order
where convert(datetime,DeliveryDate)>='2004-4-1'
and convert(datetime,DeliveryDate)<'2004-5-1'
group by Product_ID
--其他月份自己加--汇总
select Product_ID,
max(Order1) as Order1,
max(DeliveryDate1) as DeliveryDate1,
sum(DeliveryQty1) as DeliveryQty1,
max(Order2) as Order2,
max(DeliveryDate2) as DeliveryDate2,
sum(DeliveryQty2) as DeliveryQty2,
max(Order3) as Order3,
max(DeliveryDate3) as DeliveryDate3,
sum(DeliveryQty3) as DeliveryQty3       --其他月自己加
from #T_order
group by Product_ID,SortNum
order by Product_ID,SortNum/*
结果
Product_ID           Order1     DeliveryDate1        DeliveryQty1 Order2     DeliveryDate2        DeliveryQty2 Order3     DeliveryDate3        DeliveryQty3 
-------------------- ---------- -------------------- ------------ ---------- -------------------- ------------ ---------- -------------------- ------------ 
A001                 001        2004-01-01           1000         002        2004-02-01           1000         002        2004-03-15           1000
A001                 001        2004-01-10           2000         NULL       NULL                 NULL         NULL       NULL                 NULL
A001                 001        2004-01-20           5000         NULL       NULL                 NULL         NULL       NULL                 NULL
A001                 小计                              8000         小计                              1000         小计                              1000
B001                 001        2004-01-01           2000         003        2004-02-10           5000         NULL       NULL                 NULL
B001                 NULL       NULL                 NULL         004        2004-02-20           3000         NULL       NULL                 NULL
B001                 小计                              2000         小计                              8000         NULL       NULL                 NULL（所影响的行数为 7 行）*/
drop table #T_order

Answer 6

有主键应该能排出来，不过必须用临时表，否则11个full join就不知道怎么执行了

Answer 7

11个full join 应该是没问题,但效率应该是问题.

Answer 8

--不知道这样处理,效率会不会好一些,楼主有数据的话,测试一下大家的方法--查询的存储过程
create proc p_qry
@begin_ym char(6)=200401, --查询的开始年月
@end_ym char(6)=200403 --查询的结束年月
as
--明细数据
declare @rcount varchar(20)
select gid=0,ym=datediff(month,@begin_ym+'01',DeliveryDate)+1
,Product_ID1=Product_ID,Order_ID,DeliveryDate,DeliveryQty
into #t
from S_order
where DeliveryDate 
between convert(char(10),cast(@begin_ym+'01' as datetime),120)
and convert(char(10),dateadd(month,1,@end_ym+'01')-1,120)
order by Product_ID,DeliveryDate,Order_ID
set @rcount=@@rowcount+1--生成分组序号
declare @Product_ID Varchar(20),@ym int,@i int
update #t set @i=case 
when @Product_ID=Product_ID1 and @ym=ym
then @i+1 else 1 end
,gid=@i,@Product_ID=Product_ID1,@ym=ym--小计数据
insert #t select @rcount,datediff(month,@begin_ym+'01',DeliveryDate)+1,Product_ID,'小计','',sum(DeliveryQty)
from S_order
where DeliveryDate 
between convert(char(10),cast(@begin_ym+'01' as datetime),120)
and convert(char(10),dateadd(month,1,@end_ym+'01')-1,120)
group by Product_ID,datediff(month,@begin_ym+'01',DeliveryDate)+1--转置显示处理
declare @s varchar(8000)
select @s='',@i=max(ym) from #t
while @i>0
select @s=',[Order_ID'+cast(@i as varchar)
+']=max(case ym when '+cast(@i as varchar)
+' then Order_ID else '''' end),[DeliveryDate'
+cast(@i as varchar)+']=max(case when ym='
+cast(@i as varchar)+' then DeliveryDate else '''' end),[DeliveryQty'
+cast(@i as varchar)+']=max(case ym when '
+cast(@i as varchar)+' then DeliveryQty else '''' end)'
+@s
,@i=@i-1
exec('select Product_ID=case gid when '+@rcount+' then Product_ID1 else '''' end'+@s+'
from #t
group by Product_ID1,gid
order by Product_ID1,gid')
go--调用
exec p_qry '200401','200403'
go

Answer 9

--测试--测试数据
create table S_order(
Order_ID        Varchar(10),--订单号
Product_ID      Varchar(20),--产品编号
DeliveryDate    Varchar(20),--交货日期
DeliveryQty     Smallint    --交货数量
)
insert S_order select '001','A001','2004-01-01',1000
union      all select '001','A001','2004-01-10',2000
union      all select '001','A001','2004-01-20',5000
union      all select '001','B001','2004-01-01',2000
union      all select '002','A001','2004-02-01',1000
union      all select '002','A001','2004-03-15',1000
union      all select '003','B001','2004-02-10',5000
union      all select '004','B001','2004-02-20',3000 
go--查询的存储过程
create proc p_qry
@begin_ym char(6)=200401, --查询的开始年月
@end_ym char(6)=200403 --查询的结束年月
as
--明细数据
declare @rcount varchar(20)
select gid=0,ym=datediff(month,@begin_ym+'01',DeliveryDate)+1
,Product_ID1=Product_ID,Order_ID,DeliveryDate,DeliveryQty
into #t
from S_order
where DeliveryDate 
between convert(char(10),cast(@begin_ym+'01' as datetime),120)
and convert(char(10),dateadd(month,1,@end_ym+'01')-1,120)
order by Product_ID,DeliveryDate,Order_ID
set @rcount=@@rowcount+1--生成分组序号
declare @Product_ID Varchar(20),@ym int,@i int
update #t set @i=case 
when @Product_ID=Product_ID1 and @ym=ym
then @i+1 else 1 end
,gid=@i,@Product_ID=Product_ID1,@ym=ym--小计数据
insert #t select @rcount,datediff(month,@begin_ym+'01',DeliveryDate)+1,Product_ID,'小计','',sum(DeliveryQty)
from S_order
where DeliveryDate 
between convert(char(10),cast(@begin_ym+'01' as datetime),120)
and convert(char(10),dateadd(month,1,@end_ym+'01')-1,120)
group by Product_ID,datediff(month,@begin_ym+'01',DeliveryDate)+1--转置显示处理
declare @s varchar(8000)
select @s='',@i=max(ym) from #t
while @i>0
select @s=',[Order_ID'+cast(@i as varchar)
+']=max(case ym when '+cast(@i as varchar)
+' then Order_ID else '''' end),[DeliveryDate'
+cast(@i as varchar)+']=max(case when ym='
+cast(@i as varchar)+' then DeliveryDate else '''' end),[DeliveryQty'
+cast(@i as varchar)+']=max(case ym when '
+cast(@i as varchar)+' then DeliveryQty else '''' end)'
+@s
,@i=@i-1
exec('select Product_ID=case gid when '+@rcount+' then Product_ID1 else '''' end'+@s+'
from #t
group by Product_ID1,gid
order by Product_ID1,gid')
go--调用
exec p_qry '200401','200403'
go--删除测试
drop table S_order
drop proc p_qry/*--测试结果Product_ID           Order_ID1  DeliveryDate1        DeliveryQty1 Order_ID2  DeliveryDate2        DeliveryQty2 Order_ID3  DeliveryDate3        DeliveryQty3 
-------------------- ---------- -------------------- ------------ ---------- -------------------- ------------ ---------- -------------------- ------------ 
                     001        2004-01-01           1000         002        2004-02-01           1000         002        2004-03-15           1000
                     001        2004-01-10           2000                                         0                                            0
                     001        2004-01-20           5000                                         0                                            0
A001                 小计                              8000         小计                              1000         小计                              1000
                     001        2004-01-01           2000         003        2004-02-10           5000                                         0
                                                     0            004        2004-02-20           3000                                         0
B001                 小计                              2000         小计                              8000                                         0（所影响的行数为 7 行）--*/