iT邦幫忙

0

SQL 當資料庫是 空白 或是 x 時

請問SQL 資料庫來源是
https://ithelp.ithome.com.tw/upload/images/20181030/20109425R1eypTgNcY.jpg

數值是空白和 X 時 怎麼使用 where 去排除他

我是寫這樣


SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime FROM TEGPXS03 WHERE SiteName= '彰化(大城)' IS NOT NULL AND 'X' ORDER BY PublishTime DESC   

好像不行 Help !~
/images/emoticon/emoticon06.gif

因為資料庫 是空白 不是NULL 然後是 一個X 但又好像x
不曉得where 怎下判斷排除

2 個回答

4
暐翰
iT邦大師 2 級 ‧ 2018-10-30 10:46:44
最佳解答

數值是空白和 X 時 怎麼使用 where 去排除他

使用is not null and <> 'X'

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE  /*篩選其他條件*/ (SiteName IS NOT NULL AND SiteName <> 'X') /*排除數值是空白和X*/
ORDER BY PublishTime DESC   

第二個問題

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 
is not null AND   PM2_5 , PM10, CO  , SO2 , NO2 ,O3  <> 'X'  
ORDER BY PublishTime DESC

不行= = 卡
is附近語法說不正確

回答:
sql沒有那麼智能,不能多個欄位判斷

PM2_5 , PM10, CO  , SO2 , NO2 ,O3  <> 'X'

要這樣寫才對

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 	
	and (PM2_5 is not null and PM2_5 <> 'X')
	and (PM10 is not null and PM10 <> 'X')
	and (CO is not null and CO <> 'X')
	and (SO2 is not null and SO2 <> 'X')
	and (NO2 is not null and NO2 <> 'X')
	and (O3 is not null and O3 <> 'X')
ORDER BY PublishTime DESC

第三個問題

暐翰大大
您的方法使用可以 畫面真的都把X 都取消了 也把 空白取消
但是
如果一排都空白卻..
https://ithelp.ithome.com.tw/upload/images/20181030/20109425FxlqhaDufz.jpg
還是顯示出來

回答:
需要提供你使用什麼資料庫
因為你的問題應該還要篩選掉'',而各家資料庫ISNULL語法可能不同
你先試試看這個版本

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 	
	and (ISNULL(PM2_5,'') <> '' and PM2_5 <> 'X')
	and ( ISNULL(PM10,'') <> ''  and PM10 <> 'X')
	and ( ISNULL(CO,'') <> '' and CO <> 'X')
	and ( ISNULL(SO2,'') <> '' and SO2 <> 'X')
	and ( ISNULL(NO2,'') <> '' and NO2 <> 'X')
	and ( ISNULL(O3,'') <> '' and O3 <> 'X')
ORDER BY PublishTime DESC   
看更多先前的回應...收起先前的回應...
ted8224 iT邦新手 5 級 ‧ 2018-10-30 11:27:40 檢舉


SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 
is not null AND   PM2_5 , PM10, CO  , SO2 , NO2 ,O3  <> 'X'  
ORDER BY PublishTime DESC   

不行= = 卡
is附近語法說不正確/images/emoticon/emoticon02.gif

暐翰 iT邦大師 2 級 ‧ 2018-10-30 11:34:27 檢舉

sql沒有那麼智能,不能多個欄位判斷

PM2_5 , PM10, CO  , SO2 , NO2 ,O3  <> 'X'  

要這樣寫才對

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 	
	and (PM2_5 is not null and PM2_5 <> 'X')
	and (PM10 is not null and PM10 <> 'X')
	and (CO is not null and CO <> 'X')
	and (SO2 is not null and SO2 <> 'X')
	and (NO2 is not null and NO2 <> 'X')
	and (O3 is not null and O3 <> 'X')
ORDER BY PublishTime DESC   
ted8224 iT邦新手 5 級 ‧ 2018-10-30 11:52:28 檢舉

暐翰大大
您的方法使用可以 畫面真的都把X 都取消了 也把 空白取消
但是
如果一排都空白卻..
https://ithelp.ithome.com.tw/upload/images/20181030/20109425FxlqhaDufz.jpg

還是顯示出來
/images/emoticon/emoticon02.gif

slime iT邦大師 1 級 ‧ 2018-10-30 11:55:58 檢舉

錦上添花:

where isnull(PM10,'x') != 'x'

ted8224
這招你試試

SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)' 	
	and ISNUMERIC(PM2_5) = 1
	and ISNUMERIC(PM10) = 1
	and ISNUMERIC(CO) = 1
	and ISNUMERIC(SO2) = 1
	and ISNUMERIC(NO2) = 1
	and ISNUMERIC(O3) = 1
ORDER BY PublishTime DESC  
暐翰 iT邦大師 2 級 ‧ 2018-10-30 13:25:18 檢舉

我更新回答在文章

ted8224 iT邦新手 5 級 ‧ 2018-10-30 14:32:17 檢舉
SELECT TOP 100 PM2_5 , PM10, CO  , SO2 , NO2 ,O3 , Pollutant , MsgContent  , SiteName  , PublishTime , CreatTime 
FROM TEGPXS03 
WHERE    SiteName= '彰化(大城)'  
 and (NOT PM2_5 ='' and NOT PM2_5 = 'X')
 and (NOT PM10 ='' and NOT PM10 = 'X')
 and (NOT CO ='' and NOT CO = 'X')
 and (NOT SO2 ='' and NOT SO2 = 'X')
 and (NOT NO2 ='' and NOT NO2 = 'X')
 and (NOT O3 ='' and NOT O3 = 'X')
ORDER BY PublishTime DESC;

這個也能

我要發表回答

立即登入回答