union 与 union all 区别

union all 展示所有结果
union     展示去重后的结果
  • union all

(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE
sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('0', '3', '4')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
UNION ALL
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM un_StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('1', '2')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)

Explain:
id select_type table partitions type possible_keys key key_len ref rows filterd Extra
1 PRIMARY sc ref IntelUserCode IntelUserCode 108 const 1 5 Using index condition; Using where; Using filesort
1 PRIMARY ua ref IntelUserCode IntelUserCode 108 const 2 10 Using where; Using index
2 UNION ua ref IntelUserCode IntelUserCode 108 const 2 10 Using where; Using index; Using temporary; Using filesort
2 UNION sc ref IntelUserCode IntelUserCode 108 const 1 100 Using index condition; Using where
  • union

(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE
sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('0', '3', '4')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
UNION
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM un_StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('1', '2')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)

Explain:
id select_type table partitions type possible_keys key key_len ref rows filterd Extra
1 PRIMARY sc ref IntelUserCode IntelUserCode 108 const 1 5 Using index condition; Using where; Using filesort
1 PRIMARY ua ref IntelUserCode IntelUserCode 108 const 2 10 Using where; Using index
2 UNION ua ref IntelUserCode IntelUserCode 108 const 2 10 Using where; Using index; Using temporary; Using filesort
2 UNION sc ref IntelUserCode IntelUserCode 108 const 1 100 Using index condition; Using where
UNION RESULT <union1,2> ALL Using temporary

union 比 union all 多用了一个临时表存储去重后的数据

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

  • 2003-2014考研真题
    **2014真题Directions:Read the following text. Choose the be...
    又是夜半惊坐起阅读 11,552评论 0赞 23
  • 《那年冬季那场雪》第二章 雨花灯
    文 / 伊米crystal 上一章《那年冬季那场雪》第一章 第一场雪 / 《那年冬季那场雪》目录 方雪站在雨花灯...
    伊米crystal阅读 479评论 0赞 1
  • 育儿路上，莫忘初心
    曾经乐观的认为我们这一代是活出了自我，活出了真彩，与我们的父辈们不一样，尤其是在育儿上，不会那么固执的要求孩子听我...
    木星合月阅读 258评论 0赞 1
  • 不再孤独
    2017年，地球与一枚异星探测器插肩而过，探测器被捕获，这是地球首次与异星发生接触，经过联合国一夜讨论，决定公布探...
    lbeast阅读 248评论 0赞 0
  • 斗虫记
    夏天万物生机蓬勃，庄稼长得起劲，山林绿得正好。太阳一日猛似一日，幸好也还有蝉儿爱它，越热得狠，蝉儿越闹得欢。电视上...
    喜乐心记阅读 346评论 0赞 2

友情链接更多精彩内容