我正在为我的学校创建一个应用程序,我在构建正确的查询时遇到了麻烦。
我有两个表,table1和table2。
table1
---------------------------------------------------------
| StudentID | SubjectID | Present | Type |
---------------------------------------------------------
| 2 | 3 | yes | 1 |
| 2 | 2 | yes | 2 |
| 3 | 1 | no | 3 |
---------------------------------------------------------
表
---------------------------------------------------------
| SubjectID | SubjectName | Number1 | Number2 |
---------------------------------------------------------
| 1 | Name1 | 6 | 4 |
| 2 | Name2 | 4 | 8 |
| 3 | Name3 | 5 | 2 |
---------------------------------------------------------
表1中的SubjectID是表2中的外键引用
我想建立一个查询sql,从表1给我的学生id没有遗漏任何类型3主题(即没有像这样的行)
---------------------------------------------------------
| StudentID | SubjectID | Present | Type |
---------------------------------------------------------
| 3 | 1 | no | 3 |
---------------------------------------------------------
并且完成了75%的类型1(即我发现它是这样的
SELECT t1.StudentID,t1.SubjectID ,t1.Type,t2.Number1 as num
FROM table1 as t1,table2 as t2
WHERE t1.Present=yes and t2.SubjectID=t1.SubjectID
GROUP BY StudentID,SubjectID
HAVING COUNT(*)/num >= 75/100
但是我不能把这两件事结合起来。
您可以通过为查询提供别名并将其连接为子查询来组合查询…
SELECT finisher.StudentID FROM
(
SELECT DISTINCT StudentID
FROM table1 t1
JOIN table2 t2 ON t2.SubjectID = t1.SubjectID
WHERE t1.Present = 'yes' AND t1.Type1 = 1
GROUP BY t1.StudentID, t2.SubjectID
HAVING COUNT(*) / t2.Number2 >= 0.75
) finisher
JOIN
(
SELECT DISTINCT t1.StudentID
FROM table1 t1
LEFT JOIN
(
SELECT DISTINCT StudentID
FROM table1
WHERE Type = 3 AND Present = 'no'
) missed ON missed.StudentID = t1.StudentID
WHERE t1.Type = 3
AND missed.StudentID IS NULL
) notmissed ON finisher.StudentID = notmissed.StudentID
"StudentID 's from table1 that not miss any Type 3"…我假设这里不希望包含没有任何类型3行的学生。
似乎这一切都完成了,但如何…
SELECT x.*
FROM
( SELECT t1.StudentID
, t1.SubjectID
, t1.Type
, t2.Number1 num
FROM table1 t1
JOIN table2 t2
ON t2.SubjectID=t1.SubjectID
WHERE t1.Present='yes'
GROUP
BY t1.StudentID
, t1.SubjectID
HAVING COUNT(*)/num >= 0.75
) x
LEFT
JOIN table1 y
ON y.student_id = x.student_id
AND y.subject_id = x.subject_id
AND y.type = 3
AND y.present = 'no'
WHERE y.student_id IS NULL;