select 编号,aa.A,bb.B,cc.C from ( select 编号, A = sum(类型) from T1 where 类型 = 'A' group by 编号) as aa ( select 编号, B = sum(类型) from T1 where 类型 = 'B' group by 编号) as bb ( select 编号, C = sum(类型) from T1 where 类型 = 'C' group by 编号) as bb where aa.编号 *=bb.编号 and aa.编号 *= cc.编号
你可以首先用一条语句 select distinct 类型 from 表T1 得到类型的个数,然后在动态的创建你的SQL我就这样做过
语句应该这样 表A create table A ( ID NUMBER, LX VARCHAR2(1), C NUMBER ) ID A B C1 1 A 10 2 1 B 12 3 1 C 14 4 2 A 11 5 2 C 13select b.id,(select sum(c) from a where id=b.id and lx='A') A , (select sum(c) from a where id=b.id and lx='B') B, (select sum(c) from a where id=b.id and lx='C') C from a b
select 编号,sum(a) as a,sum(b) as b,sum(c) as c from ( select 编号,数量 as a,0 as b,0 as c from T1 where 类型 = 'A' unionselect 编号,0 as a,数量 as b,0 as c from T1 where 类型 = 'b' unionselect 编号,0 as a,0 as b,数量 as c from T1 where 类型 = 'c' ) aa group by 编号
程序这样 var s,c:string; begin s:='select b.id,' c:='(select sum(c) from a where id=b.id and lx=''%s'') %s ,' Query1.Close; Query1.Sql.Text:='select distinct lx from a'; Query1.Open; while not Query1.Eof do begin s:=s+Format(c,[Query1.FieldByName('lx').AsString, Query1.FieldByName('lx').AsString]); Query1.Next; end; s:=copy(s,1,Length(s)-1); s:=s+ ' from a b'; 这样S最后得到的就是你要的SQL
哦对了最好还要加个GROUP BY去掉重复的数据select cc.id,cc.a,cc.b,cc.c from (select b.id,(select sum(c) from a where id=b.id and lx='A') A , (select sum(c) from a where id=b.id and lx='B') B, (select sum(c) from a where id=b.id and lx='C') C from a b)cc group by cc.id,cc.a,cc.b,cc.c
如果你用sql server 2000,利用函数会简单点。 (不好意思,上面的少了',',而且先应该union一次:)
from
( select 编号, A = sum(类型)
from T1
where 类型 = 'A'
group by 编号) as aa
( select 编号, B = sum(类型)
from T1
where 类型 = 'B'
group by 编号) as bb
( select 编号, C = sum(类型)
from T1
where 类型 = 'C'
group by 编号) as bb
where aa.编号 *=bb.编号 and aa.编号 *= cc.编号
得到类型的个数,然后在动态的创建你的SQL我就这样做过
表A
create table A
(
ID NUMBER,
LX VARCHAR2(1),
C NUMBER
)
ID A B C1 1 A 10
2 1 B 12
3 1 C 14
4 2 A 11
5 2 C 13select b.id,(select sum(c) from a where id=b.id and lx='A') A ,
(select sum(c) from a where id=b.id and lx='B') B,
(select sum(c) from a where id=b.id and lx='C') C
from a b
(
select 编号,数量 as a,0 as b,0 as c from T1 where 类型 = 'A' unionselect 编号,0 as a,数量 as b,0 as c from T1 where 类型 = 'b' unionselect 编号,0 as a,0 as b,数量 as c from T1 where 类型 = 'c'
) aa group by 编号
var
s,c:string;
begin
s:='select b.id,'
c:='(select sum(c) from a where id=b.id and lx=''%s'') %s ,'
Query1.Close;
Query1.Sql.Text:='select distinct lx from a';
Query1.Open;
while not Query1.Eof do
begin
s:=s+Format(c,[Query1.FieldByName('lx').AsString,
Query1.FieldByName('lx').AsString]);
Query1.Next;
end;
s:=copy(s,1,Length(s)-1);
s:=s+ ' from a b';
这样S最后得到的就是你要的SQL
(select b.id,(select sum(c) from a where id=b.id and lx='A') A ,
(select sum(c) from a where id=b.id and lx='B') B,
(select sum(c) from a where id=b.id and lx='C') C
from a b)cc
group by cc.id,cc.a,cc.b,cc.c
(不好意思,上面的少了',',而且先应该union一次:)
请: firetoucher(风焱) 兄弟讲一下关于MS SQL 2K中用函数解决的方法,
我可以另开一贴
(case 类型 when a then 数量 end ) a,
(case 类型 when b then 数量 end ) b,
(case 类型 when c then 数量 end ) c
from T1