有A/B两表,要求读A表的集录集时能获取全部买下某片的价格A:
ID,TVName
1 007
2 大话西游B:
ID,TVID,TVNumber,TVPrice
1 1 01 20
2 1 02 30
3 1 03 40
4 2 01 28
5 2 02 38
ID,TVName
1 007
2 大话西游B:
ID,TVID,TVNumber,TVPrice
1 1 01 20
2 1 02 30
3 1 03 40
4 2 01 28
5 2 02 38
from a,b
where a.id=b.TVID
from tva a,tvb b
where a.id = b.tvid
group by a.id,a.tvname
from A
INSERT INTO C1 SELECT 1,'007'
UNION ALL SELECT 2,N'大话西游'CREATE TABLE C2(ID INT,TVID INT,TVNUM NVARCHAR(5),TVPRICE INT)
INSERT INTO C2 SELECT 1,1,'01',20
UNION ALL SELECT 2,1,'02',30
UNION ALL SELECT 3,1,'03',40
UNION ALL SELECT 4,2,'01',28
UNION ALL SELECT 5,2,'02',38SELECT C1.TVNAME,SUM(C2.TVPRICE) FROM C1
INNER JOIN C2 ON C2.TVID=C1.ID
GROUP BY C1.TVNAMEDROP TABLE C1
DROP TABLE C2RESULTS:
------------------------
007 90
大话西游 66