为什么此SQL查询返回完全重复的记录？

Touhid K.

我写了这个查询，这给了我确切的重复记录。

不知道为什么。请需要您的帮助。

我需要知道是什么原因导致查询显示重复记录。

这是查询，后面是模式。

询问：

select 
    p.Scode Property,
    IsNull(u.Scode, '') Unit,
    IsNull(ISNULL(t.sCode, tr.SD2), '') Payer,
    'R-' + RTrim(LTrim(Convert(Varchar(9), tr.Id-600000000))) Receipt,
    tr.uPDate 'Post Date',
    tr.sDateOccurred 'Date Occurred',
    tr.sTotalAmount 'Total Amount',
    Case tr.sAmountPaid 
       When Null Then '' When 0 Then 0 Else '' 
    End 'Amount Paid'
from 
    P p
INNER JOIN 
    TR tr ON tr.hprop = p.Id
INNER JOIN 
    GT dt ON isnull(tr.HP5,0) = Case when 'Non-P' = 'Non-P' Then 2 else dt.Id end
LEFT JOIN 
    T t on isnull(tr.HPERSON,0) = Case when 'Ten' = 'Non-Ten' Then 0 else t.Id end
LEFT JOIN 
    U u ON u.Id = tr.hUnit
WHERE
    1 = 1
    and p.Id = 99
    and tr.uPDate Between convert(datetime, '01-Apr-2015', 106) And convert(datetime, '01-Apr-2015', 106)
ORDER BY
    p.sCode, u.sCode, t.sCode, tr.SD2, tr.uPDate

架构：

Id int Primary key
Scode varchar(20)
...

Id int Primary Key
Scode varchar(20)
...

Id int Primary key
Scode varchar(20)
...

Id bigint Primary Key
hP int FK(P)
hP5 int
hPerson FK(T)
SD2 varchar(20)
hUnit int FK(U)
uPDate Date
...

Id int Primary Key
Scode varchar(20)
...

样本查询输出

lwheight                Laundry     R-1016071   2015-04-01  2015-04-17  350.00  0
lwheight                Laundry     R-1016071   2015-04-01  2015-04-17  350.00  0
lwheight    1104        t0026989    R-1009490   2015-04-01  2015-04-06  832.28  0
lwheight    1104        t0026989    R-1009490   2015-04-01  2015-04-06  832.28  0

Touhid K.

是的。乔纳森是对的。该案例陈述引起了问题。我将查询更改为此：

select p.Scode Property,
IsNull(u.Scode, '') Unit,
IsNull(ISNULL(t.sCode, tr.SD2), '') Payer,
'R-' + RTrim(LTrim(Convert(Varchar(9), tr.Id-600000000))) Receipt,
tr.uPDate 'Post Date',
tr.sDateOccurred 'Date Occurred',
tr.sTotalAmount 'Total Amount',
Case tr.sAmountPaid When Null Then '' When 0 Then 0 Else '' End 'Amount Paid'
from P p
INNER JOIN TR tr ON tr.hprop = p.Id
INNER JOIN GT dt ON isnull(tr.HP5,0) = gt.Id and gt.scode = 'z-nonper'
LEFT JOIN T t on isnull(tr.HPERSON,0) = t.Id
LEFT JOIN U u
ON u.Id=tr.hUnit
WHERE
1 = 1
and p.Id=99
and tr.uPDate Between convert(datetime, '01-Apr-2015', 106) And convert(datetime, '01-Apr-2015', 106)
ORDER BY
p.sCode,
u.sCode,
t.sCode,
tr.SD2,
tr.uPDate

并且它按要求工作。谢谢。:)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。