MySQL 之 DISTINCT

DISTINCT 关键字是去除查询(select [column])结果返回列([column])的重复数据。

新建表

CREATE TABLE TB_CLASS
(
    ID      varchar(32) NOT NULL COMMENT '主键' PRIMARY KEY,
    CL_NAME varchar(32) NULL COMMENT '班级名称',
    CS_ID   varchar(32) NULL COMMENT '课程ID'
) COMMENT '班级表';
CREATE TABLE TB_STUDENT
(
    ID          varchar(32) NOT NULL COMMENT '主键' PRIMARY KEY,
    ST_NAME     varchar(32) NULL COMMENT '名字',
    ST_BIRTHDAY varchar(14) NULL COMMENT '出生日期(YYYYMMDD)',
    ST_PHONE    varchar(11) NULL COMMENT '电话',
    CL_ID       varchar(32) NULL COMMENT '班级ID'
) COMMENT '学生表';
CREATE TABLE TB_SCORE
(
    ID      varchar(32)    NOT NULL COMMENT '主键' PRIMARY KEY,
    SCORE   decimal(18, 2) NULL COMMENT '分数',
    ST_ID   varchar(32)    NOT NULL COMMENT '学生ID',
    CS_ID   varchar(32)    NULL COMMENT '课程ID',
    CS_NAME varchar(32)    NULL COMMENT '课程名',
    REMARKS varchar(1024)  NULL COMMENT '备注'
) COMMENT '分数表';
# 课程表非必须表
CREATE TABLE TB_COURSE
(
    ID      varchar(32) NOT NULL COMMENT '主键' PRIMARY KEY,
    CS_NAME varchar(32) NULL COMMENT '课程名'
) COMMENT '课程表';

插入数据

INSERT INTO TB_COURSE (ID, CS_NAME)
    VALUES (1, '语文'),(2, '数学'),(3, '英语');
INSERT INTO TB_CLASS (ID, CL_NAME, CS_ID)
    VALUES (1, '大班', 1),(2, '中班', 2),(3, '大班', 3),(4, '中班', 1),(5, '中班', 2),(6, '中班', 3),(7, '小班', 1),(8, '小班', 2),(9, '小班', 3);
INSERT INTO TB_STUDENT (ID, ST_NAME, ST_BIRTHDAY, ST_PHONE, CL_ID)
    VALUES (1, '小明', '20221209', '10000', 1),(2, '小董', '20221208', '10086', 2),(3, '小红', '20221207', '10010', 3),(4, '小黑', '20221206', '11111', 1),(5, '小黄', '20221205', '12345', 1);
INSERT INTO TB_SCORE (ID, SCORE, ST_ID, CS_ID, CS_NAME, REMARKS)
    VALUES (1, 100, 1, 1, '语文', '小明'),(2, 99, 1, 2, '数学', '小明'),(3, 98, 1, 3, '英语', '小明'),(4, 97, 2, 1, '语文', '小董'),(5, 96, 2, 2, '数学', '小董'),(6, 95, 2, 3, '英语', '小董'),(7, 94, 3, 1, '语文', '小红'),(8, 93, 3, 2, '数学', '小红'),(9, 92, 3, 3, '英语', '小红');

根据主键关联

SELECT SC.* FROM TB_SCORE SC
    WHERE SC.ST_ID IN (
        SELECT ST.ID FROM TB_STUDENT ST
            WHERE ST.CL_ID IN (
                SELECT CL.ID FROM TB_CLASS CL
                    WHERE CL.CS_ID IN (
                        SELECT CS.ID FROM TB_COURSE CS
#                             WHERE CS.CS_NAME = '语文'
                    )
            )
    )
;

等价于

SELECT  SC.* FROM TB_SCORE SC,
         TB_STUDENT ST,
         TB_CLASS CL,
         TB_COURSE CS
    WHERE SC.ST_ID = ST.ID
      AND ST.CL_ID = CL.ID
      AND CL.CS_ID = CS.ID
#       AND CS.CS_NAME = '语文'
;

根据外键=外键关联

SELECT SC.* FROM TB_SCORE SC
    WHERE SC.CS_ID IN (
        SELECT CL.CS_ID FROM TB_CLASS CL
            WHERE CL.ID IN (
                SELECT ST.CL_ID FROM TB_STUDENT ST
            )
    )
;

等价于

SELECT DISTINCT SC.* FROM TB_SCORE SC, TB_CLASS CL, TB_STUDENT ST
    WHERE SC.CS_ID = CL.CS_ID
      AND CL.ID = ST.CL_ID;

这里与上面sql不同，这里需要加 DISTINCT 和 “SC.”，如果不加就是显示所有表的所有列，那样与上面sql不同效果。

 
如果不加 DISTINCT ，就会有重复数据：

SELECT /*DISTINCT*/ SC.* FROM TB_SCORE SC, TB_CLASS CL, TB_STUDENT ST
    WHERE SC.CS_ID = CL.CS_ID
      AND CL.ID = ST.CL_ID;

 
如果不加 SC. ，则会显示所有表的所有列：

SELECT /*DISTINCT SC.*/ * FROM TB_SCORE SC, TB_CLASS CL, TB_STUDENT ST
    WHERE SC.CS_ID = CL.CS_ID
      AND CL.ID = ST.CL_ID;

若有不正之处，请谅解和批评指正，谢谢~
转载请标明：
https://blog.csdn.net/vihem/article/details/128248799