我不能update或insert; 我只能select。我到目前为止所拥有的是
SELECT
m.id AS "Customer #", m.email AS "Email", p.purchased AS "Made Purchase"
FROM customer.profile AS m
LEFT JOIN customer.purchased AS p ON p.id = m.id
WHERE p.status = "SUCCESS"
GROUP BY m.id
customer.profile 表结构为：
| ID | 电子邮件 |
|---|---|
| 1个 | [email protected] |
| 2 | 行 |
| 3 | 行 |
| 4 | 行 |
| 5 | 行 |
customer.purchased 表结构为：
| Purchase_id | 顾客ID | 购买价格 | 采购状态 |
|---|---|---|---|
| 1个 | 1个 | $ 20.00 | “成功” |
| 2 | 2 | $ 20.00 | “待处理” |
| 3 | 3 | $ 20.00 | “成功” |
所需的输出SELECT是：
| Customer # | Email | Made Purchase |
|----------------|-----------------|-------------------|
| 1 | [email protected] | 1 |
| 2 | [email protected] | 0 |
| 3 | [email protected] | 0 |
| 4 | [email protected] | 1 |
| 5 | [email protected] | 0 |
customer.purchased将始终有customer_idfrom的子集customer.profile。我如何使用原始查询来解决这个问题？
我怀疑您想要：
SELECT m.id as customer_number, m.email AS email,
(p.id IS NOT NULL) AS made_purchase
FROM customer.profile m LEFT JOIN
customer.purchased p
ON p.id = m.id AND p.status = 'SUCCESS'
GROUP BY m.id
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句