历史数据回溯&竖表转横表

select *
from Tb
 pivot(max(字段3)
       for 字段2 in(数据源1,数据源2, 数据源3 )) a

                    
                    
select 字段1,
 max(case 字段2 when '数据源1' then 字段3 else null end) 数据源1,
 max(case 字段2 when '数据源2' then 字段3 else null end) 数据源2,
 max(case 字段2 when '数据源3' then 字段3 else null end) 数据源3
from Tb
group by 字段1

BEGIN
-- 按日回退还款明细数据,start_date格式(2019-05-01)
declare start_date date;
set @start_date = daydate;
  -- drop table if EXISTS repayment_detail_day;
  -- create table repayment_detail_day as
insert into repayment_detail_day
select a.*,
       case when  date(当前计划还款日) >= @start_date then 0
            when  date(还款日期) is null then 1
            when  date(还款日期) is not null and  date(还款日期)<= date(当前计划还款日) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date> date(还款日期) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date<= date(还款日期) then  1
        end as 当前是否逾期,
       case when  date(当前计划还款日) >= @start_date then 0
            when  date(还款日期) is null then to_days(@start_date)-to_days( date(当前计划还款日))
            when  date(还款日期) is not null and  date(还款日期)<= date(当前计划还款日) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date> date(还款日期) then to_days( date(还款日期))-to_days( date(当前计划还款日))
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date<= date(还款日期) then to_days(@start_date)-to_days( date(当前计划还款日))
       end as 付清时或当前逾期天数,
       @start_date 当前日期
from (
      select c.contract_id 合同ID,
             c.customer_num 客户编号,
             d.repaying_plan_detail_id 还款计划明细ID,
             d.current_period 当前期数,
             d.current_end_date 当前计划还款日,
             d.current_principal 当前应还本金,
             d.pay_loan_date 还款日期,
             d.repayed_principal 已还本金
       from repaying_plan c
       join repaying_plan_detail d 
         on c.repaying_plan_id=d.repaying_plan_id
       where pay_loan_date < @start_date
      order by contract_id,repaying_plan_id,current_period ) a
order by 合同ID,当前期数;
END

 

mysql> select * from students;
+----------+---------+--------+
| name     | kecheng | fenshu |
+----------+---------+--------+
| zhangsan | yuwen   |     81 |
| zhangsan | shuxue  |     75 |
| lisi     | yuwen   |     76 |
| lisi     | shuxue  |     76 |
| wangwu   | yuwen   |     81 |
| wangwu   | shuxue  |    100 |
| wangwu   | yingyu  |     90 |
+----------+---------+--------+
mysql> select distinct name from students where name not in (select distinct name from students where fenshu<=80);
+--------+
| name |
+--------+
| wangwu |

 

mysql> select name from students group by name having min(fenshu)>80;
+--------+
| name |
+--------+
| wangwu |
+--------+