SQL 跨表查询

2012-12-06 02:39:34 422 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Python爬虫之爬取并下载哔哩哔哩视频它可以爬取并下载视频文件和音频文件！亲自使用过，太好用了。阅读详情

A表字段 NO NAME
11111 张三
B表字段 NO MONEY
11111 1000
11111 500
11111 2000
22222 300
..... ...
C表字段 NO PRICE TYPE
11111 500 Retail
11111 2000 Retail
..... ... ......
求解，用一条SQL语句查询条件是B.NO=A.NO,C.NO=A.NO,C.TYPE="RETAIL"得出B.MONEY相加，和C.PRICE相加。

结果如下
NO NAME MONEY PRICE
11111 张三 3500 3500

...全文

WS2812B非标准通信协议：手把手实现精准时序控制详解WS2812B的精准时序实现原理，剖析其非标准通信协议的关键细节。通过实际操作指导，帮助开发者稳定驱动ws2812b灯带，解决常见时序偏差问题，提升项目显示效果与响应一致性。阅读详情

XinJW

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

习惯性蹭分 2012-12-06

打赏
举报


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

Mr_GY 2012-12-06

打赏
举报

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

Mr_GY 2012-12-06

打赏
举报

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

GiveMePressure 2012-12-06

打赏
举报

SELECT a.no, a.name, b.money, c.price FROM a INNER JOIN (SELECT b.no, SUM(b.money) AS money FROM b GROUP BY b.no)b ON a.no = b.no INNER JOIN (SELECT c.no, SUM(c.price) AS price FROM c WHERE c.type = 'Retail' GROUP BY c.no)c ON a.no = c.no

好帅的一条鱼 2012-12-06

打赏
举报

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 行受影响)

还在加载中灬 2012-12-06

打赏
举报

不明白第二个3500怎么来的

xuam 2012-12-06

打赏
举报

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

XinJW 2012-12-06

打赏
举报

引用 3 楼 good212164 的回复:

SQL code?123456789101112131415161718192021Create 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……

仰泳的鱼谢谢指教，太完美的答案了。 http://bbs.csdn.net/topics/390288622?page=1#post-392977725 这个贴子也没结，你回复一下，把分送你。谢谢。

我腫了 2012-12-06

打赏
举报

select a.NO ,isnull(sum(b.MONEY),0) AS MONEY ,isnull(sum(c.PRICE),0) AS PRICE from a left join b on a.NO=b.NO left join c on a.NO=c.NO and c.TYPE='Retail' grup by a.NO

SQL 跨表查询

相关推荐