select a.NUMBER,b.name from 表1 a,表2 b where a.id = b.id and 查询条件
我没说清楚: 有一表A: ID PID NAME NUMBER 1 1 AAA 15 2 1 BBB 23 3 1 CCC 33 4 2 DDD 64 5 2 EEE 37 表B ID PID NAME 1 1 III 2 2 JJJ 其实我要的结果是: ID NAME NUMBER 1 III 71 2 JJJ 101
Select B.ID, B.NAME, SUM(A.NUMBER) As NUMBER From A Inner Join B On A.PID=B.PID Group By B.ID,B.NAME
我自己解决了,在A表中加字段BID,条件BID=PID, 完整如下: select A.name,a.nums from (SELECT pid , sum(NUMBER) as nums FROM A GROUP BY pid ) B ,A where A.bid=A.pid and B.pid=A.pid
為什麼加上BID??兩表不是都有PID,不是用那個關聯嗎??
我的表里原来有这个BID用来表示父子关系的
不清楚你這字段之間的關係where A.bid=A.pid and B.pid=A.pid感覺bid和pid應該是一樣的作用的啊。另外,你的子查詢應該可以不用。
where a.id = b.id and 查询条件
有一表A:
ID PID NAME NUMBER
1 1 AAA 15
2 1 BBB 23
3 1 CCC 33
4 2 DDD 64
5 2 EEE 37
表B
ID PID NAME
1 1 III
2 2 JJJ
其实我要的结果是:
ID NAME NUMBER
1 III 71
2 JJJ 101
Select
B.ID,
B.NAME,
SUM(A.NUMBER) As NUMBER
From A
Inner Join B
On A.PID=B.PID
Group By B.ID,B.NAME
完整如下:
select A.name,a.nums from
(SELECT pid , sum(NUMBER) as nums
FROM A
GROUP BY pid
) B ,A where A.bid=A.pid and B.pid=A.pid