重复行,因为1列有多个不同的值

  • 本文关键字:因为 1列 sql sql-server tsql
  • 更新时间 :
  • 英文 :


我正在运行SELECT查询以获取同一服务器实例中的多个表中的数据。然而,我刚刚注意到,在一些数据上的行被复制,因为我从主表中提取的其中一列中有几个不同的值。下面是查询:

SELECT DISTINCT BIF030.C_ACCOUNT AS ACCOUNTNUMBER,
BIF003.C_ACCOUNTTYPE AS ACCOUNTTYPECODE,
CON013.C_DESCRIPTION AS ACCOUNTTYPE,
BIF003.C_DIVISION AS ZONE_DIVISONCODE,
CON028.C_DESCRIPTION AS ZONE_DIVISION,
BIF030.C_METER as METERNUMBER,
BIF005.C_METERCUSTOM1 AS REGISTERNUMBER,
CONVERT(DECIMAL(20,2), BIF030.N_CONSUMP) AS CONSUMPTION,       
CON007.C_DESCRIPTION AS UNITS,       
BIF030.T_READDATE AS READINGDATE,
MONTH(BIF030.T_READDATE) AS READINGMONTH,
DAY(BIF030.T_READDATE) AS READINGDAY,
YEAR(BIF030.T_READDATE) AS READINGYEAR,
BIF030.I_DAYS AS READINGDAYSCOUNT

FROM ADVANCED.BIF030     
LEFT JOIN ADVANCED.CON007 ON CON007.C_UNITS=BIF030.C_UNITS
LEFT JOIN ADVANCED.BIF005 ON BIF005.C_METER=BIF030.C_METER
LEFT JOIN ADVANCED.BIF003 ON BIF003.C_ACCOUNT=BIF030.C_ACCOUNT
LEFT JOIN ADVANCED.CON013 ON CON013.C_ACCOUNTTYPE=BIF003.C_ACCOUNTTYPE
LEFT JOIN ADVANCED.CON028 ON CON028.C_DIVISION=BIF003.C_DIVISION

WHERE T_READDATE > '01-01-2014'

ORDER BY ACCOUNTNUMBER, READINGDATE ASC

我知道SELECT DISTINCT是不受欢迎的,但是如果没有它,我会得到更多的行。下面是提取数据时的样例:

READINGDAYSCOUNT

好的。这是好消息,我想补充一下,以防将来对其他人有帮助。我发现,由于ACCOUNTTYPECODEZONE_DIVISIONCODE来自表BIF003,我需要在WHERE语句中添加更多。

AND BIF030.C_CUSTOMER = BIF003.C_CUSTOMER

因为C_CUSTOMER列不同(它是BIF003BIF030表中的列),导致单独的ACCOUNTTYPECODE结果,我需要在WHERE语句中检查它。

谢谢大家让我思考这个问题。

相关内容

  • 没有找到相关文章

最新更新