2

## SQL 排除異常資料

``````SELECT TOP 8 M_DateTime ,A515V ,A515S  FROM dbo.P1234567_Hr_2018 WHERE DP_NO = 'A006'    ORDER BY M_DateTime DESC;

``````

ted8224 iT邦新手 5 級 ‧ 2018-08-14 15:03:06 檢舉

where子句多這個判斷式，where 狀態 not in ('031','032'~~~~,'038')

### 2 個回答

4

iT邦研究生 1 級 ‧ 2018-08-14 15:10:23

``````SELECT TOP 8 M_DateTime ,A515V ,A515S
FROM dbo.P1234567_Hr_2018
WHERE DP_NO = 'A006'
and A515S = '010'
ORDER BY M_DateTime DESC;
``````

``````SELECT TOP 8 M_DateTime ,A515V ,A515S
FROM dbo.P1234567_Hr_2018
WHERE DP_NO = 'A006'
and not A515S between '031' and '038'
ORDER BY M_DateTime DESC;
``````

ted8224 iT邦新手 5 級 ‧ 2018-08-14 15:15:56 檢舉

ted8224 iT邦新手 5 級 ‧ 2018-08-16 14:14:43 檢舉

``````SELECT TOP 8 M_DateTime ,A515V ,A515S
FROM dbo.P1234567_Hr_2018
WHERE DP_NO = 'A006'
and not A515S   IN  ( '020','021','026','031','038','030','035','M20','M21','M26','M31','M38','M30','M35')

``````

ted8224

ted8224 iT邦新手 5 級 ‧ 2018-08-17 08:24:58 檢舉

0
fuzzylee1688
iT邦新手 4 級 ‧ 2018-08-14 15:13:40

WITH mQuery AS (
SELECT M_DateTime ,A515V ,A515S,
ROW_NUMBER() OVER (ORDER BY M_DateTime DESC) AS ROW_NUM
FROM dbo.P1234567_Hr_2018 A
WHERE DP_NO = 'A006'
AND A515S NOT IN ('031', ..... , '038')
)
SELECT *
FROM mQuery
WHERE ROW_NUM <=8

ted8224 iT邦新手 5 級 ‧ 2018-08-14 15:22:08 檢舉