mysql - 查询输出与预期输出不同

Question

下面的查询正在做我需要的:

SELECT assign.from_uid, assign.aid, assign.message, curriculum.asset, 
       curriculum.title, curriculum.description 
FROM assignment assign 
INNER JOIN curriculum_topics_assets curriculum 
   ON assign.nid = curriculum.asset 
WHERE assign.to_uid = 13 AND assign.status = 1 
GROUP BY assign.from_uid, assign.to_uid, assign.nid 
ORDER BY assign.created DESC

现在我需要获取结果的总行数。例如，如果它显示 5 行，o/p 应该像我预期的 o/p。下面给出了我尝试的查询。

SELECT count(description) FROM assignment assign 
INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
WHERE assign.to_uid = 13 AND assign.status = 1 
GROUP BY assign.from_uid, assign.to_uid, assign.nid 
ORDER BY assign.created DESC

我的预期成绩:

count(*)
---------
5

我目前的职业:

count(*)
---------
6
2
5
6
6

Answer 1

最简单的解决方案是

• 将您的初始 GROUP BY 查询放在子选择中
• 选择从此子选择中检索的行数

SQL语句

SELECT COUNT(*)
FROM   (
          SELECT  assign.from_uid 
          FROM    assignment assign 
                  INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
          WHERE   assign.to_uid = 13 
                  AND assign.status = 1 
          GROUP BY 
                  assign.from_uid
                  , assign.to_uid
                  , assign.nid 
       ) q

编辑 - 为什么原始查询没有返回所需的结果

它确实已经准备好了得到正确结果所需的东西

1. 您的查询没有分组返回 25 条记录的结果集 (6+2+5+6+6)
2. 从这 25 条记录中，您有 5 种独特的 from_uid、to_uid、nid
组合

现在您不想计算每个组合有多少条记录(正如您在示例中所做的那样)，而是计算有多少独特的(不同的人？) 组合。

对此的一种解决方案是我提出的子选择，但使用 DISTINCT 子句的等效语句可能更全面。

SELECT  COUNT(*)
FROM    (
          SELECT  DISTINCT assign.from_uid
                  , assign.to_uid
                  , assign.nid
          FROM    assignment assign 
                  INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
          WHERE   assign.to_uid = 13 
                  AND assign.status = 1 
        ) q

请注意，我个人偏爱 GROUP BY 解决方案。