I've got the following table layouts:
我有以下表格布局:
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 |
| ... |
+--------------+------------------+
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)的messu量数据，现在我想永远为客户提供这两种类型的数据a和b的总和。
So, I thought: select the sum, join on a or b and group by a.customer_id, b.customer_id.
所以，我想:选择和，加入a或b，再加入a。customer_id b.customer_id。
Resulting in the following query:
产生以下查询:
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...
这并不能给我带来正确的结果……
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?
我尝试了几种方法，左连接，按客户表和组连接。有谁知道我做错了什么吗?
Thanx!
谢谢!
1 个解决方案
#1
3
Your query
您的查询
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;
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.
假设d中只有一个记录，它是type_a。在ta和tb中有两个记录。d中的记录匹配d.type_id=ta.id上的ta中的一条记录。因此，这种结合(dxta)允许在最终结果中保留任何结核病记录。你会得到一个非预期的笛卡尔积。
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;
#1
3
Your query
您的查询
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;
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.
假设d中只有一个记录，它是type_a。在ta和tb中有两个记录。d中的记录匹配d.type_id=ta.id上的ta中的一条记录。因此，这种结合(dxta)允许在最终结果中保留任何结核病记录。你会得到一个非预期的笛卡尔积。
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;