如何使用EXCEPT或Union命令排除第一个查询数据 [英] How to exclude first query data using EXCEPT or Union command

问题描述

我要求从第二个查询数据中排除第一个查询数据我想分隔第二个查询数据最好的方法是什么？我们如何排除第一个查询EXCEPT和UNION语法为，当我添加UNION ALL所有第一个查询时，所有
数据都包含第二个查询数据。您可以就此提出建议。

I have requirement to exclude first query data from 2nd query data i want to separate second query data what is the best way for that how can we you exclude first query EXCEPT and UNION syntax for that, When i adding UNION ALL all first query all data include second query data.Can you please advise on this.

Sample Query-

Sample Query-

推荐答案

Requirement to exclude first query data from 2nd query

语法将为 -

SECOND QUERY

SECOND QUERY

除了

第一次查询;

这样的事情：

ALTER FUNCTION Report_DailyStatusReport --trainnig
	(
	@Consolidated VARCHAR(1),
	@IncludeArchivedVoyages CHAR(1),
	@CurrentCountry VARCHAR(2),
	@CompanyPK UNIQUEIDENTIFIER
	)
RETURNS TABLE
AS
RETURN
--Second Query
SELECT DISTINCT JS_UniqueConsignRef AS Booking_ID,
	JS_Direction AS Direction,
	odrref.OdrNum AS orderno
FROM JobShipment
LEFT JOIN JobPackLines ON JS_PK = JL_JS
LEFT JOIN JobcontainerPackPivot ON J6_JL = JL_PK
WHERE JS_IsBooking = 1
	AND JH_GC = @CompanyPK
	AND JS_IsCancelled = 0
	AND JS_IsForwardRegistered = 1
	AND @Consolidated = 'Y'
EXCEPT
--1st query
SELECT DISTINCT JS_UniqueConsignRef AS Booking_ID,
	JS_Direction AS Direction,
	odrref.OdrNum AS orderno
FROM JobShipment
LEFT JOIN JobPackLines ON JS_PK = JL_JS
LEFT JOIN JobcontainerPackPivot ON J6_JL = JL_PK
WHERE JS_IsBooking = 1
	AND JS_IsCancelled = 0
	AND JS_IsForwardRegistered = 0
	AND JH_GC = @CompanyPK
	AND (
		@IncludeArchivedVoyages = 'Y'
		OR JV_IsActive IS NULL
		OR JV_IsActive = 1
		)

这篇关于如何使用EXCEPT或Union命令排除第一个查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！