主页 / dba / 问题 / 153528
Accepted
Nishant
Asked: 2016-10-28 12:12:32 +0800 CST

我如何识别所有通过的学生？

  • 772

假设我有一个表student和一个exam跟踪学生参加的所有考试的表，我如何找到所有通过或未参加任何类似于白名单的考试的学生？在此示例中，这意味着学生 1 和 2。

PS：passflagU表示状态未知，activeflagN表示考试已被删除，其余的标志是不言自明的。

说清楚一点，真正的用例其实是assessments，我需要找出没有pending评估的人可以参与，这样他们才能进行下一组程序。其他人则需要完成待处理的项目。

+-------------+------+
| student_idn | name |
+-------------+------+
| 1           | Mark |
| 2           | Jack |
| 3           | Jane |
| 4           | Jill |
+-------------+------+

+----------+-------------+------+--------+
| exam_idn | student_idn | pass | active |
+----------+-------------+------+--------+
| 1        | 1           | Y    | Y      |
| 2        | 1           | Y    | Y      |
| 3        | 1           | Y    | Y      |
| 4        | 2           | Y    | Y      |
| 5        | 2           | N    | N      |
| 6        | 3           | Y    | Y      |
| 7        | 3           | Y    | Y      |
| 8        | 3           | Y    | Y      |
| 9        | 3           | N    | Y      |
| 10       | 4           | U    | Y      |
+----------+-------------+------+--------+

我想出了下面的查询，但有没有更好的方法来做到这一点？我觉得这是 O(n) 或 O(nlogn) 取决于索引等不确定（任何人都知道最好的可能）因此应该接近最优？

SELECT
  *
FROM student
WHERE NOT EXISTS (SELECT
  *
FROM exam
WHERE exam.student_idn = student.student_idn
AND pass <> 'Y'
AND active = 'Y')
if-not-exists

2 个回答

  1. Best Answer

    您描述的问题称为关系划分。基本上有两种方法可以接近它：

    1. 将所有 x : p(x) 转换为不存在 x : not p(x)

    换句话说，将通过所有测试的学生转换为不存在该学生未通过的测试的学生。您在问题中的查询就是一个例子。

    这种转换通常称为De Morgan's laws for quantifiers. 参见例如：

    https://philosophy.stackexchange.com/questions/28324/proving-de-morgans-laws-for-quantifiers

    1. 通过与考试相同数量的考试的学生。就像是：

       SELECT student_id
 FROM tests
 WHERE passed = 'y' 
 GROUP BY student_id
 HAVING COUNT(1) = (SELECT COUNT(DISTINCT test_id) 
                    FROM tests)

    我是用手机回答的，所以在回答时看不到问题，所以根据您的问题，我的回答的细节可能是错误的，但希望您能明白。

    • 3

  2. 这个怎么样：

    USE tempdb

CREATE TABLE dbo.Student (
  s_idn int,
  name varchar(30)
);
CREATE TABLE dbo.Exam (
  e_idn int IDENTITY,
  s_idn int,
  pass char(1),
  active char(1)
)
GO
INSERT INTO dbo.student (s_idn, name)
  VALUES (1, 'Mark'),
  (2, 'Jack'),
  (3, 'Jane'),
  (4, 'Jill')
GO

INSERT INTO dbo.Exam (s_idn, pass, active)
  VALUES (1, 'Y', 'Y')
  , (1, 'Y', 'Y')
  , (1, 'Y', 'Y')
  , (2, 'Y', 'Y')
  , (2, 'N', 'N')
  , (3, 'Y', 'Y')
  , (3, 'Y', 'Y')
  , (3, 'Y', 'Y')
  , (3, 'N', 'Y')
  , (4, 'U', 'Y')
GO

SELECT
  *
FROM dbo.Student
EXCEPT
SELECT DISTINCT
  s.*
FROM dbo.student s
INNER JOIN dbo.exam e
  ON s.s_idn = e.s_idn
  AND (e.pass <> 'Y'
  AND e.active <> 'N');

    以下性能成本较低：

    SELECT
  *
FROM dbo.student
WHERE s_idn NOT IN (SELECT s.s_idn
                    FROM dbo.student s
                    INNER JOIN dbo.exam e
                        ON s.s_idn = e.s_idn
                       AND e.pass <> 'Y'
                       AND e.active <> 'N')

    其实应该是这样的，这样就不需要把两张表连接起来了：

    SELECT
  *
FROM dbo.student
WHERE s_idn NOT IN (SELECT s_idn
                    FROM dbo.exam e
                    WHERE e.pass <> 'Y'
                      AND e.active = 'Y')
    • 1

相关问题