加入查询混淆 - 我不知道如何加入3个查询

First query:

select 
    pid, month(dates) as month, sum(quan) as quan 
from 
    purchase 
where 
    month(dates) between 1 and 12 
    and pid = 4 
group by 
    pid, month(dates)

Second query:

select 
    pid, month(dates) as months, sum(quan) as TotalAmount 
from 
    inward 
where 
    month(dates) between 1 and 12 
    and pid = 4 
group by 
    pid, month(dates)

Third query:

select 
    pid, month(dates) as months, sum(quan) as TotalAmount 
from 
    issue 
where 
    month(dates) between 1 and 12 
    and pid = 4 
group by 
    pid, month(dates)

I don't know how to join these queries for output pid is foreign key

我不知道如何加入这些查询输出pid是外键

2 个解决方案

#1

Try using the join statement

尝试使用join语句

SELECT
pid,month(dates) AS month,sum(quan) AS quan 
FROM purchase 
    INNER Join
pid,month(dates) AS months , sum(quan) AS TotalAmount
FROM inward
    INNER JOIN
pid,month(dates) AS months , sum(quan) AS TotalAmount
FROM issue
WHERE month(dates) between 1 and 12 AND pid=4
GROUP BY pid, month(dates)

#2

Join the three queries as if they were tables. You need parentheses around them, i.e. from (...) join (...):

加入三个查询就好像它们是表格一样。你需要围绕它们的括号,即从(...)加入(...):

select
  pid, 
  month,
  coalesce(sum_purchase.total, 0) as total_purchase,
  coalesce(sum_inward.total, 0) as total_inward,
  coalesce(sum_issue.total, 0) as total_issue
from
(
  select pid, month(dates) as month, sum(quan) as total
  from purchase 
  where pid=4 
  group by pid, month(dates)
) sum_purchase
full outer join
(
  select pid, month(dates) as month, sum(quan) as total
  from inward 
  where pid=4 
  group by pid, month(dates)
) sum_inward using (pid, month)
full outer join
(
  select pid, month(dates) as month, sum(quan) as total
  from issue 
  where pid=4 
  group by pid, month(dates)
) sum_issue using (pid, month)
order by pid, month;

This is standard SQL and should work in many DBMS. Some don't support full outer joins however, others don't support using clauses. So you may have to adjust this a little.

这是标准SQL,应该适用于许多DBMS。有些不支持完全外连接,但有些不支持使用子句。所以你可能需要稍微调整一下。

#1