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