货号 颜色 客户 进货 退货 销售 存货
S M L XL 合计 S M L XL 合计 S M L XL 合计 S M L XL 合计
A 1 K1 1 2 1 2 6
K2 2 1 1 1 5
K3 2 2 1 1 6
K4 1 1 2 1 5
K5 1 2 1 2 6
小计: 5 7 8 6 7 28
A 2 K1
K2
K3
K4
K5
. . .
. . .
. . .
S M L XL 合计 S M L XL 合计 S M L XL 合计 S M L XL 合计
A 1 K1 1 2 1 2 6
K2 2 1 1 1 5
K3 2 2 1 1 6
K4 1 1 2 1 5
K5 1 2 1 2 6
小计: 5 7 8 6 7 28
A 2 K1
K2
K3
K4
K5
. . .
. . .
. . .
(select 货号,颜色,客户,count(进货S) A,count(进货M) B,count(进货L) C,....
Group by 货号,颜色,客户) GroupTableA order by 货号,颜色,客户
select c.zzjg_mc as 单位,sum(1) as zsum,sum(zbs) as zbs,sum(fbs) as fbs,
sum(case c.dm_nr when '现职干部' then 1 else 0 end)as sum1,
sum(case c.dm_nr when '现职干部' then zbs else 0 end)as zbs1,
sum(case c.dm_nr when '现职干部' then fbs else 0 end)as fbs1,
sum(case c.dm_nr when '退出现职干部' then 1 else 0 end)as sum2,
sum(case c.dm_nr when '退出现职干部' then zbs else 0 end)as zbs2,
sum(case c.dm_nr when '退出现职干部' then fbs else 0 end)as fbs2,
sum(case c.dm_nr when '离休干部' then 1 else 0 end)as sum3,
sum(case c.dm_nr when '离休干部' then zbs else 0 end)as zbs3,
sum(case c.dm_nr when '离休干部' then fbs else 0 end)as fbs3, sum(case c.dm_nr when '退休干部' then 1 else 0 end)as sum4,
sum(case c.dm_nr when '退休干部' then zbs else 0 end)as zbs4,
sum(case c.dm_nr when '退休干部' then fbs else 0 end)as fbs4, sum(case c.dm_nr when '后备干部' then 1 else 0 end)as sum5,
sum(case c.dm_nr when '后备干部' then zbs else 0 end)as zbs5,
sum(case c.dm_nr when '后备干部' then fbs else 0 end)as fbs5, sum(case c.dm_nr when '待任' then 1 else 0 end)as sum6,
sum(case c.dm_nr when '待任' then zbs else 0 end)as zbs6,
sum(case c.dm_nr when '待任' then fbs else 0 end)as fbs6, sum(case c.dm_nr when '其他' then 1 else 0 end)as sum7,
sum(case c.dm_nr when '其他' then zbs else 0 end)as zbs7,
sum(case c.dm_nr when '其他' then fbs else 0 end)as fbs7, sum(case c.dm_nr when '去世' then 1 else 0 end)as sum8,
sum(case c.dm_nr when '去世' then zbs else 0 end)as zbs8,
sum(case c.dm_nr when '去世' then fbs else 0 end)as fbs8
from
( select zzjg_mc,user_jb_zbs as zbs,user_jb_fbs as fbs,dm_nr from user_jb_zzjg_zzjg_view
union
select zzjg_mc,user_jb_zbs as zbs,user_jb_fbs as fbs,dm_nr from user_jb_zzjg_zzjg_view2
)c
group by c.zzjg_mc
GO
自己前几天也碰到了类似问题 用了一个存储过程解决的 你自己参考一下吧
declare @sql varchar(800)
set @sql='select 货号,颜色'
select @sql=@sql+',sum(case when 尺码='''+Rtrim(NAME)+''' then 进货数量 end) ['+rtrim(NAME)+']'
from (select CODE,NAME from 尺码库)tem
exec(@sql+',lsj,zl,zhj,je from 进货库 WHERE 货号=''1234'' group by 货号,颜色order by 货号,颜色')
Query1.SQL.Clear;
Query1.SQL.Add('select * from #a where zddh='''+trim(edit1.Text)+'''');
Query1.SQL.Add('union');
Query1.SQL.Add('select ph,sh,sum(cpjc) as cpjc,null,null from ck where zddh='''+trim(edit1.Text)+''' group by ph,sh');
Query1.SQL.Add('order by ph,sh,cpjc'); Query1.Open;
From T1
Compute F1,Sum(F2),Sum(F3),Sum(F4),Sum(F2+F3+F4)
From T1