组合按同一列分组的两个GROUP BY查询

Question

我有一个事务表和一个标记表。我想获得按标签分组的事务表中所有事务的总和。有两种不同类型的事务:"预算"one_answers"实际"

这个查询将返回我想要的"预算"事务:

  SELECT tg.name as tag, SUM(amount) as budgetTotal 
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN tag tg ON tg.id = tt.tag_id
   WHERE tx.type = "budget"
     AND tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
GROUP BY tg.name

当然，对于"实际"事务的查询也是相同的:

  SELECT tg.name as tag, SUM(amount) as actualTotal 
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN tag tg ON tg.id = tt.tag_id
   WHERE tx.type = "actual"
     AND tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
GROUP BY tg.name

我的问题:我如何将这两个查询的结果分组成一个，这样我就得到一个结果表，其中有三列:tag, budgetTotal和actualTotal?

Answer 1

试试这个:

  SELECT tg.name, 
         CASE WHEN tx.type = "actual" THEN SUM(amount) END AS actualTotal,
         CASE WHEN tx.type = "budget" THEN SUM(amount) END AS budgetTotal
  FROM....
  WHERE  tx.type IN ("actual", "budget")
  AND   ....
  GROUP BY tg.name

Answer 2

SELECT tg.name as tag, SUM(amount) as budgetTotal, 'budget' as rectype
FROM transaction tx
JOIN transaction_tag tt ON tt.transaction_id = tx.id
JOIN tag tg ON tg.id = tt.tag_id
WHERE tx.type = "budget"
AND tx.date >= '2011-07-15' 
AND tx.date < '2011-08-15'
GROUP BY tg.name
UNION ALL
SELECT tg.name as tag, SUM(amount) as actualTotal, , 'actual' as rectype
FROM transaction tx
JOIN transaction_tag tt ON tt.transaction_id = tx.id
JOIN tag tg ON tg.id = tt.tag_id
WHERE tx.type = "actual"
AND tx.date >= '2011-07-15' 
AND tx.date < '2011-08-15'
GROUP BY tg.name

Answer 3

不是轻视另一个答案(可能更好)，但如果合适的话，这里是如何将其作为两个单独的行。此外，这个答案可以扩展到任意数量的tx.type，而无需更改查询(当然，如果您在where子句中删除对t.type的引用):

SELECT tg.name as tag, tx.type, SUM(amount) as total
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN tag tg ON tg.id = tt.tag_id
   WHERE tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
     AND tx.type in ("budget", "actual")
GROUP BY tg.name, tx.type;