douxiegan6468 2013-01-29 09:56
浏览 29
已采纳

PHP MYSQL查询结合并添加两个平衡结果

Somebody please help me to solve my sql query i have already spend two days for this....

i have a MYSQL query given below

(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)

Which Gives a result of (below):

  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

but i am trying to get a result which can add the repeating value's total and avoid repeating value....some thing like this (below):

  cl_list      name              value        total
------------------------------------------------------------------------------------
    1       department         jewller          3
    3       price                 50            2
    6       color                blue           1
    6       color                Red            3

somebody please help me i am very sad about my output...

Thank you very much in advance...

  • 写回答

3条回答 默认 最新

  • dongpeng0127 2013-01-29 10:05
    关注

    Select from your query and group by cl_list, name and value:

    SELECT
      cl_list,
      name,
      value,
      sum(total) as total
    FROM (
      -- your current query here ...
    ) data
    GROUP BY
      cl_list,
      name,
      value
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 stm32开发clion时遇到的编译问题
  • ¥15 lna设计 源简并电感型共源放大器
  • ¥15 如何用Labview在myRIO上做LCD显示?(语言-开发语言)
  • ¥15 Vue3地图和异步函数使用
  • ¥15 C++ yoloV5改写遇到的问题
  • ¥20 win11修改中文用户名路径
  • ¥15 win2012磁盘空间不足,c盘正常,d盘无法写入
  • ¥15 用土力学知识进行土坡稳定性分析与挡土墙设计
  • ¥70 PlayWright在Java上连接CDP关联本地Chrome启动失败,貌似是Windows端口转发问题
  • ¥15 帮我写一个c++工程