主页 / dba / 问题 / 326673
Accepted
user1910240
Asked: 2023-05-03 00:16:28 +0800 CST

TSQL - 如何在一次选择中获取具有不同日期的多个值？

  • 772

在一条语句中从前两个日期获取值的最有效方法是什么？（Freespace-2 和 free space -3 将提前 2 天和 3 天）

SELECT 
  servername, 
  DriveName,
  DriveLabel, 
  TotalCapacity, 
  UsedCapacity, 
  FreeSpace, 
  FreeSpacePercentage, 
  '' AS [Freespace -2],
  '' AS [Frespace-3], logDate
FROM dba.DiskSpace
WHERE logDate>GETDATE()-1;


CREATE TABLE [dba].[DiskSpace](
    [servername] [varchar](100) NULL,
    [DriveName] [varchar](3) NULL,
    [DriveLabel] [varchar](100) NULL,
    [TotalCapacity] [decimal](10, 2) NULL,
    [UsedCapacity] [decimal](10, 2) NULL,
    [FreeSpace] [decimal](10, 2) NULL,
    [FreeSpacePercentage] [int] NULL,
    [logDate] [date] NULL
) ON [PRIMARY]
GO
sql-server

1 个回答

  1. Best Answer

    像下面这样的东西会帮助你：

    WITH DiskSpaceLag AS (
  SELECT
    ds.servername,
    ds.DriveName,
    ds.DriveLabel,
    ds.TotalCapacity,
    ds.UsedCapacity,
    ds.FreeSpace,
    ds.FreeSpacePercentage,
    ds.logDate,
    LAG(ds.FreeSpace, 2) OVER (PARTITION BY ds.servername, ds.DriveName ORDER BY ds.logDate) AS [Freespace -2],
    LAG(ds.FreeSpace, 3) OVER (PARTITION BY ds.servername, ds.DriveName ORDER BY ds.logDate) AS [Freespace -3]
  FROM dba.DiskSpace ds
)
SELECT *
FROM DiskSpaceLag
WHERE logDate = GETDATE();
    • 4

相关问题