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

sql where null 空白

ted8224 2018-10-30 10:31:49 ‧ 34690 瀏覽

分享至

請問SQL 資料庫來源是

數值是空白和 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 !~

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

暐翰

iT邦大師 1 級 ‧ 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 都取消了也把空白取消
但是
如果一排都空白卻..

還是顯示出來

回答:
需要提供你使用什麼資料庫
因為你的問題應該還要篩選掉''，而各家資料庫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

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

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附近語法說不正確

暐翰 iT邦大師 1 級 ‧ 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 都取消了也把空白取消
但是
如果一排都空白卻..

還是顯示出來

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

錦上添花:

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

純真的人 iT邦大師 1 級 ‧ 2018-10-30 13:00:33 檢舉

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邦大師 1 級 ‧ 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;

這個也能

登入發表回應