SQLSERVER实现多列IN的几种方法

1. EXISTS

SELECT a.OBJECT_ID,ROW_NUMBER() OVER (PARTITION BY a.FHMDDM,a.SHMDDM,a.SJQJRJ ORDER BY a.HH) 
	,a.YDH,a.FHMDDM,a.SHMDDM,a.SJQJRJ
FROM dbo.CBZD a 
WHERE 
	a.JFZL IS NOT NULL AND 
	EXISTS (SELECT 1 FROM 
	
	(SELECT FHMDDM,SHMDDM,SJQJRJ
	FROM dbo.CBZD 
	WHERE JFZL IS NOT NULL AND  (bz IS NULL OR  bz not LIKE '%邮件订单%')
	GROUP BY FHMDDM,SHMDDM,SJQJRJ
	HAVING COUNT(*)>1
	) b 
	WHERE b.FHMDDM = a.FHMDDM AND b.SHMDDM=a.SHMDDM AND b.SJQJRJ=a.SJQJRJ)

2. +连接后join

SELECT a.OBJECT_ID,ROW_NUMBER() OVER (PARTITION BY a.FHMDDM,a.SHMDDM,a.SJQJRJ ORDER BY a.HH) 
	,a.YDH,a.FHMDDM,a.SHMDDM,a.SJQJRJ
FROM  dbo.CBZD  a JOIN 	(
	
	SELECT FHMDDM,SHMDDM,SJQJRJ
	FROM dbo.CBZD 
	WHERE JFZL IS NOT NULL AND  (bz IS NULL OR  bz not LIKE '%邮件订单%')
	GROUP BY FHMDDM,SHMDDM,SJQJRJ
	HAVING COUNT(*)>1
	
	) b ON a.FHMDDM+a.SHMDDM+CONVERT(NVARCHAR(10),a.SJQJRJ,121)=b.FHMDDM+b.SHMDDM+CONVERT(NVARCHAR(10),b.SJQJRJ,121)
WHERE 
	a.JFZL IS NOT NULL 

3. 正常join

SELECT a.OBJECT_ID,ROW_NUMBER() OVER (PARTITION BY a.FHMDDM,a.SHMDDM,a.SJQJRJ ORDER BY a.HH) 
	,a.YDH,a.FHMDDM,a.SHMDDM,a.SJQJRJ
FROM  dbo.CBZD  a JOIN 	(
	
	SELECT FHMDDM,SHMDDM,SJQJRJ
	FROM dbo.CBZD 
	WHERE JFZL IS NOT NULL AND  (bz IS NULL OR  bz not LIKE '%邮件订单%')
	GROUP BY FHMDDM,SHMDDM,SJQJRJ
	HAVING COUNT(*)>1
	
	) b ON b.FHMDDM = a.FHMDDM AND b.SHMDDM=a.SHMDDM AND b.SJQJRJ=a.SJQJRJ 
WHERE 
	a.JFZL IS NOT NULL