多个 SQL 计入单个结果集

Question

大家好吗

我正在尝试做一些我认为在 MYSQL 上不可能的事情。

我们

有一个非常大的数据库，从中提取我们创建的产品的生产报告。目前，我们运行多个查询来获取这些结果，然后在将数据发送给需要报告的任何人之前手动将数据传输到表中。

有没有更简单的方法可以做到这一点。IE 单个查询。

我运行的查询示例

a. SELECT count(id) from ProductA_T where created between '<date>' and '<date>' and productstatus = "<successful>";
b. SELECT count(id) from ProductB_T where created between '<date>' and '<date>' and productstatus = "<successful>";
c. SELECT count(id) from ProductC_T where created between '<date>' and '<date>' and productstatus = "<successful>";

我正在寻找的结果示例

1. 产品A 产品B 产品C
500
2. 500 500

Answer 1

您可以将所有内容放入 Select 子句中，如下所示：

Select
(SELECT count(id) from ProductA_T where created between '<date>' and '<date>' and productstatus = "<successful>") as CountA
(SELECT count(id) from ProductB_T where created between '<date>' and '<date>' and productstatus = "<successful>") as CountB
(SELECT count(id) from ProductC_T where created between '<date>' and '<date>' and productstatus = "<successful>") as CountC

这样，您将拥有如下输入：

• 计数 A 计数 B 计数 C
500
• 500 500

您以后可以轻松添加更多计数

Answer 2

您可以使用 UNION ALL：

SELECT 'ProductA', count(id) from ProductA_T where created between '<date>' and '<date>' and productstatus = "<successful>"
UNION ALL
SELECT 'ProductB', count(id) from ProductB_T where created between '<date>' and '<date>' and productstatus = "<successful>"
UNION ALL
SELECT 'ProductC', count(id) from ProductC_T where created between '<date>' and '<date>' and productstatus = "<successful>";

当然，您可以复制其他 4 种产品的选择或任何您想要的数量;-）

Answer 3

我认为您需要使用存储过程

Create StoredProcedure SP_Name_1
As
Beign
Declare @id1 int,@id2 int,@id3 int;
SELECT @id1 = count(id) from ProductA_T where created between '<date>' and '<date>' and productstatus = "<successful>";
SELECT @id2 = count(id) from ProductB_T where created between '<date>' and '<date>' and productstatus = "<successful>";
SELECT @id2 = count(id) from ProductC_T where created between '<date>' and '<date>' and productstatus = "<successful>";
select  @id1 as ProductA ,@id2 as ProductB ,@id3 as ProductC
End

谢谢塔哈