mdsk.net
当前位置:首页 >> sql 表连接查询怎么显示A表中的所以数据,B表中没有的显示null >>

sql 表连接查询怎么显示A表中的所以数据,B表中没有的显示null

使用左连接即可 查WX的SQL: select a.sid,a.sname,b.staff_id,b.enable from a left join (select sid,staff_id,enable from b where b.staff_id='wx') b on a.sid=b.sid 查ADMIN的SQL select a.sid,a.sname,b.staff_id,b.enable from a left join (select sid,staff_id,enable from b where b.staff_id='admin') b on a.sid=b.sid

保留A表全部结果需要和b表左外连接,查询wx:select a.*, b.enable from a left join b on a.sid=b.sid where b.staff_id='wx'查询admin:select a.*, b.enable from a left join b on a.sid=b.sid where b.staff_id='admin'

SELECT A.户名FROM TABLE_A A, TABLE_B BWHERE A.户名 = B.户名(+)WHERE B.户名 IS NULL还可以有其他方法:1 select distinct A.ID from A where A.ID not in (select ID from B)1 select A.ID from A left join B on A.ID=B.ID where B.ID is null

如果只比较一个字段select * from A where field1 not in (select field1 from B)如果比较多个字段select * from A where not exists(select * from B where field1 = A.field1 and field2 = A.field2)如果你的数据库支持except,还可以:select field1, field2, from Aexceptselect field1, field2, from B

EXISTS写法:SELECT A.条码 FROM TABLE1 A WHERE EXISTS(SELECT 1 FROM TABLE2 B WHERE A.条码 = B.ITEM_NO); INNER JOIN写法:SELECT A.*,B.* FROM TABLE1 A INNER JOIN TABLE2 B ON A.条码 = B.ITEM_NO; 还有其他的写法.小表关联可以用IN SELECT A.* FROM TABLE1 A WHERE A.条码 IN(SELECT B.ITEM_NO FROM TABLE2 B)

select B表字段 from B表 where B表字段 in (select A表字段 from A表) 这里查出来的数据就是A表B表都存在的数据.

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

select name from A where name not in (select name from B)有问题再追问,望采纳.

你可以将ab两个表整理成一个表,然后来查询既可以了 两个表整理成一个表:select t1.id id ,t1.name1 name,t2.number number, t1.其他信息 from table_a t1 left join table_b t2 on t1.id1=t2.id and t1.name1=t2.name union select t2.id id ,t1.name2 name,t2.number number, t1.其他信息 from table_a t1 left join table_b t2 on t1.id2=t2.id and t1.name2=t2.name

CREATE TABLE #tab_1( sname varchar(20) null, id int null)goinsert into #tab_1select '行业1',1 UNION select '行业2',2 UNIONselect '行业3',3 GOCREATE TABLE #tab_2( name varchar(20) null, tab1_id varchar(2000) null)goinsert into #tab_2select

相关文档
网站首页 | 网站地图
All rights reserved Powered by www.mdsk.net
copyright ©right 2010-2021。
内容来自网络,如有侵犯请联系客服。zhit325@qq.com