将表重新规整排序的问题
jgm kz sl rq
0102010580p5002007-7-5 0:00:00
0102010580z1002007-7-5 0:00:00
0102010580f502007-7-5 0:00:00
0103020525p10002007-8-5 0:00:00
0103020525z2502007-8-5 0:00:00
0103020525f1002007-8-5 0:00:00
0103020525p10002007-5-1 0:00:00
0102010580z2502007-5-25 0:00:00
0103040535p5002007-8-20 0:00:00
0103040535z1002007-8-20 0:00:00
0103040535f502007-8-20 0:00:00
0103040535f502007-5-20 0:00:00
我想得到下面的结果
jgm kz(p)sl kz(z)sl kz(f)sl
0102010580 500 350 50
0103020525 2000 250 100
0103040535 500 100 100
得到一个jgm下kz为p,为z,为f的总的数量sl
[解决办法]
加上條件
Select
jgm,
SUM(Case kz When 'p ' Then sl Else 0 End) As [kz(p)sl],
SUM(Case kz When 'z ' Then sl Else 0 End) As [kz(z)sl],
SUM(Case kz When 'f ' Then sl Else 0 End) As [kz(f)sl]
From
表
Where
rq Between '2007-06-30 ' And '2007-09-30 '
Group By
jgm
或者
Select
jgm,
[kz(p)sl] = SUM(Case When kz = 'p ' Then sl Else 0 End),
[kz(z)sl] = SUM(Case When kz = 'z ' Then sl Else 0 End),
[kz(f)sl] = SUM(Case When kz = 'f ' Then sl Else 0 End)
From
表
Where
rq Between '2007-06-30 ' And '2007-09-30 '
Group By
jgm
