問題描述

我正在使用以下查詢來填充值的下拉列表.

I'm using the following query to populate a dropdown list of values.

select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing

我想對結果進行 A-Z 排序.我嘗試了以下方法:

I'd like to sort A-Z the results. I've tried the following:

select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
ORDER BY City ASC

但是我收到一個錯誤:

關鍵字聯合"附近的語法不正確.

Incorrect syntax near the keyword 'Union'.

此外，此查詢正在提取Blank or NULL"值并在下拉列表頂部顯示一個空格.如果可能的話，我想隱藏它.不顯示任何空值?

Additionally this query is pulling "Blank or NULL" values and displaying a blank space at the top of the drop-down. I'd like to hide that if possible. Not display any null value?

推薦答案

感謝大家的回復，它們讓我對在哪里查找問題有了很多見解.添加以下內容的原始查詢獲得了正確的結果.

Thank you everyone for the responses it gave me a lot of insight on where to look for my problem. The original query with the addition of the below achieved the proper result.

工作查詢:

select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
where isnull(City,'') <> ''
Order by City ASC

選擇城市"始終位于下拉列表的頂部.感謝@scsimon 在我的另一篇文章中對此的貢獻.

with 'Select a City' always at the top of the dropdown. Credit to @scsimon on my other post for this.

with cte as(
select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
where isnull(City,'') <> '')


select * from cte  Order by case when City = 'Select a City' then 1 else 2     end, City ASC

這篇關于ORDER BY select 語句使用 UNION &清楚的的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！