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;
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)
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;
from wr
where datepart(weekday,getdate()) = wr.WEEK_DAY;