SQL查询转义第一个记录显示降序

本文关键字：显示 降序 记录 第一个 查询 转义 SQL | 更新日期: 2023-09-27 18:17:42

我想右转查询

SELECT '0' AS PropertyDetailId, 'Select PropertyDetail' as PropertyName 
UNION SELECT PropertyDetailId,PropertyDetail.PropertyName 
FROM PropertyDetail join SiteDetail ON 
PropertyDetail.SiteDetailId = SiteDetail.SiteDetailId WHERE 
    PropertyDetail.IsActive = 1 
    AND PropertyDetail.SiteId = 6 
    AND SiteDetail.IsActive = 1 
    AND CustomerId = 0
ORDER BY PropertyName

但是我的PropertyDetailId = 0在下拉列表中。我想解决这个问题我的第一个记录escape from Order命令

SQL查询转义第一个记录显示降序

像这样使用"row number"字段:

SELECT 1 AS RN, '0' AS PropertyDetailId, 'Select PropertyDetail' as PropertyName 
UNION 
SELECT 2 AS RN, PropertyDetailId,PropertyDetail.PropertyName 
FROM PropertyDetail 
     JOIN SiteDetail ON PropertyDetail.SiteDetailId = SiteDetail.SiteDetailId 
WHERE 
    PropertyDetail.IsActive = 1 
    AND PropertyDetail.SiteId = 6 
    AND SiteDetail.IsActive = 1 
    AND CustomerId = 0
ORDER BY RN, PropertyName
另一种方法是在下拉菜单
中显示数据之前在客户端添加一条记录。

只要排除你不想要的。试试这个

SELECT '0' AS PropertyDetailId, 'Select PropertyDetail' as PropertyName 
    UNION SELECT PropertyDetailId,PropertyDetail.PropertyName 
    FROM PropertyDetail join SiteDetail ON 
    PropertyDetail.SiteDetailId = SiteDetail.SiteDetailId WHERE 
        PropertyDetail.IsActive = 1 
        AND PropertyDetail.SiteId = 6 
        AND SiteDetail.IsActive = 1 
        AND CustomerId = 0 
        AND PropertyDetailId <> 0
    ORDER BY PropertyName

试试下面这个。(SELECT PROPERTYDETAIL按值排序)

    SELECT '0' AS PROPERTYDETAILID
        ,'SELECT PROPERTYDETAIL' AS PROPERTYNAME
    UNION
    SELECT PROPERTYDETAILID
        ,PROPERTYDETAIL.PROPERTYNAME
    FROM PROPERTYDETAIL
    JOIN SITEDETAIL ON PROPERTYDETAIL.SITEDETAILID = SITEDETAIL.SITEDETAILID
    WHERE PROPERTYDETAIL.ISACTIVE = 1
        AND PROPERTYDETAIL.SITEID = 6
        AND SITEDETAIL.ISACTIVE = 1
        AND CUSTOMERID = 0
    ORDER BY CASE PROPERTYNAME WHEN   'SELECT PROPERTYDETAIL' THEN 1 END, PROPERTYNAME

   with aa as(
    SELECT '0' AS PropertyDetailId, 'Select PropertyDetail' as PropertyName 
    UNION SELECT PropertyDetailId,PropertyDetail.PropertyName 
    FROM PropertyDetail join SiteDetail ON 
    PropertyDetail.SiteDetailId = SiteDetail.SiteDetailId WHERE 
    PropertyDetail.IsActive = 1 
    AND PropertyDetail.SiteId = 6 
    AND SiteDetail.IsActive = 1 
    AND CustomerId = 0
    )
    Select * from aa 
    ORDER BY (case when PropertyDetailId='0' then PropertyDetailId else PropertyName end)
相关文章：