1、准备一个考分表

SELECT * from score where c_id is not null

2、算出 s_id 和 c_id的笛卡尔

5.9 求笛卡尔方式

SELECT * from 
(SELECT DISTINCT s_id from score where c_id is not null  ) t1 
INNER JOIN 
(SELECT DISTINCT c_id from score where c_id is not null) t2

8.18 求笛卡尔方式

-- 正规SQL写法
SELECT DISTINCT  t1.s_id,t2.c_id from score t1 CROSS join score t2;

-- mysql

SELECT DISTINCT  t1.s_id,t2.c_id from score t1 join score t2;

:::info 在mysql中 INNER JOIN /CROSS JOIN/JOIN 三个表连接是等价的 :::

3、左连

SELECT * from (
    SELECT * from 
    (SELECT DISTINCT s_id from score where c_id is not null  ) t1 
    INNER JOIN 
    (SELECT DISTINCT c_id from score where c_id is not null) t2
    ) t3
LEFT JOIN
    (SELECT * from score where c_id is not null) t4
on t3.s_id = t4.s_id and t3.c_id = t4.c_id

4、取 结果为null的

SELECT * from (
    SELECT * from (
        SELECT * from 
        (SELECT DISTINCT s_id from score where c_id is not null  ) t1 
        INNER JOIN 
        (SELECT DISTINCT c_id from score where c_id is not null) t2
        ) t3
    LEFT JOIN
        (SELECT s_id as s_id_copy,c_id as c_id_copy  from score where c_id is not null) t4
    on t3.s_id = t4.s_id_copy and t3.c_id = t4.c_id_copy ) t5
where t5.s_id_copy is null

5、使用exists

SELECT DISTINCT
    t1.s_id,
    t2.c_id 
FROM
    score t1
    CROSS JOIN score t2 
WHERE
    NOT EXISTS (
    SELECT
        1 
    FROM
        score t3 
    WHERE
        t1.s_id = t3.s_id 
    AND t2.c_id = t3.c_id);

  -- 无格式
  SELECT DISTINCT  t1.s_id,t2.c_id from score t1 CROSS join score t2
    where not exists (SELECT 1 from score t3 where t1.s_id = t3.s_id and t2.c_id=t3.c_id)

6、使用except

-- MYSQL 中是不支持EXCEPT子句
SELECT DISTINCT  t1.s_id,t2.c_id from score t1 CROSS join score t2
EXCEPT 
SELECT s_id,c_id from score;