求教一个SELECT的写法

[rovoboy]

两个表A,B
A有以下几行
ID --?--
1 --?--
2 --?--
3 --?--
4 --?--
B有以下几行
ID AID NUM
1 1 2
2 1 3
3 2 4
4 2 7
5 3 2
6 4 2
7 2 4
其中AID表示A表中的ID，两表无任何关联

现要一结果如下

ID --?-- SUMNUM SUBNUM
1 --?-- 5(2+3) 2
2 --?-- 15(4+7＋4) 3
3 --?-- 2 1
4 --?-- 2 1

--?-- 表示其它无关数据

请问怎么写？多谢！

[wd_318]

select count(1) as ID,A.?,sum(B.NUM) as SUMNUM,sum(1) as SUBNUM from
A,B where A.ID=B.AID group by A.ID

[lyxinfo]

不知道你是不是想要这个：
select a.* ,(select sum(b.Num) from b where b.id = a.id),
(select count(b.num) from b where b.id = a.id)
from a

[wd_318]

搞错了，应该是：
select a.ID as ID,A.?,sum(B.NUM) as SUMNUM,sum(1) as SUBNUM from
A,B where A.ID=B.AID group by A.ID,A.? order by a.ID

[rovoboy]

wd_318(想当程序员):你的写法我会，我没说清楚，不好意思。
有没有不用列出表A中所有字段的写法？

[huier22]

看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书看书

[erickleung]

select B.ID ,A.-?-, sum(B.NUM) AS SUMNUM, count(B.NUM) AS SUBNUM from B
join A on B.ID=A.ID
group by B.ID,-?-;

[wd_318]

我倒不太明白了，如果全部列出都可以，那你指定几个字段不是一样的吗？既然不是用A.*，不需要的字段你不写就成啦

[redbat]

select a.*,bb.subnum,bb.sumnum from a,(select aid,count(aid) sumnum,sum(num) subnum from b group by aid) as bb where a.id = bb.aid

[rovoboy]

因为我的字段名是未知的，所以要用通配符去列。
前几天我这网坏了，没及时回复，不好意思。
我希望的是类似这种写法：（当然这种写法是错的）
select a.ID as ID,A.＊,sum(B.NUM) as SUMNUM,sum(1) as SUBNUM from
A,B where A.ID=B.AID group by A.ID,A.＊ order by a.ID
而不是把A中所有字段列出来。

[net_steven]

select A.*,C.SUMNUM,C.SUBNUM from A left join (select AID as ID,sum(B.NUM) as SUMNUM,count(AID) as SUBNUM from B group by B.AID) C
on A.ID=C.ID order by A.ID
------------------------------------------------------------
呵呵，两表还是有关联的。

[gongmk]

select ta.id, sum(tB.num), count(*) from ta, tb where ta.id = tb.aid group by ta.id
再sql2000上测试了一下，结果是：
1 5 2
2 15 3
3 2 1
4 2 1

[gongmk]

不好意思，发现上面的结果少了一项输出，那么就改成这样吧：
select ta.name, tc.* from ta, (select ta.id as id, sum(tB.num) as num1, count(*) as num2 from ta, tb where ta.id = tb.aid group by ta.id) as tc where ta.id = tc.id
在sql2000上的测试结果是：
name 1 1 5 2
name 2 2 15 3
name 3 3 2 1
name 4 4 2 1

[gongmk]

name 1, name 2, name 3, name 4 就是你的无关项--?--

[rovoboy]

多谢各位大虾，我抄回去试试。
学校的破网，现在还没好！！！火大。

[昵称被占用了]

提醒你一下，不要因为少输一些字符而把语句搞复杂了，要注意执行的效率！

[流星尔]

select A.*,C.SUMNUM,C.SUBNUM from A left join (select AID as ID,sum(B.NUM) as SUMNUM,count(AID) as SUBNUM from B group by B.AID) C
on A.ID=C.ID order by A.ID

[zwz99999]

select a.* ,(select sum(b.Num) from b where b.id = a.id),
(select count(b.num) from b where b.id = a.id)
from a

[wwq_jeff]

这个SQL执行速度比较好：

select a.id as id, max(a.?) as ? , sum(b.num) as sumnum, count(*) as subnum from a, b where a.id = b.id

[cxmcxm]

select a.id,a.--?--,b.sumnum,b.subnum from a left join
(select aid,sum(num) sumnum,count(*) subnum from b group by aid) b
on a.id=b.aid

[lishuguang]

afff