问个基础题，实在不会了

[qq_31871069]

sch xh
C01177450086 40
C01177450086 59 这是table1

rq sch xh zp jz
20171216 C01177450086 17 KSC010028-EHL0 POL-230W-R-01-NRT-ADS
20171217 C01177450086 59 KSC010028-EHL0 POL-230W-R-01-NRT-ADS
20171216 C01177450086 19 KSC010212-EHL0 POL_215FH_R_01_NRT_CLR_HADS_/ 这是table2，
要求table2出来以下结果

20171216 C01177450086 17 KSC010028-EHL0 POL-230W-R-01-NRT-ADS
20171216 C01177450086 19 KSC010212-EHL0 POL_215FH_R_01_NRT_CLR_HADS_/
求问如何写

[acen_chen]

SELECT b.* FROM table1 a left JOIN table2 b ON a.sch=b.sch and a.xh = b.xh where b.xh is null

[acen_chen]

反了 SELECT b.* FROM table1 a right JOIN table2 b ON a.sch=b.sch and a.xh = b.xh where a.xh is null

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
GO
CREATE TABLE table1(sch VARCHAR(50),xh VARCHAR(50))
CREATE TABLE table2(rq VARCHAR(50), sch VARCHAR(50),xh VARCHAR(50),zp VARCHAR(50),jz VARCHAR(50))
GO
INSERT INTO table1 VALUES('C01177450086','40')
INSERT INTO table1 VALUES('C01177450086','59')
INSERT INTO table2 VALUES('20171216','C01177450086','17','KSC010028-EHL0','POL-230W-R-01-NRT-ADS')
INSERT INTO table2 VALUES('20171217','C01177450086','59','KSC010028-EHL0','POL-230W-R-01-NRT-ADS')
INSERT INTO table2 VALUES('20171216','C01177450086','19','KSC010212-EHL0','POL_215FH_R_01_NRT_CLR_HADS_/')

SELECT distinct t2.* FROM table2 AS t2 
WHERE t2.xh NOT IN (
	SELECT t1.xh FROM table1 AS t1 INNER JOIN table2 tt2 ON  t1.sch=tt2.sch AND t1.xh=tt2.xh
)

[吉普赛的歌]

这样？

SELECT b.* FROM table1 a INNER JOIN table2 b ON a.sch=b.sch AND b.rq='20171216'

[qq_31871069]

引用 1 楼 yenange 的回复:

这样？

SELECT b.* FROM table1 a INNER JOIN table2 b ON a.sch=b.sch AND b.rq='20171216'

不是，和日期无关，就是在table2里面取出来满足table2里面sch，xh同时不等于table1里面的sch，xh的数据

[snlixing]

;with cte as (
select rq,t2.sch,t2.xh,t2.zp,t2.jz from table1 as t1,table2 as t2 where t1.sch=t2.sch and t1.xh=t2.xh
)
select * from table2 as t  where (select count(1) as num from cte where cte.rq = t.rq and cte.sch=t.sch and cte.xh=t.xh and cte.zp=t.zp and cte.jz=t.jz) = 0