代码之家 › 专栏 › 技术社区 › Mark

如何对不同列上的两个不同查询的结果求和?

mysql sql

Mark · 技术社区 · 1 年前

这里我的 customers 表:

身份证件	名称
1.	abc
2.	bcd
3.	cde
4.	def

以及 shifts 表:

身份证件	id1	id2
1.	1.
2.	2.	1.
3.	1.	3.

我的预期输出是:

名称	号码
abc	3.
bcd	1.
cde	1.
def	0

所以我只想数一数 customers.id 是在 轮班 桌子,无论是否在 id1 或 id2 。两者都可以是 null .

我可以显示一列的表格,例如:

SELECT t1.name, COUNT(t2.id1) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id1=t1.id
WHERE t2.id1=t1.id
GROUP BY t2.id1

当然,我也可以这样做 id2 . 但我如何将这两个结果相加呢?

我知道我可以用 + 操作员,例如:

COUNT(t2.id1)+COUNT(t2.id2)

但实际计算结果却大不相同,因为它也应该改变 JOIN 和 GROUP 条款。

因此,我试图按照建议对整个查询的结果进行求和 here :

SELECT q1.name, q1.num+q2.num
FROM 
    (SELECT t1.name, COUNT(t2.id1) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id1=t1.id
    WHERE t2.id1=t1.id
    GROUP BY t2.id1) AS q1,

    (SELECT t1.name, COUNT(t2.id2) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id2=t1.id
    WHERE t2.id2=t1.id
    GROUP BY t2.id2) AS q2
WHERE q1.name=q2.name

但自 WHERE 子句要求两个查询具有相同的内容 name 这是不可能的。如果我删除 哪里 子句结果是错误的,因为我丢弃了 名称 第二个问题。

如何对不同列上的两个不同查询的结果求和?

2 回复 | 直到 1 年前

Kliment Merzlyakov Barmar 1 年前

如果 shifts.id 如果是唯一的,那么您可以执行两次左连接,并按照@ysth的建议使用count distinct。

SELECT
  t1.name,
  COUNT(DISTINCT t2.id) + COUNT(DISTINCT t3.id) AS num
FROM customers t1
LEFT JOIN shifts t2 ON t2.id1 = t1.id
LEFT JOIN shifts t3 ON t3.id2 = t1.id
GROUP BY t1.name

或者,您可以使用两个子查询:

WITH shift1 AS (
  SELECT
    t1.name,
    COUNT(t2.id1) AS num
  FROM customers t1
  LEFT JOIN shifts t2 ON t2.id1 = t1.id
  GROUP BY t1.name
),

shift2 AS (
  SELECT
    t1.name,
    COUNT(t3.id1) AS num
  FROM customers t1
  LEFT JOIN shifts t3 ON t3.id2 = t1.id
  GROUP BY t1.name
),

shift_both AS (
  SELECT
    name,
    num
  FROM shift1

  UNION ALL

  SELECT
    name,
    num
  FROM shift2
)

SELECT
  name,
  SUM(num) AS num
FROM shift_both
GROUP BY name

Kliment Merzlyakov Barmar 1 年前

两者都使用 id1 和 id2 在你的 ON 条件。

SELECT t1.name, COUNT(t2.id) AS num
FROM customers t1
LEFT JOIN shifts t2
ON t1.id IN (t2.id1, t2.id2)
GROUP BY t1.name

使用 LEFT JOIN 和 COUNT(t2.shift) 因此,对于没有轮班的客户,您将获得零计数。

此解决方案假设您没有 id1 = id2 ,或者如果你这样做,他们不应该各自参与计数。

您还应该考虑将 shifts 桌子,类似于

身份证件
shift_number
customer_id