求SQL语句：查询仅含1条非零结束日期折扣的账户

需求说明 #

从discounts表中筛选出符合以下条件的账户：

• 该账户仅有1条折扣记录
• 这条折扣记录的结束日期不等于0

原表数据 #

| account no        | discount no | status   |  end date |
|       14188971    |        111  | 1        | 12-DEC-23 | 
|       14188971    |        111  | 1        |      0    |
|       16743289    |        111  | 1        |      0    |
|       19908543    |        111  | 1        | 14-DEC-23 |
|       2510987     |        111  | 1        | 14-DEC-23 |
|       1663890     |        111  | 1        |      0    |
|       9033175     |        111  | 1        |      0    |  

期望结果 #

| account no        | end date  |
|       19908543    | 14-DEC-23 |
|       2510987     | 14-DEC-23 |

原SQL问题分析 #

你提供的SQL存在几个问题：

1. 不必要关联了account_t表，需求仅涉及discounts表的数据
2. WHERE子句中引用了未定义的pt.purchase_end_t字段，属于语法错误
3. 统计逻辑仅计算了特定折扣编号和状态的记录数，但需求是判断账户所有折扣记录总数为1
4. RIGHT JOIN会引入无折扣记录的账户，不符合需求

正确SQL示例 #

方式一：分组筛选（简洁版） #

SELECT 
    `account no` AS account_no,
    `end date` AS end_date
FROM 
    discounts
GROUP BY 
    `account no`, `end date`
HAVING 
    COUNT(*) = 1  -- 账户仅1条折扣记录
    AND `end date` <> '0'  -- 结束日期不为0

方式二：带额外过滤条件（若需限定折扣编号和状态） #

如果需要只考虑折扣编号为111且状态为1的记录，可添加WHERE过滤：

SELECT 
    `account no` AS account_no,
    `end date` AS end_date
FROM 
    discounts
WHERE 
    `discount no` = '111'
    AND `status` = 1
GROUP BY 
    `account no`, `end date`
HAVING 
    COUNT(*) = 1
    AND `end date` <> '0'

方式三：窗口函数（更灵活） #

使用窗口函数先计算每个账户的总折扣数，再筛选符合条件的记录：

WITH account_discount_stats AS (
    SELECT 
        `account no`,
        `end date`,
        COUNT(*) OVER (PARTITION BY `account no`) AS total_discounts
    FROM 
        discounts
    WHERE 
        `discount no` = '111'
        AND `status` = 1
)
SELECT DISTINCT
    `account no` AS account_no,
    `end date` AS end_date
FROM 
    account_discount_stats
WHERE 
    total_discounts = 1
    AND `end date` <> '0'

说明 #

• 由于表字段名包含空格，查询时需要用反引号（MySQL）或方括号（SQL Server）包裹字段名
• 三种方式都能准确筛选出仅有1条有效折扣记录的账户，可根据实际数据库语法和需求选择

内容的提问来源于stack exchange，提问作者pRieS