从多个表中按日期选择数据。

huangapple go评论69阅读模式
英文:

selection of data from multiple table date by date

问题

我有3个不同的表,分别是job_party、job_party_details和job_party_delv。其中,job_party是主表,其他两个是详细表。我正在尝试从所有这些表中按日期收集数据。我编写了以下查询并获得了完美的数据,但问题是job_party_details的数据先出现,job_party_delv的数据后出现。我希望所有数据都按日期一次性获取。

SELECT job_party.on_date
     , SUM(job_party_details.qty) as detail_qty
     , NULL as delv 
  FROM job_party_details d
  JOIN job_party p
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date 
 UNION
SELECT p.on_date
     , NULL as detail_qty
     , SUM(d.d_qty) as delv 
  FROM job_party_delv d 
   JOIN job_party p 
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date

请注意,我只提供了代码的翻译部分,不包含任何其他内容。

英文:

I have 3 different table named job_party, job_party_details and job_party_delv. In which job_party is the main table and others are detailed tables. I am trying to gather data date by date from all these tables. I wrote the following query and getting perfect data but the problem is data of job_party_details comes first and job_party_delv comes latter. I want all data at once as per the date.

SELECT job_party.on_date
     , SUM(job_party_details.qty) as detail_qty
     , NULL as delv 
  FROM job_party_details d
  JOIN job_party p
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date 
 UNION
SELECT p.on_date
     , NULL as detail_qty
     , SUM(d.d_qty) as delv 
  FROM job_party_delv d 
   JOIN job_party p 
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date

答案1

得分: 0

NULL替换为0,然后将查询作为子查询,并在外部查询上执行另一个SUM,按on_date进行分组,如下所示:

SELECT on_date, SUM(detail_qty) as detail_qty, SUM(delv) as delv 
FROM
(SELECT job_party.on_date
     , SUM(job_party_details.qty) as detail_qty
     , 0 as delv 
  FROM job_party_details d
  JOIN job_party p
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date 
 UNION
SELECT p.on_date
     , 0 as detail_qty
     , SUM(d.d_qty) as delv 
  FROM job_party_delv d 
   JOIN job_party p 
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date) A GROUP BY on_date;
英文:

Replace NULL with 0 then make the query as a sub-query and perform another SUM on the outer query GROUP BY on_date like this:

SELECT on_date,SUM(detail_qty) as detail_qty, SUM(delv) as delv 
FROM
(SELECT job_party.on_date
     , SUM(job_party_details.qty) as detail_qty
     , 0 as delv 
  FROM job_party_details d
  JOIN job_party p
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date 
 UNION
SELECT p.on_date
     , 0 as detail_qty
     , SUM(d.d_qty) as delv 
  FROM job_party_delv d 
   JOIN job_party p 
    on d.jp_id = p.id 
 where p.party_id = 9 
   and d.i_id = 1 
 GROUP 
    BY p.on_date) A GROUP BY on_date;

huangapple
  • 本文由 发表于 2020年1月6日 15:10:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/59608010.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定