sql - 将多个结果合并为列，而不是行

Question

我需要对数据库中的各种表执行一些计数
我想将这些计数合并为一个结果。

考虑以下查询:

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

如果我使用 UNION 组合它们，每个结果将成为最终结果中的一行:

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

输出:

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

我想要的是

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

我能想到的“命名”结果的唯一其他方法是使用类似这样的方法:

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

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

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

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

我应该提一下，为了解释核心问题，上面的查询非常简单。实际上，需要合并的两个查询可能如下所示:

查询 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。

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

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

当然，在现实中，还有很多事情要算

Answer 1

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

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