# sql

## 车辆黄皮书越界

```sql
with t101 as (
select
    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM') as year_month,
    first_unit,
    count(1) as total,
    count(shi_fou_zhi_xing_zhong_yue_jie = '是' or null) as ls,
    count(shi_fou_zhi_xing_zhong_yue_jie = '否' or null) as wg
from
    car.car_yue_jie
where
    year_month = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM')::int4
group by
    first_unit
),
t102 as (
select
    year_month,
    car.car_second_unit_sort.second_unit,
    coalesce(total, 0) as total,
    null as bjx,
    coalesce(ls, 0) as ls,
    coalesce(wg, 0) as wg
from
    t101
right join car.car_second_unit_sort on
    t101.first_unit = car.car_second_unit_sort.second_unit
where
    car_second_unit_sort.second_unit in ('石家庄', '唐山', '秦皇岛', '邯郸', '邢台', '保定', '张家口', '承德', '廊坊', '沧州', '衡水', '雄安', '机动局', '省公司本部')
order by
    car.car_second_unit_sort.sort desc
)
select
    *
from
    t102
```

## 车辆黄皮书费用

```sql
with
t101 as (
select
    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM') as month_id,
    second_org_no,
    second_org_name,
    round(sum(case when kpi_id in ('KPI_001', 'KPI_002', 'KPI_004', 'KPI_005', 'KPI_006', 'KPI_007', 'KPI_008', 'KPI_009', 'KPI_010', 'KPI_011', 'KPI_013', 'KPI_014', 'KPI_015', 'KPI_016', 'KPI_017', 'KPI_018', 'KPI_019', 'KPI_020', 'KPI_021', 'KPI_022', 'KPI_023') then ty_value else 0 end) / 10000, 2) as ty_fy,
    round(sum(case when kpi_id in ('KPI_001', 'KPI_002', 'KPI_004', 'KPI_005', 'KPI_006', 'KPI_007', 'KPI_008', 'KPI_009', 'KPI_010', 'KPI_011', 'KPI_013', 'KPI_014', 'KPI_015', 'KPI_016', 'KPI_017', 'KPI_018', 'KPI_019', 'KPI_020', 'KPI_021', 'KPI_022', 'KPI_023') then ly_value else 0 end) / 10000, 2) as ly_fy
from
    car.car_fee_hb
where
    second_org_no is not null
    and month_id = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM')::int4
group by
    second_org_no,
    second_org_name
),
t102 as (
select
    t101.*,
    case
        when ly_fy = 0 then null
        else round((ty_fy - ly_fy) / ly_fy * 100, 2)
    end as tb
from
    t101
join car.car_second_unit_sort c on
    t101.second_org_name = c.second_unit
order by
    c.sort desc
)
select
    *
from
    t102
```

## 查询接口日志

```sql
select
    *
from
    common.nginx_log
where
    login_id != 'test'
order by
    access_time desc
```

## 查询数据处理日志

```sql
select
    *
from
    common.data_log
order by
    create_time desc
```

## 车辆租赁费缺失检查
```sql
select
    *
from car.car_base_data_month a
where year_month = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM')::int4 and che_liang_lai_yuan = '租赁车辆' and not exists (select 1 from car.car_zu_lin b where a.year_month = b.year_month and a.che_pai_hao = b.che_pai_hao)
```

## 检查车辆基本信息删除但没有报废
```sql
select
    *
from
    car.car_base_data_month a
where
    year_month = TO_CHAR(CURRENT_DATE - interval '2 month', 'YYYYMM')::int4
    and not exists (
    select
        1
    from
        car.car_base_data_day b
    where
        b.year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4
        and b.data_date = (select max(data_date) from car.car_base_data_day where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4)
        and a.year_month = b.last_month
        and a.che_pai_hao = b.che_pai_hao)
    and not exists (
    select
        1
    from
        car.car_bao_fei b
    where a.che_pai_hao = b.che_pai_hao and b.month_id = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4);
select * from car.car_base_data_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 and car_new = 1;
```
```sql
select
    *
from
    car.car_base_data_month a
where
    year_month = TO_CHAR(CURRENT_DATE - interval '2 month', 'YYYYMM')::int4
    and not exists (
    select
        1
    from
        car.car_base_data_month b
    where
        a.year_month = b.last_month
        and a.che_pai_hao = b.che_pai_hao)
    and not exists (
    select
        1
    from
        car.car_bao_fei b
    where a.che_pai_hao = b.che_pai_hao and b.month_id = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4);
select * from car.car_base_data_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 and car_new = 1;
```

## 检查同时存在车辆基本信息和报废
```sql
select
    b.bao_fei_ri_qi,
    *
from
    car.car_base_data_day a
join car.car_bao_fei b on
    a.year_month = b.month_id
    and a.che_pai_hao = b.che_pai_hao
where
    a.year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4
    and a.data_date = (select max(data_date) from car.car_base_data_day where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4)
```
```sql
select
    b.bao_fei_ri_qi,
    a.*
from
    car.car_base_data_month a
join car.car_bao_fei b on
    a.year_month = b.month_id
    and a.che_pai_hao = b.che_pai_hao
where
    a.year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4
```

## 降序查询数据
```sql
select * from house.building_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by building_area desc;
select * from house.building_repair_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by final_cost desc;
select * from house.land_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by total_land_area desc;
select * from house.rent_in_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 and contract_status = '履行中' order by gross_amount desc;
select * from house.room_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by building_area desc;
select * from car.car_bao_xian where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by total_amount desc;
select * from car.car_da_wei_xiu where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_lu_qiao where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_nian_jian_fei where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_qi_ta where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_ran_you where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_wei_xiu where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by amount_excluding_tax desc;
select * from car.car_li_cheng_month where year_month = TO_CHAR(CURRENT_DATE - interval '1 month', 'YYYYMM')::int4 order by zong_li_cheng desc;
```