sql - 如果满足条件，SQL 将状态应用于帐户的每一行

Question

我有以下数据集，

并希望创建一个“标志”，其中任何具有 bucket_type = 'Self-pay' 和 bucket_status = 'Outstanding' 的帐户都会收到与该帐户有关的所有行的描述“Self-pay Outstanding”。其他情况可视为“N/A”。像这样：

我对 row_count()、partition by 和 case 语句有些熟悉，但我不确定如何将它们组合在一起以实现我想要的。我有以下代码，我在我的案例陈述中被绊倒了。

select 
account
,bucket_type
,bucket_status
,case when bucket_type = 'Self-pay' and bucket_status = 'Outstanding' over (partition by account)
then 'Self-pay Outstanding' else null end 
from account_table

非常感谢！

标签： sqlsql-serversubquerywindow-functions

Answer 1

用一个常见的表表达式试试这个

SQL小提琴

MS SQL Server 2017 架构设置：

CREATE TABLE Account_Table
(
  Account VarChar(50),
  Bucket_Type VarChar(50),
  Bucket_Status VARCHAR(50)
 );
 
 INSERT INTO Account_Table
 VALUES
  ('A', 'Self-pay', 'Outstanding'),
  ('A', 'Prebilled', 'Closed'),
  ('A', 'Primary', 'Closed'),
  ('B', 'Self-pay', 'Closed'),
  ('B', 'Prebilled', 'Closed'),
  ('B', 'Primary', 'Outstanding'),
  ('C', 'Primary', 'Closed'),
  ('C', 'Secondary', 'Outstanding')

查询 1：

WITH CTE
AS
(
  SELECT DISTINCT Account, 'Self-pay Outstanding' as Flag
  FROM Account_Table
  WHERE 
    Bucket_Type = 'Self-Pay' AND
    Bucket_Status = 'Outstanding'
 )
 SELECT A.Account, A.Bucket_Type, A.Bucket_Status, COALESCE(CTE.Flag, 'N/A') AS Flag
 FROM Account_Table A
 LEFT JOIN CTE
   ON CTE.Account = A.Account

结果：

| Account | Bucket_Type | Bucket_Status |                 Flag |
|---------|-------------|---------------|----------------------|
|       A |    Self-pay |   Outstanding | Self-pay Outstanding |
|       A |   Prebilled |        Closed | Self-pay Outstanding |
|       A |     Primary |        Closed | Self-pay Outstanding |
|       B |    Self-pay |        Closed |                  N/A |
|       B |   Prebilled |        Closed |                  N/A |
|       B |     Primary |   Outstanding |                  N/A |
|       C |     Primary |        Closed |                  N/A |
|       C |   Secondary |   Outstanding |                  N/A |