我的查询同时具有SUMGROUP_CONCAT函数时遇到问题。

和值随GROUP_CONCAT值的增加而变化。

下面是我的代码:

SELECT     ul.display_name,
           ul.photo,
           ul.user_id,
           Sum(ulr.level_score)              AS level_scores,
           Sum(ulr.level_timer)              AS level_timer,
           Group_concat(ulr.level_completed) AS levels,
           Group_concat(DISTINCT c.bit_id)   AS bit_id
FROM       user_level_responses              AS ulr
INNER JOIN user_login                        AS ul
ON         (
                      ul.user_id=ulr.user_id)
INNER JOIN c_member AS cm
ON         (
                      cm.user_id=ul.user_id
           AND        cm.user_approval='Y'
           AND        cm.delete_status='0'
           AND        cm.status='1')
INNER JOIN ctree ct
ON         (
                      cm.circuit_id=ct.circuit_id )
INNER JOIN cir AS c
ON         (
                      c.circuits_id=cm.circuit_id
           AND        c.builtin=0
           AND        c.delete_status='0'
           AND        c.status='1')
WHERE      Match(ct.circuit_path) against ('_902_')
AND        ulr.institution_id=321
AND        ulr.delete_status=0
AND        ulr.status=1
AND        ul.delete_status=0
GROUP BY   ulr.user_id
ORDER BY   level_scores DESC,
           level_timer ASC,
           ul.display_name limit 500


如果实际分数是900,并且如果我在GROUP_CONCAT中有2个id,则实际分数是原始分数的两倍。

Expected OUTPUT:

user1   2010.cs,btech   960 00:01:08    Completed
user2   btech   920 00:01:08    Completed

OUTPUT GETTING:

user1   2010.cs,btech   1920    00:01:08    Completed
user2   btech   920 00:01:08    Completed


实际金额的两倍,即960。

最佳答案

您的问题是您的多个ID正在使结果在分组之前的行增加一倍。您可以通过在子查询中加入所有外部数据来解决此问题。

我绝对不知道您的数据库的结构,也不知道所有的功能,但这在重新组织您的查询方面是一个黑暗的障碍。如果需要,我可以编写一个简单得多的SQLFiddle来向您展示我的意思。

SELECT     ul2.display_name,
           ul2.photo,
           ul2.user_id,
           Sum(ulr.level_score)              AS level_scores,
           Sum(ulr.level_timer)              AS level_timer,
           Group_concat(ulr.level_completed) AS levels,
           ul2.bit_id
FROM       user_level_responses              AS ulr
INNER JOIN (
    SELECT
    ul.display_name,
    ul.photo,
    ul.user_id,
    GROUP_CONTACT(DISTINCT c.bit_id)  as bit_id
    FROM       user_login                        AS ul
    INNER JOIN c_member AS cm
    ON         (
                  cm.user_id=ul.user_id
           AND        cm.user_approval='Y'
           AND        cm.delete_status='0'
           AND        cm.status='1')
    INNER JOIN ctree ct
    ON         (
                  cm.circuit_id=ct.circuit_id )
    INNER JOIN cir AS c
    ON         (
                  c.circuits_id=cm.circuit_id
           AND        c.builtin=0
           AND        c.delete_status='0'
           AND        c.status='1')
    WHERE      Match(ct.circuit_path) against ('_902_')
    AND        ul.delete_status=0
    GROUP BY   ul.user_id
) AS ul2
ON  (
        ulr.user_id = ul2.user_id )
WHERE      ulr.institution_id=321
AND        ulr.delete_status=0
AND        ulr.status=1
GROUP BY   ulr.user_id
ORDER BY   level_scores DESC,
           level_timer ASC,
           ul.display_name limit 500

关于mysql - 查询中Mysql SUM和GROUP_CONCAT的问题,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31780871/

10-16 18:33
查看更多