SQL Server 分区和 Run Case 语句

发布于05月12日

我正在try 在**表B**中获取ID的最新记录.我try 对最上面的值进行排名，然后使用结果将以下条件应用于TableA中名为‘Status’的列:

如果end为空或空，则状态=‘当前正在运行’
如果最新记录是过go 48小时内的记录，则状态=‘最近完成’
如果结束时间超过48小时，则STATUS=‘超过48小时内未运行’
否则‘最近没有活动’

CREATE TABLE [dbo].[TableA](
    [ID] [varchar](50) NULL,
    [Status] [varchar](50) NULL
) 

CREATE TABLE [dbo].[TableB](
    [SiteId] [varchar](50) NULL,
    [db_addr] [varchar](50) NULL,
    [Start] [datetime] NULL,
    [End] [datetime] NULL,
    [ID] [varchar](50) NULL
) 
GO

INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z1001', N'')
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z1002', N'')
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z1003', N'')
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z3002', N'')

INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001',   CAST(N'2023-05-01T00:00:00.000' AS DateTime), CAST(N'2023-05-02T00:10:00.000' AS DateTime), N'Z1001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001', CAST(N'2023-05-02T01:00:00.000' AS DateTime), CAST(N'2023-05-02T01:10:00.000' AS DateTime), N'Z1001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001', CAST(N'2023-05-03T01:00:00.000' AS DateTime), CAST(N'2023-05-03T01:10:00.000' AS DateTime), N'Z1001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'2001', N'B002',     CAST(N'2023-05-01T00:00:00.000' AS DateTime), CAST(N'2023-05-02T00:10:00.000' AS DateTime), N'Z2001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'2001', N'B002', CAST(N'2023-05-02T01:00:00.000' AS DateTime), CAST(N'2023-05-02T01:10:00.000' AS DateTime), N'Z2001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'3001', N'B005', CAST(N'2023-05-02T01:00:00.000' AS DateTime), NULL, N'Z3001')
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'3002', N'C007', CAST(N'2023-05-10T01:00:00.000' AS DateTime), CAST(N'2023-05-10T01:00:00.000' AS DateTime), N'Z3002')

我试图编写一条SQL Partition语句，然后编写Case，但都出错了.

WITH cte AS (
  SELECT a.ID, Status,
    ROW_NUMBER() OVER (PARTITION BY b.db_addr, b.ID ORDER BY b.[End] DESC) AS rn,
    MAX(b.[End]) OVER (PARTITION BY b.db_addr, b.ID) AS latest_end
  FROM TableA a
  JOIN TableB b ON a.ID = b.ID
)
UPDATE a
SET Status = CASE
  WHEN cte.latest_end IS NULL OR cte.latest_end = '' THEN 'Currently Discharging'
  WHEN cte.rn = 1 AND cte.latest_end >= DATEADD(hour, -48, GETDATE()) THEN 'Discharged recently'
  ELSE 'Not discharged in more than 48hours'
  END
FROM TableA a
LEFT JOIN cte ON a.ID = cte.ID
WHERE a.ID IS NOT NULL;

with cte as ( select a.*, b.[End], ROW_NUMBER() OVER (partition by a.ID order by [End]) as rn from TableA a inner join TableB b on b.ID = a.ID ) select ID, case when [End] is null then 'Currently Running' when [End] >= DATEADD(day, -2, GETDATE()) then 'Recently FInished' when [End] < DATEADD(day, -2, GETDATE()) then 'not run in more than 48hours' else 'no recent activity' end as Status from cte where rn = 1

with cte as ( select a.*, b.[End], ROW_NUMBER() OVER (partition by a.ID order by [End]) as rn from TableA a inner join TableB b on b.ID = a.ID ) UPDATE TableA set TableA.Status = case when [End] is null then 'Currently Running' when [End] >= DATEADD(day, -2, GETDATE()) then 'Recently FInished' when [End] < DATEADD(day, -2, GETDATE()) then 'not run in more than 48hours' else 'no recent activity' end from cte c inner join TableA a on a.ID = c.ID where rn = 1

SQL Server 分区和 Run Case 语句

推荐答案

Sql相关问答推荐

Oracle中的分层查询

表名数组

retrofit AWS Athena中的JSON

查询每周数据(周一至周日)，避免年度日期重叠

SQL递归.硬币兑换问题.-try 使用递归解决硬币找零问题

表函数的作用域和功能

更正用于判断错误组合的SQL

AdventureWorks 查询

复制SQL Server临时表

正则表达式忽略特定数据部分的分隔符

计数时如何为所有时间间隔返回 0 而不是什么都不返回

在 SQL 中使用循环遍历按时间顺序排列的数据

我如何编写一个遍历数组数组并将所有值连接成一个字符串的 postgres 函数

计算 SQL 中的总体成功率：递归 CTE 还是替代方法？

在 PostgreSQL 中使用重音敏感排序进行重音不敏感搜索

从每行中排除最大元素

Athena：从字符串birth_dt列计算年龄

使用一组值进行分组和计数

在 Snowflake SQL 中计算通货inflating 率

在 AWS athena 的视图之上创建视图时，如何消除此错误：列别名列表有 1 个条目但t有 4 列可用？