SQL Union使用实例

最新推荐文章于 2025-10-20 20:32:49 发布

原创最新推荐文章于 2025-10-20 20:32:49 发布 · 798 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #sql server #sql

database 专栏收录该内容

4 篇文章

订阅专栏

本文介绍SQL中Union与Union All的区别，并通过具体示例展示它们的应用场景。同时，文章还提供了如何根据星期字段动态获取比率的方法。

union :

不包含重复行，进行默认排序

select 1 test , 2 test2 union select 1 ,3 union select 1 ,5 union select 1 ,4 union select 1 ,3;

test	test2
1	2
1	3
1	4
1	5

union all :

包含重复的数据，不排序

select 1 test , 2 test2 union all select 1 ,3 union all select 1 ,5 union all select 1 ,4 union all select 1 ,3;

test	test2
1	2
1	3
1	5
1	4
1	3

实际运用，有一个只有星期字段的表，如何根据每天动态自动获取当天的比率。

思路：手动构造一个将星期的天数与比率结合的数据集

table :

exp_tb

ID	SUN	MON	TUE	WED	THU	FRI	SAT	UPD
1	0	20	20	20	20	20	0	2004/10/08 13:51:24

sql:                               select 1 WEEK_DAY,SUN PART_RATE
                                    from exp_tb union
                                    select 2 WEEK_DAY,MON PART_RATE
                                    from exp_tb union
                                    select 3 WEEK_DAY,TUE PART_RATE
                                    from exp_tb union
                                    select 4 WEEK_DAY,WED PART_RATE
                                    from exp_tb union
                                    select 5 WEEK_DAY,THU PART_RATE
                                    from exp_tb union
                                    select 6 WEEK_DAY,FRI PART_RATE
                                    from FNAEDRT00 union
                                    select 7 WEEK_DAY,SAT PART_RATE
                                    from exp_tb;

result:

WEEK_DAY	PART_RATE
1	0
2	20
3	20
4	20
5	20
6	20
7	0

获取当天的比率:

with wr as (

select 1 WEEK_DAY,SUN PART_RATE
                                    from exp_tb union
                                    select 2 WEEK_DAY,MON PART_RATE
                                    from exp_tb union
                                    select 3 WEEK_DAY,TUE PART_RATE
                                    from exp_tb union
                                    select 4 WEEK_DAY,WED PART_RATE
                                    from exp_tb union
                                    select 5 WEEK_DAY,THU PART_RATE
                                    from exp_tb union
                                    select 6 WEEK_DAY,FRI PART_RATE
                                    from FNAEDRT00 union
                                    select 7 WEEK_DAY,SAT PART_RATE
                                    from exp_tb）

　　　　　　　　　select wr.PART_RATE
from wr
where datepart(weekday,getdate()) = wr.WEEK_DAY;