时间戳

首页 > 解决方案 > 根据 SQL 中查询的值添加指示“1”或“0”的列

sql - 根据 SQL 中查询的值添加指示“1”或“0”的列

问题描述

我目前正在尝试将值添加到指示客户是否为 VIP 的列中，VIP 客户已下达 10 个或更多订单。1 = VIP，0 = 非 VIP。

我编写了这个查询，它成功返回了一个列，该列提供了每个客户 ID 的订单计数。

SELECT *
FROM customer;

SELECT customer.customer_id, customer_name, address_line_1, city, state_province, count(order_header.customer_id) AS ordercount
FROM customer
    LEFT JOIN order_header ON customer.customer_id = order_header.customer_id
    GROUP BY customer.customer_id;

如何更改此设置以指示具有 >= 10 个订单的客户为“1”，而其他所有人为“0”？

此查询使用子查询，但每行的“ordercount”列是 6,921（订单总数）。

WITH OrderCountTable AS
(
    SELECT customer.customer_id, customer_name, address_line_1, city, state_province, count(order_header.customer_id) AS ordercount
    FROM order_header, customer
    GROUP BY customer.customer_id
)
SELECT *
FROM OrderCountTable
WHERE ordercount >= 10;

我很困惑，不知道如何实现我的目标，我觉得我把这件事复杂化了。任何帮助将不胜感激。

根据 Akina 的评论更新 #1

FROM (SELECT customer.customer_id, customer_name, address_line_1, city, state_province, count(order_header.customer_id) AS ordercount
    FROM customer
    LEFT JOIN order_header ON customer.customer_id = order_header.customer_id 
    GROUP BY customer.customer_id) AS vipTable

这将返回正确的数据集，但 isVIP 在每一行上都是错误的。我放错东西了吗？

标签： sql

解决方案

最终工作的解决方案：

SELECT customer_id, customer_name, address_line_1, city, state_province, CASE WHEN ordercount >= 10 THEN 1 ELSE 0 END AS is_vip_customer
FROM (SELECT customer.customer_id, customer_name, address_line_1, city, state_province, count(order_header.customer_id) AS ordercount
    FROM customer
    LEFT JOIN order_header ON customer.customer_id = order_header.customer_id 
    GROUP BY customer.customer_id) AS vipTable

推荐阅读