作者chan15 (ChaN)
看板Database
标题[SQL ] MySQL GROUP BY 的问题
时间Wed Apr 17 00:23:58 2013
今天有三个 table
// 计画
tbl_project
t_id int
t_name varchar
有一笔资料
t_id = 1
t_name = 'project a'
---------------------------------
// 计画订单
tbl_project_order
po_id int
po_name varchar
po_quantity int
po_price int
p_id int // tbl_project fk
有两笔资料
po_id = 1
po_name = 'order 1'
po_quantity = 1
po_price = 300
p_id = 1
po_id = 2
po_name = 'order 2'
po_quantity = 1
po_price = 500
p_id = 1
---------------------------------
// 计画细节
tbl_project_detail
pd_id int
pd_name varchar
p_id int // tbl_project fk
有两笔资料
pd_id = 1
pd_name = 'detail 1'
p_id = 1
pd_id = 2
pd_name = 'detail 2'
p_id = 1
今天要统计每个 project 订单的总额，SQL 语法如下
SELECT p.p_name, SUM(po.po_quantity*po.po_price) AS poMoney FROM tbl_project `p` LEFT JOIN tbl_project_order `po` ON po.p_id = p.p_id GROUP BY p.p_id;
结果会出现
p_name = 'project 1'
poMoney = 800
数字是对的，但另外要加入这个 project 的统计，於是修改结构如下
SELECT p.p_name, SUM(po.po_quantity*po.po_price) AS poMoney, COUNT(pd.pd_id) AS pdCount FROM tbl_project `p` LEFT JOIN tbl_project_order `po` ON po.p_id = p.p_id LEFT JOIN tbl_project_detail `pd` ON pd.p_id = p.p_id GROUP BY p.p_id;
结果出现了
p_name = 'project 1'
poMoney = 1600
pdCount = 4
正确应该是 poMoney = 800 pdCount = 4，因为 LEFT JOIN 的关系所以 poMoney 重复了两次，pdCount 重复了四次，请问该怎麽下才是正确的
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 220.134.11.173
※ 编辑: chan15 来自: 220.134.11.173 (04/17 00:24)