如何從聯合查詢中洗掉空行并對結果進行ASC排序。下面的查詢回傳正確的未經排序的資料，但它也包括一個空行。 我怎樣才能完成這項任務？如果有人能提供一個更有效的方法，我將非常感激。謝謝你
。SELECT TOP 1 'All rooms' AS [ SELECTION: ] FROM tbl1 UNION ALL
(SELECT DISTINCT Room1 FROM tbl1 Where Room1 IS NOT NULL UNION ALL
SELECT DISTINCT Room2 FROM tbl1 WHERE Room2 IS NOT NULL
UNIONALL
SELECT DISTINCT Room3 FROM tbl1 WHERE Room3 IS NOT NULL）。)
當前輸出：
所有房間
公共房間
201
20
20.1 20
'Null行在這里
256
預期：
All rooms
20
20.1
201
256 256
common Room
uj5u.com熱心網友回復：
嘗試改變第二行 - 你正在選擇room1 where room2 is not null，也許你的意思是where room1 is not null或者where room2 is not null and room1 is not null
SELECT TOP 1 'All rooms' AS [ SELECTION: ] FROM tbl1 UNION ALL
(SELECT DISTINCT Room1 FROM tbl1 Where Room1 IS NOT NULL UNION ALL
SELECT DISTINCT Room2 FROM tbl1 WHERE Room2 IS NOT NULL
UNIONALL
SELECT DISTINCT Room3 FROM tbl1 WHERE Room3 IS NOT NULL）。)
uj5u.com熱心網友回復：
我通過使用Len函式而不是IS NOT NULL，成功的解決了這個問題。
SELECT TOP 1 'All rooms' AS [ SELECTION: ] FROM tbl1
UNION ALL( SELECT DISTINCT Room1 As [SELECT LOCATION: ]
FROM tbl1 WHERE Len(Room1) >= 1
UNION SELECT DISTINCT Room2 FROM tbl1
WHERE Len(Room2)>=1
UNION SELECT DISTINCT Room3
FROM tbl1 WHERE Len（Room3）>= 1
ORDER BY [SELECTION:]）。)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/314454.html
標籤：
下一篇：Access查詢中空格前的文本