mysql – 使用多个左连接查询 – 点列值不正确

我有以下数据库结构,我正在尝试运行一个查询,该查询将显示教室,有多少学生是教室的一部分,教室分配了多少奖励,以及分配给单个教授的积分数量教室(基于classroom_id专栏).

在最底层使用查询我正在尝试收集教室分配的’totalPoints’ – 基于在classroom_redeemed_codes表中计算points列并将其作为单个整数返回.

由于某种原因,totalPoints的值不正确 – 我做错了但不确定是什么……

– 更新 –
这是sqlfiddle： –
http://sqlfiddle.com/#!2/a9f45

我的结构：

CREATE TABLE `organisation_classrooms` (
  `classroom_id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(255) NOT NULL,`active` tinyint(1) NOT NULL,`organisation_id` int(11) NOT NULL,`period` int(1) DEFAULT '0',`classroom_bg` int(2) DEFAULT '3',`sortby` varchar(6) NOT NULL DEFAULT 'points',`sound` int(1) DEFAULT '0',PRIMARY KEY (`classroom_id`)
);

CREATE TABLE organisation_classrooms_myusers (
  `classroom_id` int(11) NOT NULL,`user_id` bigint(11) unsigned NOT NULL,);

CREATE TABLE `classroom_redeemed_codes` (
  `redeemed_code_id` int(11) NOT NULL AUTO_INCREMENT,`myuser_id` bigint(11) unsigned NOT NULL DEFAULT '0',`ssuser_id` bigint(11) NOT NULL DEFAULT '0',`classroom_id` int(11) NOT NULL,`order_product_id` int(11) NOT NULL DEFAULT '0',`order_product_images_id` int(11) NOT NULL DEFAULT '0',`date_redeemed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`points` int(11) NOT NULL,`type` int(1) NOT NULL DEFAULT '0',`notified` int(1) NOT NULL DEFAULT '0',`inactive` tinyint(3) NOT NULL,PRIMARY KEY (`redeemed_code_id`),);

SELECT
  t.classroom_id,title,COALESCE (
    COUNT(DISTINCT r.redeemed_code_id),0
   ) AS totalRewards,COALESCE (
    COUNT(DISTINCT ocm.user_id),0
   ) AS totalStudents,COALESCE (sum(r.points),0) AS totalPoints
  FROM
  `organisation_classrooms` `t`
   LEFT OUTER JOIN classroom_redeemed_codes r ON (
   r.classroom_id = t.classroom_id
   AND r.inactive = 0
   AND (
    r.date_redeemed >= 1393286400
    OR r.date_redeemed = 0
   )
   )
   LEFT OUTER JOIN organisation_classrooms_myusers ocm ON (
   ocm.classroom_id = t.classroom_id
   )
   WHERE
    t.organisation_id =37383
   GROUP BY title
   ORDER BY t.classroom_id ASC
   LIMIT 10

– 编辑 –

OOPS！我有时讨厌sql …我犯了一个大错误,我试图计算在classroom_redeemed_codes而不是organisation_classrooms_myuser表中的学生人数.我真的很抱歉我应该早点把它拿走？！

classroom_id | totalUniqueStudents
     16             1
     17             2
     46             1
     51             1
     52             1

在classroom_redeemed_codes表中有7行,但由于teacher_id 46有两行,尽管具有相同的myuser_id(这是学生ID),这应该显示为一个唯一的学生.

这有意义吗？基本上试图根据myuser_id列获取classroom_redeemed_codes表中唯一学生的数量.

例如,一个教室id 46可以在classroom_redeemed_codes表中有100行,但是如果它们相同的myuser_id则应该显示totalUniqueStudents计为1而不是100.

如果不清楚,请告诉我……

– 更新 –
我有以下查询似乎工作借用了一个似乎工作的用户…(我的头痛)我会再次接受答案.很抱歉这个混乱 – 我想我只是在想这个

select crc.classroom_id,COUNT(DISTINCT crc.myuser_id) AS users,COUNT( DISTINCT crc.redeemed_code_id ) AS classRewards,SUM( crc.points ) as classPoints,t.title
  from classroom_redeemed_codes crc
       JOIN organisation_classrooms t
         ON crc.classroom_id = t.classroom_id 
        AND t.organisation_id = 37383
        where crc.inactive = 0
        AND ( crc.date_redeemed >= 1393286400
        OR crc.date_redeemed = 0 )
        group by crc.classroom_id

最佳答案

我首先运行每个特定类的点数的预查询聚合,然后使用左连接到它.我在结果集中获得的行数多于预期的样本,但没有MysqL直接测试/确认.但是here is a SQLFiddle of your query通过使用点总和进行查询,并在应用users表时得到笛卡尔结果,这可能是重复点的基础.通过预先查询兑换代码本身,您只需获取该值,然后加入用户.

SELECT
      t.classroom_id,COALESCE ( r.classRewards,0 ) AS totalRewards,COALESCE ( r.classPoints,0) AS totalPoints,COALESCE ( r.uniqStudents,0 ) as totalUniqRedeemStudents,COALESCE ( COUNT(DISTINCT ocm.user_id),0 ) AS totalStudents
   FROM
      organisation_classrooms t
         LEFT JOIN ( select crc.classroom_id,COUNT( DISTINCT crc.myuser_id ) as uniqStudents,SUM( crc.points ) as classPoints
                        from classroom_redeemed_codes crc
                           JOIN organisation_classrooms t
                              ON crc.classroom_id = t.classroom_id 
                              AND t.organisation_id = 37383
                        where crc.inactive = 0
                          AND ( crc.date_redeemed >= 1393286400
                           OR crc.date_redeemed = 0 )
                        group by crc.classroom_id ) r
            ON t.classroom_id = r.classroom_id

         LEFT OUTER JOIN organisation_classrooms_myusers ocm 
            ON t.classroom_id = ocm.classroom_id
   WHERE
      t.organisation_id = 37383
   GROUP BY 
      title
   ORDER BY 
      t.classroom_id ASC
   LIMIT 10

原文链接：https://www.f2er.com/mysql/434118.html

mysql – 使用多个左连接查询 – 点列值不正确

猜你在找的MySQL相关文章