PHP MYSQL查询组合并添加两个相等的结果
PHP MYSQL查询组合并添加两个相等的结果
提问于 2013-01-29 17:56:20
我有一个下面给出的MYSQL查询
(SELECT
c.cl_list as cl_list,
c.name as name,
pc.value as value,
count( pc.value) as total
FROM
projs p
LEFT JOIN classify_proj_new pc
ON p.proj_id = pc.proj_id_fk
LEFT JOIN classify_list c
ON c.cl_list = pc.class_id_fk
WHERE
MATCH ( p.title ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY
c.cl_list,
pc.value)
UNION ALL
(SELECT
c.cl_list as cl_list,
c.name as name,
pc.value as value,
count( pc.value) as total
FROM
jerm p
LEFT JOIN classify_jerm_new pc
ON p.jerm_id = pc.jerm_id_fk
LEFT JOIN classify_list c
ON c.cl_list = pc.class_id_fk
WHERE
MATCH ( p.jermname ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY
c.cl_list,
pc.value)其结果为(如下)：
cl_list name value total
------------------------------------------------------------------------------------
1 department jewller 2
3 price 50 2
6 color blue 1
6 color Red 2
1 department jewller 1
6 color Red 1但我正在尝试得到一个结果，它可以将重复值的总数相加，并避免像这样重复value....some的事情(如下所示)：
cl_list name value total
------------------------------------------------------------------------------------
1 department jewller 3
3 price 50 2
6 color blue 1
6 color Red 3谁来帮帮我，我对我的输出感到非常悲哀…
非常感谢你提前……
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-01-29 18:05:18
从查询中选择，并按cl_list、名称和值分组：
SELECT
cl_list,
name,
value,
sum(total) as total
FROM (
-- your current query here ...
) data
GROUP BY
cl_list,
name,
valueStack Overflow用户
发布于 2013-01-29 18:03:19
试试下面的代码。它将显示唯一的记录，避免重复。
GROUP BY c.nameStack Overflow用户
发布于 2013-01-29 18:05:10
对名称和值执行group操作
(SELECT c.cl_list as cl_list, c.name as name, pc.value as value, count( pc.value) as total
FROM projs p
LEFT JOIN classify_proj_new pc ON p.proj_id = pc.proj_id_fk
LEFT JOIN classify_list c ON c.cl_list = pc.class_id_fk
WHERE MATCH ( p.title ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY c.name, pc.value)
UNION ALL
(SELECT c.cl_list as cl_list, c.name as name, pc.value as value, count( pc.value) as total
FROM jerm p
LEFT JOIN classify_jerm_new pc ON p.jerm_id = pc.jerm_id_fk
LEFT JOIN classify_list c ON c.cl_list = pc.class_id_fk
WHERE MATCH ( p.jermname ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY c.name, pc.value)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14580073
复制相关文章
相似问题
腾讯云开发者
