SQL中如何查询A表全部数据与A表有关联的B表部分数据

发布网友发布时间：2022-04-26 10:15

共5个回答

懂视网时间：2022-05-01 12:14

FROM A LEFT OUTER JOIN
B ON A.ID = B.ID
WHERE B.ID IS NULL

开发实例：

SELECT Position_Car.Area, Position_Car.CarMapNum, Position_Car.CheweiID, Position_Car.CarlotName,
Position_Car.Points
FROM Position_Car LEFT OUTER JOIN
TRelation_Equipment_Position ON TRelation_Equipment_Position.CheWeiID = Position_Car.CheweiID
WHERE (Position_Car.CarMapNum = @CarMapNum) AND (TRelation_Equipment_Position.CheWeiID IS NULL)

sql筛选查询A表中B表已经存在的数据

标签：

热心网友时间：2022-05-01 09:22

1、创建测试表，

create table test_col_1(id number, var varchar2(200));

create table test_col_2(id number, var varchar2(200));

2、插入测试数据，

insert into test_col_1

select level*8, 'var'||level*8 from al connect by level <= 20;

insert into test_col_2

select level, 'var'||level from al connect by level <= 100;

3、查询A表与B表关联记录，

select *

from test_col_2 b

where exists (select 1 from test_col_1 a where b.id = a.id)

4、查询A表全部数据及A、B有关联的数据，

select *

from test_col_1 a

union all

select *

from test_col_2 b

where exists (select 1 from test_col_1 a where b.id = a.id)

热心网友时间：2022-05-01 10:40

左连Left Join
表B中存了表A要的信息，相连字段假设是AID，即B表中有个字段AID，是与A表中ID相关联的
SELECT A.*, B.你要的字段 FROM A
LEFT JOIN B ON A.ID = B.AID

热心网友时间：2022-05-01 12:15

当b表没条件时，select * from A a left join B b on a.ID=b.ID

当b表有条件时

SELECT a.*,b.* FROM a

left join(SELECT * FROM b as bb WHERE bb.Field=1) AS b ON a.ID=b.ID

或者

with t as (select * from b where b.Field=1) select * from t right join a on t.ID=a.ID

热心网友时间：2022-05-01 14:06

select * from 表A名称 a left join 表B名称 b on a.关联字段=b.关联字段