2021-03-26

select
       s1.buyer_name,
       ((sum(round(cast(date_part('day',e.acceptance_time::timestamp-c.time::timestamp) as numeric)) ))/count(s1.buyer_name)) as zq
from (select sum(a.num) as num, a.contract_id
      from ces_contract_cn_price a
      where a.isdelete = 0
      group by a.contract_id) b,
     (select sum(a.wtg_deliver_num) as num, a.contract_id, a."year", a."month", max(a.reality_due_date) as time
      from ces_contract_cn_receive_report a
      where a.isdelete = '0'
        and milestone_name ~ '到货'
      group by a.contract_id, a."year", a."month") c,
     (select a.contract_id
      from ces_contract_cn_contract_milestone a
      where a.milestone_name ~ '预验收'
        and a.isdelete = 0
      group by a.contract_id) d,
     (select contract_id, acceptance_time
      from ces_contract_cn_contract_milestone a
      where exists(select 1
                   from (select contract_id, max(milestone_name) as milestone_name
                         from ces_contract_cn_contract_milestone
                         where milestone_name ~ '预验收'
                           and isdelete = 0
                           and is_real = 1
                           and (add_id isnull or add_id = '')
                         group by contract_id) b
                   where b.contract_id = a.contract_id
                     and b.milestone_name = a.milestone_name)
        and a.milestone_name ~ '预验收'
        and a.isdelete = 0
        and a.is_real = 1
        and (a.add_id isnull or a.add_id = '')) e
LEFT JOIN ces_contract_cn_subject s1 ON e.contract_id = s1.contract_id and s1.isdelete = 0 AND s1.type = '2'
where b.contract_id = c.contract_id
  and b.contract_id = d.contract_id
  and b.contract_id = e.contract_id
  and b.num = c.num
  and e.acceptance_time is not null
group by s1.buyer_name
order by  zq asc limit 10

数据库语句 

这个是这几天用的数据库查询的，感觉自己真的太累了，刚入职，这边真的挺好的，慢慢适应吧。

((sum(round(cast(date_part('day',e.acceptance_time::timestamp-c.time::timestamp) as numeric)) ))/count(s1.buyer_name)) as zq   这个里面的函数一个(cast(date_part('day',e.acceptance_time::timestamp-c.time::timestamp) as numeric))  这个是计算两个日期相差多少天。这个里面用到了  round() 这个函数。这个是保留两位小数。也可以保留整数。sum()  这个求得总和。

这个数据库是postgresql  语句，刚刚接触，以后慢慢的学习。