MySQL GROUP BY来自不同表的多个列 [英] MySQL GROUP BY multiple columns from different tables
问题描述
我有以下表格布局:
Table Data
+----------+-------------------------+
| Field | Type |
+----------+-------------------------+
| type | enum('type_b','type_a') |
| type_id | int(11) unsigned |
| data | bigint(20) unsigned |
+----------+-------------------------+
Table A and B:
+--------------+------------------+
| Field | Type |
+--------------+------------------+
| id | int(11) unsigned |
| customer_id | int(11) unsigned |
| ... |
+--------------+------------------+
在表数据中,有某些类型(a或b)的计量数据. 现在,我要永远向客户提供两种类型的数据a和b的总和.
In table Data there is some messurement data from a certain type (a or b). Now I want for ever customer the total sum for both types of data a and b.
所以,我想:选择总和,加入a或b并按a.customer_id,b.customer_id分组.
So, I thought: select the sum, join on a or b and group by a.customer_id, b.customer_id.
产生以下查询:
SELECT sum(d.data) as total
FROM data d, ta, tb
WHERE
(d.type LIKE "type_a" AND d.type_id = ta.id)
OR
(d.type LIKE "type_b" AND d.type_id = tb.id)
GROUP BY ta.customer_id, tb.customer_id;
这不能给我正确的结果...
This doesn't get me the proper results...
我尝试了几种方法,左联接,在customer表上联接,并按customer.id分组等.有人知道我在做什么错吗?
I tried several approaches, left joins, joining on the customer table and group by customer.id etc. Does anyone have a clue what I'm doing wrong?
感谢!
推荐答案
您的查询
SELECT sum(d.data) as total
FROM data d, ta, tb
WHERE
(d.type LIKE "type_a" AND d.type_id = ta.id)
OR
(d.type LIKE "type_b" AND d.type_id = tb.id)
GROUP BY a.customer_id, b.customer_id;
让我们说d中只有一条记录,它是type_a. ta和tb中分别有两个记录. d中的记录与d.type_id=ta.id
上ta中的记录之一匹配.因此,(d x ta)的组合允许任何tb记录保留在最终结果中.您会得到意想不到的笛卡尔积.
Let's say there is only one record in d, and it is type_a. There are two records in ta and tb each. The record in d matches one of the records in ta on d.type_id=ta.id
. Therefore, that combination of (d x ta) allows ANY tb record to remain in the final result. You get an unintended cartesian product.
SELECT x.customer_id, SUM(data) total
FROM
(
SELECT ta.customer_id, d.data
FROM data d JOIN ta
ON (d.type LIKE "type_a" AND d.type_id = ta.id)
UNION ALL
SELECT tb.customer_id, d.data
FROM data d JOIN tb
ON (d.type LIKE "type_b" AND d.type_id = tb.id)
) X
GROUP BY x.customer_id;
这篇关于MySQL GROUP BY来自不同表的多个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!