同一张表进行多次左连接

Question

同一张表进行多次左连接

6

我有两个表格。第一个表格 => 成员 {成员编号，姓名，活跃状态} 第二个表格 => 储蓄 {储蓄编号，成员编号，月份，年份，金额，类型，已支付}

成员表格

+-----------+--------+--------+
| member_id | name   | active |
+-----------+--------+--------+
|       105 | Andri  | 1      |
|       106 | Steve  | 1      |
|       110 | Soraya | 1      |
|       111 | Eva    | 1      |
|       112 | Sonia  | 1      |
+-----------+--------+--------+

储蓄表

+------------+-----------+-------+------+--------+------+------+
| savings_id | member_id | month | year | amount | type | paid |
+------------+-----------+-------+------+--------+------+------+
|          1 |       120 |  NULL | NULL | 150000 |    1 | 1    |
|         14 |       105 |     7 | 2014 |  80000 |    2 | 1    |
|         15 |       105 |     7 | 2014 |  25000 |    3 | 1    |
|         16 |       105 |     7 | 2014 |  60000 |    4 | 1    |
|         17 |       105 |     7 | 2014 | 100000 |    5 | 1    |
|         18 |       106 |     7 | 2014 |  80000 |    2 | 1    |
|         19 |       106 |     7 | 2014 |  25000 |    3 | 1    |
|         20 |       106 |     7 | 2014 |  60000 |    4 | 1    |
|         21 |       106 |     7 | 2014 | 100000 |    5 | 1    |
|         31 |       110 |     7 | 2014 |  25000 |    3 | 1    |
|         32 |       110 |     7 | 2014 |  60000 |    4 | 1    |
|         33 |       110 |     7 | 2014 | 100000 |    5 | 1    |
|         34 |       111 |     7 | 2014 |  80000 |    2 | 1    |
|         35 |       111 |     7 | 2014 |  25000 |    3 | 1    |
|         36 |       111 |     7 | 2014 |  60000 |    4 | 1    |
|         37 |       111 |     7 | 2014 | 100000 |    5 | 1    |
|         38 |       112 |     7 | 2014 |  80000 |    2 | 1    |
|         39 |       112 |     7 | 2014 |  25000 |    3 | 1    |
|         40 |       112 |     7 | 2014 |  60000 |    4 | 1    |
|         41 |       112 |     7 | 2014 | 100000 |    5 | 1    |
|         85 |       105 |     7 | 2015 |  80000 |    2 | 1    |
|         86 |       105 |     7 | 2015 |  25000 |    3 | 1    |
|         87 |       105 |     7 | 2015 |  60000 |    4 | 1    |
|         88 |       105 |     7 | 2015 | 100000 |    5 | 1    |
|         89 |       106 |     7 | 2015 |  80000 |    2 |      |
|         90 |       106 |     7 | 2015 |  25000 |    3 |      |
|         91 |       106 |     7 | 2015 |  60000 |    4 |      |
|         92 |       106 |     7 | 2015 | 100000 |    5 |      |
|        101 |       110 |     7 | 2015 |  80000 |    2 |      |
|        102 |       110 |     7 | 2015 |  25000 |    3 |      |
|        103 |       110 |     7 | 2015 |  60000 |    4 |      |
|        104 |       110 |     7 | 2015 | 100000 |    5 |      |
|        105 |       111 |     7 | 2015 |  80000 |    2 | 1    |
|        106 |       111 |     7 | 2015 |  25000 |    3 | 1    |
|        107 |       111 |     7 | 2015 |  60000 |    4 | 1    |
|        108 |       111 |     7 | 2015 | 100000 |    5 | 1    |
|        109 |       112 |     7 | 2015 |  80000 |    2 |      |
|        110 |       112 |     7 | 2015 |  25000 |    3 |      |
|        111 |       112 |     7 | 2015 |  60000 |    4 |      |
|        144 |       110 |     7 | 2014 |  50000 |    1 | 1    |
+------------+-----------+-------+------+--------+------+------+

当会员进行储蓄时，他们可以选择5种类型的储蓄。我想做的是列出所有会员及其所有储蓄的清单。

这是 MySQL 查询语句。

SELECT m.member_id, name, 
SUM(s1.amount) as savings1,
SUM(s2.amount) as savings2,
SUM(s3.amount) as savings3,
SUM(s4.amount) as savings4,
SUM(s5.amount) as savings5
FROM members m
LEFT JOIN savings s1 ON s1.member_id = m.member_id AND s1.type = 1 AND s1.paid = 1
LEFT JOIN savings s2 ON s2.member_id = m.member_id AND s2.type = 2 AND s2.paid = 1
LEFT JOIN savings s3 ON s3.member_id = m.member_id AND s3.type = 3 AND s3.paid = 1
LEFT JOIN savings s4 ON s4.member_id = m.member_id AND s4.type = 4 AND s4.paid = 1
LEFT JOIN savings s5 ON s5.member_id = m.member_id AND s5.type = 5 AND s5.paid = 1
WHERE 
active = 1
GROUP BY m.member_id

