时间戳

首页 > 解决方案 > 如何将多个表与不同的列组合起来

sql - 如何将多个表与不同的列组合起来

问题描述

我有 3 张桌子。我希望school表格设置数据major_subject并获得minor_subject 结果。

由于该表没有列，因此也自动设置units 3.0为。major_subject

我尝试使用UNION但出现错误，因为major_subject列与minor_subject表不同。

school                                      major_subject     

| school_id |  school_name  |             |   subj_name    |  date_offered |
-----------------------------             ---------------------------------
|   1       |    schoolA    |             |  Business101   |   2021/01/01  |
|   2       |    schoolB    |             |  Marketing101  |   2021/01/01  |
|   3       |    schoolC    |        


minor_subject

| school_id  |  subj_name  | units  | date_offered |      
----------------------------------------------------
|     1      |   Math      |  1.0   |  2021/01/01  |     
|     1      |   English   |  1.0   |  2021/01/01  | 
|     1      |   Science   |  1.0   |  2021/01/01  | 
|     2      |   History   |  2.0   |  2021/01/01  |

结果表如下：

| school_id  |  subj_name     |  units   | date_offered |      
---------------------------------------------------------         
|     1      |  Business101   |   3.0    |  2021/01/01  |     
|     1      |  Marketing101  |   3.0    |  2021/01/01  |    
|     1      |  Math          |   1.0    |  2021/01/01  |     
|     1      |  English       |   1.0    |  2021/01/01  | 
|     1      |  Science       |   1.0    |  2021/01/01  | 

|     2      |  Business101   |   3.0    |  2021/01/01  |     
|     2      |  Marketing101  |   3.0    |  2021/01/01  |    
|     2      |  History       |   2.0    |  2021/01/01  |     

|     3      |  Business101   |   3.0    |  2021/01/01  |     
|     3      |  Marketing101  |   3.0    |  2021/01/01  |

标签： sqlsql-servertsql

解决方案

您可以CROSS加入school以major_subject获取 2 个表的行的所有组合，然后UNION ALL从以下位置添加行minor_subject：

SELECT s.school_id, m.subj_name, 3.0 units, m.date_offered 
FROM school s CROSS JOIN major_subject m
UNION ALL
SELECT school_id, subj_name, units, date_offered 
FROM minor_subject 
ORDER BY school_id

请参阅演示。
结果：

> school_id | subj_name    | units | date_offered
> --------: | :----------- | ----: | :-----------
>         1 | Business101  |   3.0 | 2021-01-01  
>         1 | Marketing101 |   3.0 | 2021-01-01  
>         1 | Math         |   1.0 | 2021-01-01  
>         1 | English      |   1.0 | 2021-01-01  
>         1 | Science      |   1.0 | 2021-01-01  
>         2 | History      |   2.0 | 2021-01-01  
>         2 | Marketing101 |   3.0 | 2021-01-01  
>         2 | Business101  |   3.0 | 2021-01-01  
>         3 | Business101  |   3.0 | 2021-01-01  
>         3 | Marketing101 |   3.0 | 2021-01-01

推荐阅读