我有2个表,Task
和Transaction
,如下所示:
任务
交易
我想创建一个查询,从两个表中返回信息。
列: name, priority, waiting, error, done, total, status
在哪里:
我尝试了INNER
JOIN,但得到了错误的结果:
SELECT tk.name, tk.priority, waiting.waiting, error.error, done.done, total.total
FROM task AS tk, transaction AS tran
INNER JOIN (
SELECT count(id) AS waiting
FROM transaction
WHERE status = 1
) AS waiting
INNER JOIN (
SELECT count(id) AS error
FROM transaction
WHERE status = 3
) AS error
INNER JOIN (
SELECT count(id) AS done
FROM transaction
WHERE status = 4
) AS done
INNER JOIN (
SELECT count(id) AS total
FROM transaction
) AS total;
您能帮我创建此查询吗?我正在等待的列,错误,完成,总计所有事务的计数。相反,它应该获取WHERE task.id = transaction.task和transaction.status = 1,2,3的事务数。
现有查询存在一些问题。首先,您不会在任何列上将两个表连接在一起。看来您可以加入task.id
和transaction.task
。第二。您应该能够通过使用带有某些条件逻辑的聚合函数(例如CASE表达式)来获得每个总数:
SELECT tk.name, tk.priority,
sum(case when tran.status = 1 then 1 else 0 end) waiting,
sum(case when tran.status = 3 then 1 else 0 end) error,
sum(case when tran.status = 4 then 1 else 0 end) done,
count(*) total
FROM task AS tk
INNER JOIN transaction AS tran
ON tk.id = tran.task
GROUP BY tk.name, tk.priority;
这种查询称为PIVOT,在这里您可以从行中获取值并将其转换为行。在CASE表达式中,您将只获得每个的总数status
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句