主页 / 问题 / 834046
Accepted
Alexander Semikashev
Asked:2020-05-27 17:40:12 +0000 UTC2020-05-27 17:40:12 +0000 UTC

合并两个 SQL 查询

  • 772

有两张桌子。第一个包含公司的表company，字段：company_id, company_name。

第二个表带有acts act，字段：act_id，receipt_date（收据日期），issue_date（签发日期，可能为Null）。

因此，我需要一个查询，以便数据采用这种形式：

company_id…… company_name_ количество полученых актов за 27.02.2018_количество выданных актов за 27.02.2018

company_id…… company_name_ количество полученых актов за 26.02.2018_количество выданных актов за 26.02.2018

等等...

请求是从我单独获得的，这没有问题。

select c.id company_id, c.name company, DATE(receipt_date) receipt, count(*) count from act
join company c on c.id = act.company_id
where 

    act.is_delete = 0
group by act.company_id, receipt

 

select c.id company_id, c.name company, DATE(issue_date) issue, count(*) count from act
    join company c on c.id = act.company_id
    where 

        act.is_delete = 0
    group by act.company_id, issue

但是如何组合这些请求呢？这没用。

sql

1 个回答

  1. Best Answer

    选项1

    select company_id,
       date(receipt_date) as "dt",
       count(*)           as "qty"
  into #receipt_total
  from act
where is_delete = 0
group by company_id,
        "dt"
;
-- indexes

select company_id,
       date(receipt_date) as "dt",
       count(*)           as "qty"
  into #issue_total
  from act
where is_delete = 0
group by company_id,
        "dt"
;
-- indexes


select isnull(t1.company_id, t2.company_id) as "company_id",
       isnull(t1.dt, t2.dt)                 as "dt",
       isnull(t1.qty, 0)                    as "qty_receipt",
       isnull(t2.qty, 0)                    as "qty_issue"
  into #result
  from #receipt_total    as t1
  full join #issue_total as t2 on t2.company_id = t1.company_id
                              and t2.dt = t1.dt
;

select t1.company_id,
       t2.company_name,
       t1.dt,
       t1.qty_receipt,
       t1.qty_issue
  from #result as t1
  join company as t2 on t2.id = t1.company_id
;

    选项 2

    select company_id,
       'receipt'          as "rtype",
       date(receipt_date) as "dt",
       count(*)           as "qty"
  into #receipt_total
  from act
where is_delete = 0
group by company_id,
        "dt"
;
-- indexes

select company_id,
       'issue'            as "rtype",
       date(receipt_date) as "dt",
       count(*)           as "qty"
  into #issue_total
  from act
where is_delete = 0
group by company_id,
        "dt"
;
-- indexes

select *
  into #total_data
  from #receipt_total
union all
select *
  from #issue_total
;

select company_id,
       dt,
       sum(case when rtype = 'receipt'
                 then qty
             end)                        as "qty_receipt",
       sum(case when rtype = 'issue'
                 then qty
             end)                        as "qty_issue"
  into #result
  from #total_data
;

select t1.company_id,
       t2.company_name,
       t1.dt,
       t1.qty_receipt,
       t1.qty_issue
  from #result as t1
  join company as t2 on t2.id = t1.company_id
;
    • 0

相关问题