英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论