mysql - 检查值是否存在于另一个（连接的）表中并相应地在结果中填写列

Question

DB-小提琴

/* Table Sales */
CREATE TABLE sales (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    quantity INT
);

INSERT INTO sales
(campaign, quantity)
VALUES 
("C001", "125"),
("C002", "300"),
("C003", "700"),
("C004", "900"),
("C005", "600");


/* Table Tranches */
CREATE TABLE tranches (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    tranches VARCHAR(255),
    quantity INT
);

INSERT INTO tranches
(campaign, tranches, quantity)
VALUES 
("C001", "t1", "125"),
("C001", "t2", "300"),
("C001", "t3", "700"),
("C003", "t1", "600"),
("C003", "t2", "465");

预期结果：

   campaign     quantity   exists_in_tranches
    C001          125         yes
    C002          300         no
    C003          700         yes
    C004          900         no
    C005          600         no

---

在上面的示例中，我有两个名为salesand的表tranches。
如您所见，某些campaignsin tablesales确实在 table 中有一个条目tranches。其他一些则没有。

在查询结果的列exists_in_tranches中，我想检查表中是否存在某个campaign表，如果sales存在，tranches则应将其描述为yeselse 它应该是no。

---

像这样的东西：

SELECT
s.campaign,
s.quantity,
(CASE WHEN exists in table tranches THEN "yes" ELSE "no" END) AS exists_in_tranches
FROM sales s
LEFT JOIN tranches c ON c.campaign = s.campaign
GROUP BY 1;

---

我必须如何修改query才能达到预期的结果？

标签： mysqlsql

Answer 1

您可以尝试以下 - 检查is not null并且您不需要添加 group by 子句，因为您没有使用任何聚合函数 - DEMO Here

SELECT distinct
s.campaign,
s.quantity,
CASE WHEN c.campaign is not null THEN "yes" ELSE "no" END AS exists_in_tranches
FROM sales s
LEFT JOIN tranches c ON c.campaign = s.campaign