对于一列中的相同 ID，如何为未来日期编写 CASE 语句？

发布于 2025-01-16 20:58:26 字数 785 浏览 4 评论 0原文

对于下表，我想写一个CASE语句：对于同一个ID，如果多个日期是将来的并且Renew =“Yes”，则在Action栏中填写“Needs Review”。我假设应该使用 OVER(PARTITION BY() ，但不确定如何

原始表：

ID	Date	Renew
123	06-01-2023	是
123	06-01-2022	是
123	06-01-2021	是

预期结果（与CASE 声明）：

ID	日期	更新	操作
123	06-01-2023	是	需要审核
123	06-01-2022	是	需要审查
123	06-01-2021	是	需要审查

原文

For the following table, I want to write a CASE statement: For the same ID, if more than one Dates are in the future and Renew = "Yes," fill out the Action column with "Needs Review". I assume OVER(PARTITION BY() is supposed to be used, but not sure how

Original Table:

ID	Date	Renew
123	06-01-2023	Yes
123	06-01-2022	Yes
123	06-01-2021	Yes

Expected Outcome (with the CASE statement):

ID	Date	Renew	Action
123	06-01-2023	Yes	Needs Review
123	06-01-2022	Yes	Needs Review
123	06-01-2021	Yes	Needs Review

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

别理我 2025-01-23 20:58:26

尝试使用条件 SUM() 来计算标记为“更新”的行数以及未来日期。如果计数 > 则返回“需要审核” 1.

您没有指定数据库管理系统，但尝试

SQL Server:

SELECT t.*
       , IF(t.NumToReview > 1, 'Needs Review', NULL) AS [Action]
FROM (
       SELECT 
          Id
         , [Date]
         , [Renew]
         , SUM( CASE WHEN Date > cast(getDate() as date) 
                       AND Renew = 'Yes' THEN 1
                ELSE 0
           END
         ) OVER(PARTITION BY Id) AS NumToReview
      FROM  YourTable     
  ) t

MySQL:

SELECT t.*
       , IF(t.NumToReview > 1, 'Needs Review', NULL) AS Action
FROM (
       SELECT 
          Id
         , `Date`
         , Renew
         , SUM( CASE WHEN `Date` > CAST(now() AS DATE) 
                       AND Renew = 'Yes' THEN 1
                ELSE 0
           END
         ) OVER(PARTITION BY Id) AS NumToReview
      FROM  YourTable  
  ) t

结果:

Id	Date	Renew	NumToReview	Action
123	06-01- 2023	是	2	需要审查
123	06-01-2022	是	2	需要审查
123	06-01-2021	是	2	需要审查

Try using a conditional SUM() to count the number of rows marked "Renew", with future dates. Then return "Needs Review" if the count is > 1.

You didn't specify your dbms, but try

SQL Server:

SELECT t.*
       , IF(t.NumToReview > 1, 'Needs Review', NULL) AS [Action]
FROM (
       SELECT 
          Id
         , [Date]
         , [Renew]
         , SUM( CASE WHEN Date > cast(getDate() as date) 
                       AND Renew = 'Yes' THEN 1
                ELSE 0
           END
         ) OVER(PARTITION BY Id) AS NumToReview
      FROM  YourTable     
  ) t

MySQL:

SELECT t.*
       , IF(t.NumToReview > 1, 'Needs Review', NULL) AS Action
FROM (
       SELECT 
          Id
         , `Date`
         , Renew
         , SUM( CASE WHEN `Date` > CAST(now() AS DATE) 
                       AND Renew = 'Yes' THEN 1
                ELSE 0
           END
         ) OVER(PARTITION BY Id) AS NumToReview
      FROM  YourTable  
  ) t

Results: