主页 / 问题 / 608352
Accepted
Илья Индиго
Asked:2020-12-27 16:33:06 +0000 UTC2020-12-27 16:33:06 +0000 UTC

多个表的 LEFT JOIN 产生重复项

  • 772

我正在为在线交易平台制作计费系统。在表格中tbl_user- 用户，tbl_billing_refill- 余额充值，tbl_billing- 购买/销售（如果id用户匹配buid- 这是他的购买，如果匹配 -suid这是他的销售）

id当前的任务是正确计算=1的特定用户的余额。

简化的测试数据。

CREATE TABLE `tbl_user`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `tbl_user` (`name`) VALUES ('Вася'), ('Петя'), ('Боря');


CREATE TABLE `tbl_billing_refill`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uid` INT UNSIGNED NOT NULL,
    `sum` DECIMAL(9,2) NOT NULL
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `tbl_billing_refill` (`uid`, `sum`) VALUES
(1, 1), (1, 10), (1, 100), (1, 100), (2, 2), (2, 20), (3, 300);


CREATE TABLE `tbl_billing`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `buid` INT UNSIGNED NOT NULL,
    `suid` INT UNSIGNED NOT NULL,
    `sum` DECIMAL(9,2) UNSIGNED NOT NULL
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `tbl_billing` (`buid`,`suid`,`sum`) VALUES
(1, 3, 1), (1, 2, 10), (1, 2, 10), (1, 2, 100), (2, 1, 2), (2, 1, 2), (2, 1, 20), (3, 1, 300);

请求是这样提出的：

1 我将用户表作为一个保证包含id用户数据的表，这样就可以附加一些东西，否则我不需要它的数据。

2 我需要一个OUTERJOIN，因为除 外的任何表tbl_user，一次全部包含在内，可能不包含有关此用户的数据。同样，我使用IFNULL从NULL一个包含空数据的表转换为0.

3GROUP BY u.id包括在内，因为我使用了聚合函数SUM，这需要，或者在 10.0 之前要求强制分组。从 10.0 开始，MariaDB 中的查询已经在没有它的情况下运行，但我出于习惯而放了它。

如果你有这个问题，请解释我做的是否正确？

4 当前余额计算如下（存款总和）-（购买总和）+（销售总和）对于用户c id=1（1+10+100+100）-（1+10+10+100）+ (2+2+20) +300) = 211 - 121 + 324 = 414

SELECT (IFNULL(SUM(r.sum),0) - IFNULL(SUM(b.sum),0) + IFNULL(SUM(s.sum),0)) `sum`
FROM tbl_user `u`
LEFT JOIN tbl_billing_refill `r` ON u.id=r.uid
LEFT JOIN tbl_billing `b` ON u.id=b.buid
LEFT JOIN tbl_billing `s` ON u.id=s.suid
WHERE u.id=1 GROUP BY u.id;

但问题是，请求返回的不是预期的4146624 ，而是= 414 * 16，即相同的数据被复制了 16 次。

通常，在这种情况下，当我有COUNT( id) 时，我会把所有东西都放进去DISTINCT，一切COUNT都会正常工作。我知道我是在掩盖原因而不消除影响，但我不了解原因，也无法消除它。现在是这种情况，当你不能再把它放在SUM( sum)DISTINCT中时，因为它会删除必要的重复项，我在测试数据中特别包含了这些重复项。

帮助我正确和优化地撰写请求并展示如何消除这个原因，我一直用DISTINCT-th 掩盖这个原因，这样我就不会再这样做了。:-)

mysql

1 个回答

  1. Best Answer

    您需要在子查询中单独计算金额，然后计算余额。

    SELECT rsum - bsum + ssum `sum`
FROM tbl_user `u`,
    (
    SELECT COALESCE(SUM(r.sum),0) rsum
    FROM tbl_billing_refill `r`
    WHERE r.uid=1
    ) q1,
    (
    SELECT COALESCE(SUM(b.sum),0) bsum
    FROM tbl_billing `b`
    WHERE b.buid=1
    ) q2,
    (
    SELECT COALESCE(SUM(s.sum),0) ssum
    FROM tbl_billing `s`
    WHERE s.suid=1
    ) q3
WHERE u.id=1

    但是，这里不需要 tbl_user 表......

    应 alexoander 的要求，为多个用户提供一个选项：

    SELECT u.id, q1.rsum - q2.bsum + q3.ssum `sum`
FROM tbl_user `u`,
    (
    SELECT r.uid id, COALESCE(SUM(r.sum),0) rsum
    FROM tbl_billing_refill `r`
    GROUP BY r.uid
    ) q1,
    (
    SELECT b.buid id, COALESCE(SUM(b.sum),0) bsum
    FROM tbl_billing `b`
    GROUP BY b.buid
    ) q2,
    (
    SELECT s.suid id, COALESCE(SUM(s.sum),0) ssum
    FROM tbl_billing `s`
    GROUP BY s.suid
    ) q3
WHERE u.id=q1.id
  AND u.id=q2.id
  AND u.id=q3.id
-- AND u.id IN (1,3,5,128)

    如果您需要由少数用户选择，添加 resp 是有意义的。WHERE 子查询。

    • 4

相关问题