时间戳

首页 > 解决方案 > 如何将总计行从 order by 中排除并显示在表 ssms18 的底部

sql-server - 如何将总计行从 order by 中排除并显示在表 ssms18 的底部

问题描述

这是我现在的代码。输出表中有 4 行，我想知道如何在表底部显示总计行。由于 DESC 的顺序，它目前显示在顶部。

USE [MyData]

SELECT * INTO tableOne FROM [DataLib].[dbo].[Lease] -- carry over Lease table from Data Library to Reporting Database

--populate new table with the 3 commerical sectors and their uniqu
CREATE TABLE commercial_expirations (
    UniqueID varchar(255),
    PropertyType varchar(50))

INSERT INTO commercial_expirations
VALUES  ('Asset / Most Recent Quarter / All Assets / Sector | Industrial','Industrial'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Office','Office'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Retail','Retail'),
        ('Asset / Most Recent Quarter / All Assets / SectorType | Commercial','Total')

SELECT  c.PropertyType AS 'Property Type',
        e.SpaceArea_End AS 'RentableSF',
        FORMAT(e.LsDuration_WAvg, 'N1') AS 'WA Duration (Yrs)',
        FORMAT(e.[LsArea%_End], 'P1') AS '%Leased',
        FORMAT(e.[Year0ExpireArea%], 'P1') AS '2021',
        FORMAT(e.[Year1ExpireArea%], 'P1') AS '2022',
        FORMAT(e.[Year2ExpireArea%], 'P1') AS '2023',
        FORMAT(e.[Year3ExpireArea%], 'P1') AS '2024',
        FORMAT(e.[Year4ExpireArea%], 'P1') AS '2025',
        FORMAT(iif(e.[Year5ExpireArea%] IS NULL, 0, e.[Year5ExpireArea%]) + iif(e.[Year6ExpireArea%] IS NULL, 0, e.[Year6ExpireArea%]) + iif(e.[Year7ExpireArea%] IS NULL, 0, e.[Year7ExpireArea%]) + iif(e.[Year8ExpireArea%] IS NULL, 0, e.[Year8ExpireArea%]) + iif(e.[Year9ExpireArea%] IS NULL, 0, e.[Year9ExpireArea%])  + iif(e.[Year10ExpireArea%] IS NULL, 0, e.[Year10ExpireArea%]) + iif(e.[Year11PlusExpireArea%] IS NULL, 0, e.[Year11PlusExpireArea%]), 'P1')  AS 'Thereafter'

INTO ##Schedule

FROM commercial_expirations AS c
LEFT JOIN tableOne AS e
ON c.UniqueID = e.RecordID;

SELECT  [Property Type],
        FORMAT(RentableSF, 'N0') AS 'Rentable SF',
        [WA Duration (Yrs)],
        [2021],
        [2022],
        [2023],
        [2024],
        [2025],
        Thereafter

FROM ##Schedule
ORDER BY [RentableSF] DESC;



/*
DROP TABLE commercial_expirations
DROP TABLE expSummary
DROP TABLE ##Schedule
*/

标签： sql-serverssms

解决方案

要将总行强制到顶部，您可以添加

  CASE WHEN c.PropertyType = 'Total' THEN 1 ELSE 2 END

到ORDER BY子句的开头。

您还可以通过多种方式简化和改进此查询：

  • 更改commercial_expirations为 CTE 中的虚拟表
  • 将临时表组合成一个查询
  • 使用ISNULL代替IIF(... IS NULL
  • 用于[]引用列名
    • 理想情况下，您根本不需要引用列名，请谨慎选择您的名称
  • 按实际值排序，而不是格式化表示
WITH commercial_expirations AS (
    SELECT *
    FROM (VALUES  
        ('Asset / Most Recent Quarter / All Assets / Sector | Industrial','Industrial'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Office','Office'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Retail','Retail'),
        ('Asset / Most Recent Quarter / All Assets / SectorType | Commercial','Total')
    ) v(UniqueID, PropertyType)
)
SELECT  c.PropertyType AS [Property Type],
        FORMAT(e.SpaceArea_End, 'N0') AS RentableSF,
        FORMAT(e.LsDuration_WAvg, 'N1') AS [WA Duration (Yrs)],
        FORMAT(e.[LsArea%_End], 'P1') AS [%Leased],
        FORMAT(e.[Year0ExpireArea%], 'P1') AS [2021],
        FORMAT(e.[Year1ExpireArea%], 'P1') AS [2022],
        FORMAT(e.[Year2ExpireArea%], 'P1') AS [2023],
        FORMAT(e.[Year3ExpireArea%], 'P1') AS [2024],
        FORMAT(e.[Year4ExpireArea%], 'P1') AS [2025],
        FORMAT(
            ISNULL(e.[Year5ExpireArea%], 0) +
            ISNULL(e.[Year5ExpireArea%], 0) +
            ISNULL(e.[Year6ExpireArea%], 0) +
            ISNULL(e.[Year7ExpireArea%], 0) +
            ISNULL(e.[Year8ExpireArea%], 0) +
            ISNULL(e.[Year9ExpireArea%], 0) + 
            ISNULL(e.[Year10ExpireArea%], 0) + 
            ISNULL(e.[Year11PlusExpireArea%], 0), 'P1')  AS [Thereafter]
FROM commercial_expirations AS c
LEFT JOIN tableOne AS e
  ON c.UniqueID = e.RecordID;
ORDER BY
  CASE WHEN c.PropertyType = 'Total' THEN 1 ELSE 2 END,
  e.SpaceArea_End DESC;

推荐阅读