数据库行转列、列转行、去重问题

1、列转行

select year,
– 当 a.month 的值 为 1 时 取值 a.index 否则 为 0 并取别名 为 index*

max(DECODE(a.month,'1',a.index,'0')) as index1,
max(DECODE(a.month,'2',a.index,'0')) as index2,
max(DECODE(a.month,'3',a.index,'0')) as index3,
max(DECODE(a.month,'4',a.index,'0')) as index4 
from test_2020_11_12 a group by year

原先结果:
year month index
2021 3 t1
2021 3 t1
2021 2 t1
2021 2 t1
2021 1 t1
2021 1 t1
2021 4 t1
2021 4 t1

转换以后的结果:
Year index1 index2 index3 index4
2021 t1 t2 t3 t4
2021 t1 t2 t3 t4

2、行转列

    select  year, '1' as month,index1 as index    from col_to_row
union ALL 
select  year, '2' as month, index1 as index    from col_to_row
union ALL 
select    year,  '3' as month,  index1 as index   from col_to_row
UNION ALL 
select  year,  '4' as month, index1 as index   from col_to_row

原先结果:
Year index1 index2 index3 index4
2021 t1 t2 t3 t4
2021 t1 t2 t3 t4

转换之后结果:
year month index
2021 3 t1
2021 3 t1
2021 2 t1
2021 2 t1
2021 1 t1
2021 1 t1
2021 4 t1
2021 4 t1

3、去重问题

针对多列中 只有一列时重复的
参考的文档 :

https://blog.csdn.net/djun100/article/details/84473838?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.pc_relevant_is_cache&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.pc_relevant_is_cache

SQL :select *, count(distinct name) from table group by name
具体的例子:

select *,count(DISTINCT  sex) from   demo2  a
JOIN  demo1  b
ON a.id=b.id
where ----- 9月1号 到 9月 15 的数据
TO_DATE(CONCAT(SUBSTR(date,1,4),SUBSTR(date,6,2),SUBSTR(date,9,2)),'yyyymmdd') > 
TO_DATE(CONCAT(SUBSTR("2020-09-01",1,4),SUBSTR("2020-09-01",6,2),SUBSTR("2020-09-01",9,2)),'yyyymmdd') 
and 
TO_DATE(CONCAT(SUBSTR(date,1,4),SUBSTR(date,6,2),SUBSTR(date,9,2)),'yyyymmdd') <
TO_DATE(CONCAT(SUBSTR("2020-11-12",1,4),SUBSTR("2020-11-12",6,2),SUBSTR("2020-11-12",9,2)),'yyyymmdd') 
and b.type='学生' and b.name="张三" and a.color='0' GROUP by sex