使用多个左连接查询 - 点列值不正确
I have the following database structure, and I am trying to run a single query that will show classrooms and how many students are part of the classroom, and how many rewards a classroom has allocated out, as well as how many points allocated to a single classroom (based on the classroom_id column).
我有以下数据库结构,我正在尝试运行单个查询,该查询将显示教室,教室中有多少学生,教室分配了多少奖励,以及分配给单个教授的积分数量教室(基于classroom_id专栏)。
Using the query at the very bottom I am trying to collect the 'totalPoints' that a classroom has assigned - based on counting the points column in the classroom_redeemed_codes table and return this as a single integer.
在最底部使用查询我试图收集教室分配的'totalPoints' - 基于在classroom_redeemed_codes表中计算points列并将其作为单个整数返回。
For some reason the values are incorrect for the totalPoints - I am doing something wrong but not sure what...
由于某种原因,totalPoints的值不正确 - 我做错了但不确定是什么......
-- UPDATE -- Here is the sqlfiddle:- http://sqlfiddle.com/#!2/a9f45
- 更新 - 这是sqlfiddle: - http://sqlfiddle.com/#!2/a9f45
My Structure:
我的结构:
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
-- EDIT --
- 编辑 -
OOPS! I hate SQL sometimes... I have made a big mistake, I am trying to count the number of STUDENTS in the classroom_redeemed_codes rather than the organisation_classrooms_myuser table. I'm really sorry I should have picked that up sooner?!
OOPS!我有时讨厌SQL ......我犯了一个大错误,我试图计算在classroom_redeemed_codes而不是organisation_classrooms_myuser表中的学生人数。我真的很抱歉我应该早点把它拿起来?!
classroom_id | totalUniqueStudents
16 1
17 2
46 1
51 1
52 1
There are 7 rows in the classroom_redeemed_codes table but as classroom_id 46 has two rows although with the same myuser_id (this is the student id) this should appear as one unique student.
在classroom_redeemed_codes表中有7行,但由于classroom_id 46有两行,尽管具有相同的myuser_id(这是学生ID),这应该显示为一个唯一的学生。
Does this make sense? Essentially trying to grab the number of unique students in the classroom_redeemed_codes tables based on the myuser_id column.
这有意义吗?基本上试图根据myuser_id列获取classroom_redeemed_codes表中唯一学生的数量。
e.g a classroom id 46 could have 100 rows in the classroom_redeemed_codes tables, but if it is the same myuser_id for each this should show the totalUniqueStudents count as 1 and not 100.
例如,一个教室id 46可以在classroom_redeemed_codes表中有100行,但如果它们是相同的myuser_id,则应该显示totalUniqueStudents计为1而不是100。
Let me know if this isn't clear....
如果不清楚,请告诉我......
-- update -- I have the following query which seems to work borrowed from a user below which seems to work... (my head hurts) i'll accept the answer again. Sorry for the confusion - I think I was just over thinking this somewhat
- 更新 - 我有以下查询似乎工作借用了一个似乎工作的用户...(我的头痛)我会再次接受答案。很抱歉这个混乱 - 我想我只是在想这个
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
3 个解决方案
#1
7
I ran by first doing a pre-query aggregate of your points per specific class, then used left-join to it. I am getting more rows in the result set than your sample expected, but don't have MySQL to test/confirm directly. Howeverhere is a SQLFiddle of your query By doing your query with sum of points, and having a Cartesian result when applying the users table, it is probably the basis of duplicating the points. By pre-querying on the redeem codes itself, you just grab that value, then join to users.
我首先运行每个特定类的点数的预查询聚合,然后使用左连接到它。我在结果集中获得的行数多于预期的样本,但没有MySQL直接测试/确认。无论你的查询是什么SQLFiddle通过使用点总和进行查询,并在应用users表时得到笛卡尔结果,它可能是重复点的基础。通过预先查询兑换代码本身,您只需获取该值,然后加入用户。
SELECT
t.classroom_id,
title,
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.redeemed_code_id ) AS classRewards,
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
更多相关文章
- 使用python实现一个简单的学生信息管理系统
- sql查询每个学生的最高成绩mysql语句
- 求查询成绩表中两门科成绩90分以上的学生学号的SQL语句?
- 要查询选修了所有课程的学生信息,怎样用sql实现?
- 约汗——基于Android的大学生找伙伴约运动app 开发总结