SQL Union使用实例

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;