这是输出。

+-----------+--------+----------+----------+----------+----------+----------+
| member_id | name   | savings1 | savings2 | savings3 | savings4 | savings5 |
+-----------+--------+----------+----------+----------+----------+----------+
|       105 | Andri  |     NULL |  1280000 |   400000 |   960000 |  1600000 |
|       106 | Steve  |     NULL |    80000 |    25000 |    60000 |   100000 |
|       110 | Soraya |    50000 |     NULL |    25000 |    60000 |   100000 |
|       111 | Eva    |     NULL |  1280000 |   400000 |   960000 |  1600000 |
|       112 | Sonia  |     NULL |    80000 |    25000 |    60000 |   100000 |
+-----------+--------+----------+----------+----------+----------+----------+

如您所见，计算结果不正确，例如成员105的savings2应该是160K。有什么建议针对这种情况的查询语句吗？

http://sqlfiddle.com/#!2/9eca9/1

- l1th1um

假如按照'm.member_id, s.type'进行'GROUP BY'，然后围绕会员ID进行数据透视，这样不是更简单吗？ - Jacob Lambert

4个回答

5

您可能不需要多个左连接，可以这样完成：

SELECT 
m.member_id,
m.name,
SUM(case when s.type= 1 then s.amount end) as savings1,
SUM(case when s.type= 2 then s.amount end) as savings2,
SUM(case when s.type= 3 then s.amount end) as savings3,
SUM(case when s.type= 4 then s.amount end) as savings4,
SUM(case when s.type= 5 then s.amount end) as savings5
FROM savings s
join members m on m.member_id = s.member_id
WHERE 
m.active = 1
GROUP BY m.member_id

- Abhik Chakraborty

对于保存2中的member_id=106，我认为有一些错误。实际上，它变成了80000，而你的查询将返回160000。 - Sadikhasan

你需要添加一个额外的where条件，例如s.paid = 1，以便获得正确的结果。 - Sadikhasan

@Sadikhasan，是的，添加条件后只有80000，但似乎OP正在寻找会员105的储蓄2应该为160K。 - Abhik Chakraborty

当您添加条件s.paid = 1时，还满足用户要求并得到了正确的输出。 - Sadikhasan

2

这应该可行

SELECT m.member_id, name, 

sum((case when s1.type=1 then s1.amount end)) as savings1,

sum((case when s1.type=2 then s1.amount end)) as savings2,

sum((case when s1.type=3 then s1.amount end)) as savings3,

sum((case when s1.type=4 then s1.amount end)) as savings4,

sum((case when s1.type=5 then s1.amount end)) as savings5

FROM members m

LEFT JOIN savings s1 ON s1.member_id = m.member_id 

WHERE active = 1 and s1.paid=1

GROUP BY m.member_id

- user3800108

0

看起来 join 有问题.. 你可以参考以上答案，它们都是不错的选择。

你也可以使用Pivot Query。

SELECT m.member_id,s1.type,
SUM(s1.amount) as savings
FROM members m
LEFT JOIN savings s1 ON s1.member_id = m.member_id  AND s1.paid = 1
WHERE active = 1
GROUP BY m.member_id ,s1.type

// 在这里进行枢轴操作

然后尝试进行枢轴操作，这也会很好...

- sarathkumar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- lc. · Accepted Answer

问题是您在求和之前形成了完整的联接乘积。因此，如果您的“储蓄”表中有一个以上的行，则会出现重复项。如果您在没有求和的情况下进行联接，可以清楚地看到正在发生的事情。有两种解决方法。

在派生表中执行所有汇总：

SELECT m.member_id, name, 
s1.amount as savings1,
s2.amount as savings2,
...
FROM members m
LEFT JOIN (
    select SUM(amount) as amount, member_id
    from savings
    where type = 1 and paid = 1
    group by member_id
) s1 ON s1.member_id = m.member_id
LEFT JOIN (
    select SUM(amount) as amount, member_id
    from savings
    where type = 2 and paid = 1
    group by member_id
) s2 ON s2.member_id = m.member_id
...
WHERE active = 1
GROUP BY m.member_id

一次加入，使用条件求和：

SELECT m.member_id, name, 
    SUM(CASE WHEN s.type = 1 then s.amount ELSE NULL END) as savings1,
    SUM(CASE WHEN s.type = 2 then s.amount ELSE NULL END) as savings2,
    ...
LEFT JOIN savings s s2 ON s.member_id = m.member_id AND s.paid = 1
WHERE active = 1
GROUP BY m.member_id