如何在sql中获得两个日期之间所有列都不同的行?

--This is for MSSQL Server
    with CTE as(
    select *,(row_number() over (partition by MODEM_SERIAL_NUMBER order by MODEM_SERIAL_NUMBER)) 'ROW_N'  from T_RECORDED_TEST   
    where OFFICE_ID='1710011001123' and RECORD_DATE > '2017-11-01' and RECORD_DATE < '2017-12-08' and TEST_RESULT = 'TRUE')
    select MODEM_MODEL,MODEM_SERIAL_NUMBER,OFFICE_ID,RECORD_DATE,RECORD_ID,TEST_RESULT  from CTE where ROW_N=1

使用 GROUP BY 功能和 MAX(ROWID) 假设您需要group by值上的最新行。

SELECT * FROM T_RECORDED_TEST 
WHERE ROWID IN (
    SELECT MAX(ROWID)
    FROM T_RECORDED_TEST
    where office_id='1710011001123' 
    and record_date > '2017-11-01' 
    and record_date < '2017-12-08' 
    and test_result = 'true' 
    GROUP BY MODEM_SERIAL_NUMBER;
)

*是罪魁祸首。如果您只对MODEM\u SERIAL\u NUMBER感兴趣,可以使用distinct,如下所示。

SELECT DISTINCT
    ( modem_serial_number )
FROM
    t_recorded_test
WHERE
    office_id = '1710011001123'
    AND   record_date > '2017-11-01'
    AND   record_date < '2017-12-08'
    AND   test_result = 'true';