急求一条sql语句，在线等！

显示全部楼层 · 2007-1-24 12:58:48

A 表
A字段 B字段
1
aaa
2
bbb
3
cccc
===========================
B 表

A字段 C字段
1
jjjjj
2
flala
1
ajkjf
3
jjjjj
2
jjjjj
===========================
C 表
A字段 D字段
1
KKKK
3
ueiaa
2
KKKK
1
KKKK
3
KKKK
2
KKKK
===========================
D 表

A字段
E字段(在B表中满足bb.c='jjjjj'的记录数)
F字段(在C表中满足cc.d='KKKK'的记录数)
1select count(*) from B bb where bb.c='jjjjj' and bb.a=1;
select count(*) from C ccwhere cc.d='KKKK' and cc.a=1;
2
select count(*) from B bb where bb.c='jjjjj' and bb.a=2;
select count(*) from C ccwhere cc.d='KKKK' and cc.a=2;
3
select count(*) from B bb where bb.c='jjjjj' and bb.a=3;
select count(*) from C ccwhere cc.d='KKKK' and cc.a=3;
===========================
我想按表A,字段A分组统计出满足条件的数量，然后插入到D表，实行的结果如上面的D表，希望用一条SQL语句实行，其中C表
有百万级的数据，按字段A分了区，希望求得一条高效的语句，谢谢了！！

千问 · 2007-1-24 12:58:48

insert into d
select a.a,
(select count(*) from B bb where bb.c='jjjjj' and bb.a=a.a )
,(select count(*) from C cc where cc.d='KKKK' and cc.a=a.a )
from a

千问 · 2007-1-24 12:58:48

不行啊，老大，执行效率很低啊

千问 · 2007-1-24 12:58:48

WITH CC AS (SELECT A, COUNT(*)C_COUNT FROM C WHERE C.D='kkkk' GROUP BY A),
BB AS (SELECT A, COUNT(*) B_COUNT FROM B WHERE B.C='jjjj' GROUP BY A)
INSER INTO D
SELECT aaa.A, bbb.B_COUNT, ccc.C_COUNT
FROM A aaa, BB bbb, CC ccc
WHERE aaa.A = bbb.A AND aaa.A = ccc.A