#sql #sql-server #tsql
Вопрос:
Я беру UNION , что нормально, до сегодняшнего дня я столкнулся с одной проблемой во 2-м запросе, где есть вероятность появления двух или более одинаковых записей, и их следует показывать, но объединение, являющееся объединением, берет только одну из них. Если я поставлю 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))
Комментарии:
1. Вам придется определить необходимые и ненужные дубликаты для нас-потому что мы понятия не имеем. Требуются примерные данные и ожидаемые результаты.
Ответ №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 .....