是否使用并集显示两个相同的记录，但不显示不必要的记录

Question

我使用的是UNION，直到今天还可以。我在第二个查询中遇到了一个问题，即有可能出现两个或多个相同的记录，并且应该显示这些记录，但并集只会删除其中的一个。如果我放UNION ALL，那么会出现一些我不想要的不必要的重复。该怎么办？

select Com.CompanyCode, B.BranchCode,P.ProjectCode as 'ProjectCode',G.GLAccountNo,COA.ShortText, G.DocCurrency, G.DocCurrencyAmount, 
G.Currency as 'localCurrecny', G.Amount as 'LocalAmount', S.InvoiceNo as 'Reference', 
cast(S.BillingDocumentNumber as varchar(100)) as 'DocumentNumber', '' as 'Document Type',
cast(S.BillingDate as date) as 'PostingDate', cast(S.PODate as date) as 'DocmentDate', 
G.DC,'' as 'Clearing Document', S.AccountingAssignment as 'Assignment', 
'' as 'Department', '' as 'ServiceLineCode','' as 'ProjectCode', '' as 'OffSettingAcctNo', S.TextDescription as 'Text'
from GLedger G
left join Company Com on G.CompanyID = Com.CompanyID
left join Branch B on G.BusinessArea = B.BranchCode
--inner join LedgerTypes L on G.LedgerType_ID = L.ID
inner join Sales S on G.MainID = S.SalesID and G.LedgerType_ID = '3'
left join Projects P on S.AccAssignmentWBSElementID = p.Project_ID
Left join COA on G.GLAccountNo=COA.GLAccount
where
(G.CompanyID = @CompanyID)AND
((G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo))AND
((G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo))
AND (cast(S.BillingDate as date) between cast(@TempFromDate as date)
AND cast(@TempToDate as date))
AND ISNULL(g.Active,0)= 1
AND ((G.BusinessArea = @BusinessArea or @BusinessArea is null))
union
select Com.CompanyCode, B.BranchCode,P.ProjectCode as 'ProjectCode',G.GLAccountNo,COA.ShortText, G.DocCurrency, G.DocCurrencyAmount, 
G.Currency as 'localCurrecny', G.Amount as 'LocalAmount', '' as 'Reference', cast(G.DocumentNumber as varchar(100)) as 'DocumentNumber', 
'' as 'Document Type',sal.BillingDate as 'PostingDate', CAST(st.PricingDate as date) as 'DocmentDate', G.DC,'' as 'Clearing Document', '' as 'Assignment', 
D.DepartmentCode as 'Department', S.Name as 'ServiceLineCode', P.ProjectCode as 'ProjectCode', '' as 'OffSettingAcctNo', '' as 'Text'
from GLedger G
left join Company Com on G.CompanyID = Com.CompanyID
left join Branch B on G.BusinessArea = B.BranchCode
--inner join LedgerTypes L on G.LedgerType_ID = L.ID
inner join SalesTransactions ST on G.SubID = ST.SalesTransactionID and G.LedgerType_ID = '4'
left join Department D on G.DepartmentID = D.DepartmentID
left join ServiceLineMaster S on G.ServiceLineID = S.ID
left join Projects P on ST.ProjectID = p.Project_ID
Left join COA on G.GLAccountNo=COA.GLAccount
inner join Sales sal
on sal.SalesID= st.SalesID
where
(G.CompanyID = @CompanyID)AND
((G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo))AND
((G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo))
AND (cast(sal.BillingDate as date) between cast(@TempFromDate as date)
AND cast(@TempToDate as date))
AND ISNULL(g.Active,0)= 1
AND ((G.BusinessArea = @BusinessArea or @BusinessArea is null))

Answer 1

使用union all，然后编写一个外部查询，通过在where子句中定义不必要的重复项来过滤掉它们。

select * from (
select Com.CompanyCode, B.BranchCode,P.ProjectCode as 'ProjectCode',G.GLAccountNo,COA.ShortText, G.DocCurrency, G.DocCurrencyAmount, 
G.Currency as 'localCurrecny', G.Amount as 'LocalAmount', S.InvoiceNo as 'Reference', 
cast(S.BillingDocumentNumber as varchar(100)) as 'DocumentNumber', '' as 'Document Type',
cast(S.BillingDate as date) as 'PostingDate', cast(S.PODate as date) as 'DocmentDate', 
G.DC,'' as 'Clearing Document', S.AccountingAssignment as 'Assignment', 
'' as 'Department', '' as 'ServiceLineCode','' as 'ProjectCode', '' as 'OffSettingAcctNo', S.TextDescription as 'Text'
from GLedger G
left join Company Com on G.CompanyID = Com.CompanyID
left join Branch B on G.BusinessArea = B.BranchCode
--inner join LedgerTypes L on G.LedgerType_ID = L.ID
inner join Sales S on G.MainID = S.SalesID and G.LedgerType_ID = '3'
left join Projects P on S.AccAssignmentWBSElementID = p.Project_ID
Left join COA on G.GLAccountNo=COA.GLAccount
where
(G.CompanyID = @CompanyID)AND
((G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo))AND
((G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo))
AND (cast(S.BillingDate as date) between cast(@TempFromDate as date)
AND cast(@TempToDate as date))
AND ISNULL(g.Active,0)= 1
AND ((G.BusinessArea = @BusinessArea or @BusinessArea is null))
union all
select Com.CompanyCode, B.BranchCode,P.ProjectCode as 'ProjectCode',G.GLAccountNo,COA.ShortText, G.DocCurrency, G.DocCurrencyAmount, 
G.Currency as 'localCurrecny', G.Amount as 'LocalAmount', '' as 'Reference', cast(G.DocumentNumber as varchar(100)) as 'DocumentNumber', 
'' as 'Document Type',sal.BillingDate as 'PostingDate', CAST(st.PricingDate as date) as 'DocmentDate', G.DC,'' as 'Clearing Document', '' as 'Assignment', 
D.DepartmentCode as 'Department', S.Name as 'ServiceLineCode', P.ProjectCode as 'ProjectCode', '' as 'OffSettingAcctNo', '' as 'Text'
from GLedger G
left join Company Com on G.CompanyID = Com.CompanyID
left join Branch B on G.BusinessArea = B.BranchCode
--inner join LedgerTypes L on G.LedgerType_ID = L.ID
inner join SalesTransactions ST on G.SubID = ST.SalesTransactionID and G.LedgerType_ID = '4'
left join Department D on G.DepartmentID = D.DepartmentID
left join ServiceLineMaster S on G.ServiceLineID = S.ID
left join Projects P on ST.ProjectID = p.Project_ID
Left join COA on G.GLAccountNo=COA.GLAccount
inner join Sales sal
on sal.SalesID= st.SalesID
where
(G.CompanyID = @CompanyID)AND
((G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo))AND
((G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo))
AND (cast(sal.BillingDate as date) between cast(@TempFromDate as date)
AND cast(@TempToDate as date))
AND ISNULL(g.Active,0)= 1
AND ((G.BusinessArea = @BusinessArea or @BusinessArea is null))
)t
where .....