union select和group引用

zengzsys 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(371)

设置查询时遇到问题

(SELECT * FROM attendance WHERE am_slip="absent" AND pm_slip="none")
 UNION
(SELECT * FROM attendance WHERE am_slip="none" AND pm_slip="absent")
 UNION
(SELECT * FROM attendance WHERE am_slip="absent" AND pm_slip IS NULL)
 UNION
(SELECT * FROM attendance WHERE am_slip="absent" AND pm_slip="late")
 UNION
(SELECT * FROM attendance WHERE am_slip="late" AND pm_slip="absent")
 UNION
(SELECT * FROM attendance WHERE am_slip="absent" AND pm_slip="absent")

我想合并/合并数据表（id号）中的所有匹配项，但我做不到。那么，你能帮我吗？
这是使用上述查询的输出：

这是我想要的输出：

至

这是表，我想合并它们并显示单个输出：

实验性查询但不起作用：
选择stu\u id，count（）as id\u count from（selectfrom attenting where am\u slip=“late”and pm\u slip=“none”）union（selectfrom attenting where am\u slip=“none”and pm\u slip=“late”）union（selectfrom attenting where am\u slip=“late”and pm\u slip=“absent”）union（selectfrom attenting where am\u slip=“late”and pm\u slip=“absent”）union（selectfrom attenting从其中am\u slip=“缺席”和pm\u slip=“迟到”）工会（从出席人数中选择*其中am\u slip=“迟到”和pm\u slip=“迟到”）按学生id分组按id排序

sql mysql

来源：https://stackoverflow.com/questions/52580022/union-select-and-group-occurrences

3条答案

按热度按时间

ozxc1zmp1#

好了，谢谢你们帮我
选择idnum.stu\u id，count（）from（select从考勤，其中am\u slip=“缺席”和pm\u slip=“无”联合所有select从考勤，其中am\u slip=“无”和pm\u slip=“缺席”联合所有select从考勤，其中am\u slip=“缺席”和pm\u slip=“迟到”联合所有select从考勤其中am\u slip=“迟到”和pm\u slip=“缺席”工会均从考勤中选择其中am\u slip=“缺席”和pm\u slip=“缺席”）作为idnum group by stu id order by stu id

赞(0）回复(0）举报 2021-06-20

oyt4ldly2#

我将为您提供使用distinct和替换所有union子句的示例，方法是在where子句上使用ors操作数：

SELECT
    DISTINCT name, ID_No, Num_Absence, Handle 
FROM
    attendance
WHERE
    (am_slip="absent" AND (pm_slip="none" OR pm_slip IS NULL OR pm_slip="late" OR pm_slip="absent"))
OR
    (am_slip="none" AND pm_slip="absent")
OR
    (am_slip="late" AND pm_slip="absent")

赞(0）回复(0）举报 2021-06-20

zd287kbt3#

如果希望得到不同的结果，可以正确地使用union，但在这种情况下应避免select*（select all columns），应明确地选择所涉及的列

SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="absent" AND pm_slip="none")
  UNION
SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="none" AND pm_slip="absent"
  UNION
SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="absent" AND pm_slip IS NULL
  UNION
SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="absent" AND pm_slip="late"
  UNION
SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="late" AND pm_slip="absent"
  UNION
SELECT name, ID_No, Num_Absence, Handle 
FROM attendance WHERE am_slip="absent" AND pm_slip="absent"

赞(0）回复(0）举报 2021-06-20

我来回答

union select和group引用

3条答案

相关问题

热门标签

最新问答