时间戳

首页 > 解决方案 > 一条 SQL 语句代替两条

mysql - 一条 SQL 语句代替两条

问题描述

我正在尝试缩短我的代码，但我被卡住了。目前我执行两个单独的查询以从两个单独的表中获取我需要的数据。前提是生成一份报告，比较基于性别和种族的两个不同班级的考试成绩。

查询一：

SELECT g.ethnicity AS dataPointA, 
count(case when t.fall_spring = 1 then t.studentID end) as countA,
round(avg(case when t.fall_spring = 1 then t.compScore end),0) as scoreA,
round(avg(case when t.fall_spring = 1 then t.compNP end),1) as scoreNPA

from table1 t
JOIN aall_students s
ON t.studentID=s.studentID 
JOIN aall_ethnicities g
ON s.ethnicityID=g.ethnicityID
WHERE t.classof = 2021 and s.gender = 'F'
group by s.ethnicityID

Results query 1:

dataPointA        countA   scoreA    scoreNPA
--------------------------------------------
African American |   18   | 914   |   56.6  |
--------------------------------------------
Asian            |    8   | 998   |   71.4  |
--------------------------------------------
Hispanic/Latino  |    5   | 936   |   63.0  |
--------------------------------------------
Two or More      |   11   | 1005  |   72.3  |
--------------------------------------------
White            |   28   | 1028  |   76.7  |

查询2：注意第二个查询中的WHERE子句不同，返回的行数也不同。

SELECT g.ethnicity AS dataPointB, 
count(case when t.fall_spring = 1 then t.studentID end) as countB,
round(avg(case when t.fall_spring = 1 then t.compScore end),0) as scoreB,
round(avg(case when t.fall_spring = 1 then t.compNP end),1) as scoreNPB

from table2 t
JOIN aall_students s
ON t.studentID=s.studentID 
JOIN aall_ethnicities g
ON s.ethnicityID=g.ethnicityID
WHERE t.classof = 2022 and s.gender = 'F'
group by s.ethnicityID

Results query 2:

dataPointB        countB   scoreB    scoreNPB
----------------------------------------------
African American |   12   | 838    |   40.0   |
----------------------------------------------
Asian            |    7   | 957    |   65.9   |
----------------------------------------------
Hispanic/Latino  |    7   | 931    |   59.6   |
----------------------------------------------
Native American  |    1   | 940    |   64.0   |
----------------------------------------------
Two or More      |   18   | 963    |   66.1   |
----------------------------------------------
White            |   32   | 1074   |   84.1   |

请注意，查询返回的行数不同，这意味着第一个查询中没有美洲原住民，而第二个查询中没有美洲原住民。

如果我可以将两个查询合并为一个，那么查询和处理就会减少，例如，来自两个查询的所有非裔美国人的数据都放在一行上。它还将消除脚本方面的处理和潜在的错误。

我尝试了各种形式的 UNION、JOIN 和广泛的谷歌搜索，但无法提出任何解决方案。任何可以为我指明正确方向的建议将不胜感激。

期望的结果如下所示：

dataPointA        countA   scoreA    scoreNPA   dataPointB       countB   scoreB   scoreNPB
--------------------------------------------------------------------------------------------
African American |   18   | 914   |   56.6  |  African American |   12   | 838   |   40.0  |
--------------------------------------------------------------------------------------------
Asian            |    8   | 998   |   71.4  |  Asian            |    7   | 957   |   65.9  |
--------------------------------------------------------------------------------------------
Hispanic/Latino  |    5   | 936   |   63.0  |  Hispanic/Latino  |    7   | 931   |   59.6  |
--------------------------------------------------------------------------------------------
null             |  null  | null  |   null  |  Native American  |    1   | 940   |   64.0  |
--------------------------------------------------------------------------------------------
Two or More      |   11   | 1005  |   72.3  |  Two or More      |   18   | 963   |   66.1  |
--------------------------------------------------------------------------------------------
White            |   28   | 1028  |   76.7  |  White            |   32   | 1074  |   84.1  |

使用 UNION ALL 我得到了一个可行的数组，这需要在代码方面进行一些操作。我还在回复中添加了建议的“类”列以区分表格：

dataPoint           class     count    score      scoreNP   
------------------------------------------------------------
African American |  2021   |   18    |  914    |   56.6    |
------------------------------------------------------------
Asian            |  2021   |    8    |  998    |   71.4    |
------------------------------------------------------------
Hispanic/Latino  |  2021   |    5    |  936    |   63.0    |
------------------------------------------------------------
Two or More      |  2021   |   11    | 1005    |   72.3    |
------------------------------------------------------------
White            |  2021   |   28    | 1028    |   76.7    |
------------------------------------------------------------
African American |  2022   |   12    |  838    |   40.0    |
------------------------------------------------------------
Asian            |  2022   |    7    |  957    |   65.9    |
------------------------------------------------------------
Hispanic/Latino  |  2022   |    7    |  931    |   59.6    |
------------------------------------------------------------
Native American  |  2022   |    1    |  940    |   64.0    |
------------------------------------------------------------
Two or More      |  2022   |   18    |  963    |   66.1    |
------------------------------------------------------------
White            |  2022   |   32    | 1074    |   84.1    |

标签： mysql

解决方案

这是 UNION ALL 的工作。为什么全部？一个普通的 UNION 尝试对行进行重复数据删除，而您不需要这样做。

尝试这样做：

     SELECT 'A' datatype, whatever, whatever
       FROM table1 ... WHERE whatever
     UNION ALL
     SELECT 'B' datatype, whatever, whatever
       FROM table2 ... WHERE whatever

您将获得一个带有额外列的结果集datatype（在我的示例中）。您可以使用该列来区分两个不同表的结果。

推荐阅读