我正在运行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好的。这是好消息,我想补充一下,以防将来对其他人有帮助。我发现,由于ACCOUNTTYPECODE
和ZONE_DIVISIONCODE
来自表BIF003
,我需要在WHERE
语句中添加更多。
AND BIF030.C_CUSTOMER = BIF003.C_CUSTOMER
因为C_CUSTOMER
列不同(它是BIF003
和BIF030
表中的列),导致单独的ACCOUNTTYPECODE
结果,我需要在WHERE
语句中检查它。
谢谢大家让我思考这个问题。