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

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

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的数据后出现。我希望所有数据都按日期一次性获取。

  1. SELECT job_party.on_date
  2.      , SUM(job_party_details.qty) as detail_qty
  3.      , NULL as delv 
  4.   FROM job_party_details d
  5.   JOIN job_party p
  6.     on d.jp_id = p.id 
  7.  where p.party_id = 9 
  8.    and d.i_id = 1 
  9.  GROUP 
  10.     BY p.on_date 
  11.  UNION
  12. SELECT p.on_date
  13.      , NULL as detail_qty
  14.      , SUM(d.d_qty) as delv 
  15.   FROM job_party_delv d 
  16.    JOIN job_party p 
  17.     on d.jp_id = p.id 
  18.  where p.party_id = 9 
  19.    and d.i_id = 1 
  20.  GROUP 
  21.     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.

  1. SELECT job_party.on_date
  2.      , SUM(job_party_details.qty) as detail_qty
  3.      , NULL as delv 
  4.   FROM job_party_details d
  5.   JOIN job_party p
  6.     on d.jp_id = p.id 
  7.  where p.party_id = 9 
  8.    and d.i_id = 1 
  9.  GROUP 
  10.     BY p.on_date 
  11.  UNION
  12. SELECT p.on_date
  13.      , NULL as detail_qty
  14.      , SUM(d.d_qty) as delv 
  15.   FROM job_party_delv d 
  16.    JOIN job_party p 
  17.     on d.jp_id = p.id 
  18.  where p.party_id = 9 
  19.    and d.i_id = 1 
  20.  GROUP 
  21.     BY p.on_date

答案1

得分: 0

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

  1. SELECT on_date, SUM(detail_qty) as detail_qty, SUM(delv) as delv 
  2. FROM
  3. (SELECT job_party.on_date
  4.      , SUM(job_party_details.qty) as detail_qty
  5.      , 0 as delv 
  6.   FROM job_party_details d
  7.   JOIN job_party p
  8.     on d.jp_id = p.id 
  9.  where p.party_id = 9 
  10.    and d.i_id = 1 
  11.  GROUP 
  12.     BY p.on_date 
  13.  UNION
  14. SELECT p.on_date
  15.      , 0 as detail_qty
  16.      , SUM(d.d_qty) as delv 
  17.   FROM job_party_delv d 
  18.    JOIN job_party p 
  19.     on d.jp_id = p.id 
  20.  where p.party_id = 9 
  21.    and d.i_id = 1 
  22.  GROUP 
  23.     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:

  1. SELECT on_date,SUM(detail_qty) as detail_qty, SUM(delv) as delv 
  2. FROM
  3. (SELECT job_party.on_date
  4.      , SUM(job_party_details.qty) as detail_qty
  5.      , 0 as delv 
  6.   FROM job_party_details d
  7.   JOIN job_party p
  8.     on d.jp_id = p.id 
  9.  where p.party_id = 9 
  10.    and d.i_id = 1 
  11.  GROUP 
  12.     BY p.on_date 
  13.  UNION
  14. SELECT p.on_date
  15.      , 0 as detail_qty
  16.      , SUM(d.d_qty) as delv 
  17.   FROM job_party_delv d 
  18.    JOIN job_party p 
  19.     on d.jp_id = p.id 
  20.  where p.party_id = 9 
  21.    and d.i_id = 1 
  22.  GROUP 
  23.     BY p.on_date) A GROUP BY on_date;

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

发表评论

匿名网友

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

确定