将多个结果合并为列，而不是行 [英] Combine multiple results as columns, not rows

问题描述

我需要对数据库中的各个表进行多次计数
我想将这些计数合并为一个结果.

I need to perform a number of counts on various tables in the database
and I would like to combine those counts into a single result.

考虑以下查询:

SELECT 100 As SomeCount
SELECT 200 As SomeOtherCount
SELECT 300 As YetAnotherCount

如果我使用 UNION 将它们组合起来，每个结果都将是最终结果中的一行:

If I combine them using UNION, each of the results will be a row in the final result:

SELECT 100 As SomeCount
UNION
SELECT 200 As SomeOtherCount
UNION
SELECT 300 As YetAnotherCount

输出:

> SomeCount
> --------- 
> 100 
> 200 
> 300

我想要的是

> SomeCount | SomeOtherCount | YetAnotherCount
> --------------------------------------------
>   100     |      200       |     300

我能想到的为结果命名"的唯一另一种方法是使用这样的东西:

The only other way I could think off to 'name' the results is using something like this:

SELECT 'SomeCount' As Name, 100 As Value
UNION ALL
SELECT 'SomeOtherCount', 200
UNION ALL
SELECT 'YetAnotherCount', 300

在这种情况下，结果如下:

In which case the result looks like:

>     Name          |      Value
> ---------------------------------
> 'SomeCount'       |       100
> 'SomeOtherCount'  |       200
> 'YetAnotherCount' |       300

有没有办法得到我想要的结果，还是最后一种方法可行?

Is there a way to get the results I want, or is the last method the way to go?

我应该提到上面的查询非常简单，以解释核心问题.实际上，需要组合的两个查询可能如下所示:

I should mention the queries above are very simple in order to explain the core problem. In reality two queries that need to be combined might look like this:

查询 1:

SELECT Count(Id) As UndeliveredSms
FROM
(
 SELECT Id
 FROM IncomingSms
 WHERE Id NOT IN (SELECT IncomingSmsId
                  FROM DeliveryAttempt
                  WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
 )

查询 2:

SELECT Count(Id) As UndeliveredEMail FROM
(
 SELECT Id
 FROM IncomingEMail
 WHERE Id NOT IN (SELECT IncomingEMailId
                  FROM DeliveryAttempt
                  WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
)

将这些包装在另一个 SELECT 语句中不适用于 SQlite.

Wrapping these in another SELECT statement does not work with SQlite.

使用示例中的最后一种方法确实有效，我可能会去使用该解决方案，除非这是一个坏主意:

Using the last method in the examples does work, and I might go with that solution unless it's a bad idea:

SELECT 'UndeliveredSms' As Name,  Count(Id) As Value
FROM
(
 SELECT Id
 FROM IncomingSms
 WHERE Id NOT IN (SELECT IncomingSmsId
                  FROM DeliveryAttempt
                  WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
 )

UNION

SELECT 'UndeliveredEMail', Count(Id) FROM
(
 SELECT Id
 FROM IncomingEMail
 WHERE Id NOT IN (SELECT IncomingEMailId
                  FROM DeliveryAttempt
                  WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
)

结果如下:

>     Name           |     Value
> ---------------------------------
> UndeliveredEMail   |       82
> UndeliveredSms     |       0

当然，实际上还有很多事情要计算

And of course, in reality, there are a lot more things to count

推荐答案

您应该能够在查询之间使用 CROSS JOIN:

You should be able to use a CROSS JOIN between the queries:

SELECT *
FROM
(
  SELECT Count(Id) As UndeliveredSms
  FROM
  (
   SELECT Id
   FROM IncomingSms
   WHERE Id NOT IN (SELECT IncomingSmsId
                    FROM DeliveryAttempt
                    WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
  )
)
CROSS JOIN
(
  SELECT Count(Id) As UndeliveredEMail FROM
  (
   SELECT Id
   FROM IncomingEMail
   WHERE Id NOT IN (SELECT IncomingEMailId
                    FROM DeliveryAttempt
                    WHERE Status = 'Delivered' OR Status = 'FailedPermanently')
  )
);

参见SQL Fiddle with Demo

这篇关于将多个结果合并为列，而不是行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！