34,588
社区成员
发帖
与我相关
我的任务
分享
Create table A表(NO char(8),NAME char(50))
Insert A表 select '11111','张三'
Create table B表(NO char(8),MONEY int)
Insert B表 select '11111','1000' union all
select '11111','500' union all
select '11111','2000' union all
select '22222','300'
Create table C表(NO char(8),PRICE int,TYPE char(8))
Insert C表 select '11111','500','Retail' union all
select '11111','2000','Retail'
select a.no,name,money,price
from A表 a join (
select no,SUM(money) as [money] from B表 group by NO
)as b
on a.no=b.NO
join
(select no,SUM(price) as price from
C表 where TYPE='retail'
group by NO) as c
on a.NO=c.NO
Create table A(NO char(8),NAME char(50))
Insert A
select '11111','张三'
Create table B(NO char(8),MONEY int)
Insert B
select '11111','1000' union all
select '11111','500' union all
select '11111','2000' union all
select '22222','300'
Create table C(NO char(8),PRICE int,TYPE char(8))
Insert C
select '11111','500','Retail' union all
select '11111','2000','Retail'
SELECT a.NO,a.NAME,SUM(b.money) AS money,
(SELECT SUM(c.price) FROM a,c WHERE a.NO=c.NO) AS price
FROM a,b WHERE a.NO=b.no GROUP BY a.NO,a.NAME
Create table A表(NO char(8),NAME char(50))
Insert A表
select '11111','张三'
Create table B表(NO char(8),MONEY int)
Insert B表
select '11111','1000' union all
select '11111','500' union all
select '11111','2000' union all
select '22222','300'
Create table C表(NO char(8),PRICE int,TYPE char(8))
Insert C表
select '11111','500','Retail' union all
select '11111','2000','Retail'
select A.*,SUM(C表.PRICE) as PRICE from
(select A表.NO,A表.NAME,SUM(B表.MONEY) as MONEY from A表
inner join B表 on A表.NO =B表.NO group by A表.NO ,A表.NAME) A,C表
where A.NO=C表.NO group by A.NO,A.NAME,A.MONEY
NO NAME MONEY PRICE
-------- -----------
11111 张三 3500 2500
(1 行受影响)
select A.NO, A.NAME,SUM(B.MONEY),SUM(C.PRICE)
from A inner join B on A.NO =B.NO
inner join C on A.NO =C.NO