select
[ORDER_NO]=case when left(a.ORDER_NO,1)='A' then 'DIRECT' else 'MAB' end,
[CUSTOMER]=a.CUST_NO,
b.[PARTNO],
b.RESIN
b.THK,
b.CU,
[1月]=sum(case when month(c.[DATETIME])=1 then c.Qty else 0 end),
[2月]=sum(case when month(c.[DATETIME])=2 then c.Qty else 0 end),
[3月]=sum(case when month(c.[DATETIME])=3 then c.Qty else 0 end),
[4月]=sum(case when month(c.[DATETIME])=4 then c.Qty else 0 end),
[5月]=sum(case when month(c.[DATETIME])=5 then c.Qty else 0 end),
[6月]=sum(case when month(c.[DATETIME])=6 then c.Qty else 0 end),
[7月]=sum(case when month(c.[DATETIME])=7 then c.Qty else 0 end),
[8月]=sum(case when month(c.[DATETIME])=8 then c.Qty else 0 end),
[9月]=sum(case when month(c.[DATETIME])=9 then c.Qty else 0 end),
[10月]=sum(case when month(c.[DATETIME])=10 then c.Qty else 0 end),
[11月]=sum(case when month(c.[DATETIME])=11 then c.Qty else 0 end),
[12月]=sum(case when month(c.[DATETIME])=12 then c.Qty else 0 end),
sum(c.Qty) as 总计
from
a
join
b on a.ORDER_NO=b.ORDER_NO
join
c on c.ORDER_NO=a.ORDER_NO
group by case when left(a.ORDER_NO,1)='A' then 'DIRECT' else 'MAB' end,a.CUST_NO,b.[PARTNO],b.RESIN,b.THK,b.CU
[ORDER_NO]=case when left(a.ORDER_NO,1)='A' then 'DIRECT' else 'MAB' end,
[CUSTOMER]=a.CUST_NO,
b.[PARTNO],
b.RESIN
b.THK,
b.CU,
[1月]=sum(case when month(c.[DATETIME])=1 then c.Qty else 0 end),
[2月]=sum(case when month(c.[DATETIME])=2 then c.Qty else 0 end),
[3月]=sum(case when month(c.[DATETIME])=3 then c.Qty else 0 end),
[4月]=sum(case when month(c.[DATETIME])=4 then c.Qty else 0 end),
[5月]=sum(case when month(c.[DATETIME])=5 then c.Qty else 0 end),
[6月]=sum(case when month(c.[DATETIME])=6 then c.Qty else 0 end),
[7月]=sum(case when month(c.[DATETIME])=7 then c.Qty else 0 end),
[8月]=sum(case when month(c.[DATETIME])=8 then c.Qty else 0 end),
[9月]=sum(case when month(c.[DATETIME])=9 then c.Qty else 0 end),
[10月]=sum(case when month(c.[DATETIME])=10 then c.Qty else 0 end),
[11月]=sum(case when month(c.[DATETIME])=11 then c.Qty else 0 end),
[12月]=sum(case when month(c.[DATETIME])=12 then c.Qty else 0 end),
sum(c.Qty) as 总计
from
a
join
b on a.ORDER_NO=b.ORDER_NO
join
c on c.ORDER_NO=a.ORDER_NO
group by case when left(a.ORDER_NO,1)='A' then 'DIRECT' else 'MAB' end,a.CUST_NO,b.[PARTNO],b.RESIN,b.THK,b.CU
case left(a.ORDER_NO,1) when 'A' then 'DIRECT' else 'MAB' end as Shipment,
a.CUST_NO as CUSTOMER,
b.PART_NO as PARTNO,
b.RESIN,
b.THK,
b.CU,
sum(case month(c.[DATETIME]) when 1 then c.QTY else 0 end as [1月],
sum(case month(c.[DATETIME]) when 2 then c.QTY else 0 end as [2月],
sum(case month(c.[DATETIME]) when 3 then c.QTY else 0 end as [3月],
...
sum(case month(c.[DATETIME]) when 12 then c.QTY else 0 end as [12月],
sum(c.QTY) as 总计
from
a
inner join b
on
a.ORDER_NO=b.ORDER_NO
left join c
on
b.ORDER_NO=c.ORDER_NO and
n.ITEM=c.ITEM
group by
case left(a.ORDER_NO,1) when 'A' then 'DIRECT' else 'MAB' end,
a.CUST_NO,
b.PART_NO,
b.RESIN,
b.THK,
b.CU
a.cust_no,b.part_no,b.resin,b.thk,b.cu,
sum( case convert(varchar(7),c.datetime,120) when '2008-01' then c.qty end) as [1月],
sum( case convert(varchar(7),c.datetime,120) when '2008-02' then c.qty end) as [2月],
sum( case convert(varchar(7),c.datetime,120) when '2008-03' then c.qty end) as [3月],
sum( case convert(varchar(7),c.datetime,120) when '2008-04' then c.qty end) as [4月],
sum( case convert(varchar(7),c.datetime,120) when '2008-05' then c.qty end) as [5月],
sum( case convert(varchar(7),c.datetime,120) when '2008-06' then c.qty end) as [6月],
sum( case convert(varchar(7),c.datetime,120) when '2008-07' then c.qty end) as [7月],
sum( case convert(varchar(7),c.datetime,120) when '2008-08' then c.qty end) as [8月],
sum( case convert(varchar(7),c.datetime,120) when '2008-09' then c.qty end) as [9月],
sum( case convert(varchar(7),c.datetime,120) when '2008-10' then c.qty end) as [10月],
sum( case convert(varchar(7),c.datetime,120) when '2008-11' then c.qty end) as [11月],
sum( case convert(varchar(7),c.datetime,120) when '2008-12' then c.qty end) as [12月]
from 表A a inner join 表B b on a.order_no=b.order_no
inner join 表C c on a.order_no=c.order_no
group by shipment,a.cust_no,b.part_no,b.resin,b.thk,b.cu
select (case left(a.order_no,1) when 'A' then 'DIRECT' when 'M' then 'MAB' end) as shipment,
a.cust_no,b.part_no,b.resin,b.thk,b.cu,
sum( case convert(varchar(7),c.datetime,120) when '2008-01' then c.qty end) as [1月],
sum( case convert(varchar(7),c.datetime,120) when '2008-02' then c.qty end) as [2月],
sum( case convert(varchar(7),c.datetime,120) when '2008-03' then c.qty end) as [3月],
sum( case convert(varchar(7),c.datetime,120) when '2008-04' then c.qty end) as [4月],
sum( case convert(varchar(7),c.datetime,120) when '2008-05' then c.qty end) as [5月],
sum( case convert(varchar(7),c.datetime,120) when '2008-06' then c.qty end) as [6月],
sum( case convert(varchar(7),c.datetime,120) when '2008-07' then c.qty end) as [7月],
sum( case convert(varchar(7),c.datetime,120) when '2008-08' then c.qty end) as [8月],
sum( case convert(varchar(7),c.datetime,120) when '2008-09' then c.qty end) as [9月],
sum( case convert(varchar(7),c.datetime,120) when '2008-10' then c.qty end) as [10月],
sum( case convert(varchar(7),c.datetime,120) when '2008-11' then c.qty end) as [11月],
sum( case convert(varchar(7),c.datetime,120) when '2008-12' then c.qty end) as [12月]
from 表A a inner join 表B b on a.order_no=b.order_no
inner join 表C c on a.order_no=c.order_no
group by shipment,a.cust_no,b.part_no,b.resin,b.thk,b.cu