将多个select语句组合以输出一行的最低成本方法

I have the following SELECT statements in a stored procedure:

我在存储过程中有以下SELECT语句：

SELECT COUNT(*) AS TotalCreditDenied
FROM   Table1
WHERE  StatusId = 8
       AND ManufacturerId = 1;

SELECT COUNT(*) AS TotalCreditApproved
FROM   Table1
WHERE  StatusId = 7
       AND ManufacturerId = 1;

SELECT SUM(CreditInvoiceAmount) AS TotalCreditPaid
FROM   Table1
WHERE  ManufacturerId = 1

SELECT SUM(ApproxCreditDue) AS OutstandingBalance
FROM   Table1
WHERE  StatusId = 3
       AND ManufacturerId = 1;

SELECT AVG(DATEDIFF(day, ReturnDate, CreditInvoiceDate)) AS AverageTimeToPay
FROM   Table1
WHERE  ManufacturerId = 1

I am trying to find the fastest and least costly way to get these return values back as a single result set with one row. Right now, it returns 5 result sets. I can use UNION to get one column of all the values, but how could I get the return values in 5 columns and one row.

我试图找到最快，成本最低的方法来将这些返回值作为单行结果集返回一行。现在，它返回5个结果集。我可以使用UNION获取所有值的一列，但是如何获得5列和1行的返回值。

Additionally, is there any way to execute these queries so the database only has to be scanned once instead of 5 times?

另外，有没有办法执行这些查询，所以数据库只需扫描一次而不是5次？

Thanks!

谢谢！

4 个解决方案

#1

You can do this with a SELECT ... WHERE ManufacturerId = 1 and CASE

您可以使用SELECT ... WHERE ManufacturerId = 1和CASE执行此操作

SELECT COUNT(CASE
               WHEN StatusId = 8
                 THEN 1
             END)                                        AS TotalCreditDenied,
       COUNT(CASE
               WHEN StatusId = 7
                 THEN 1
             END)                                        AS TotalCreditApproved,
       SUM(CreditInvoiceAmount)                          AS TotalCreditPaid,
       SUM(CASE
             WHEN StatusId = 3
               THEN ApproxCreditDue
           END)                                          AS OutstandingBalance,
       AVG(DATEDIFF(day, ReturnDate, CreditInvoiceDate)) AS AverageTimeToPay
FROM   Table1
WHERE  ManufacturerId = 1;

#2

You should be able to consolidate those into something like the following:

您应该能够将这些内容合并为以下内容：

Select SUM(CASE StatusId WHEN 8 THEN 1 ELSE 0 END) as TotalCreditDenied 
     , SUM(CASE StatusId WHEN 7 THEN 1 ELSE 0 END) as TotalCreditApproved
     , SUM(CreditInvoiceAmount) as TotalCreditPaid
     , SUM(CASE StatusId WHEN 3 THEN ApproxCreditDue ELSE 0 END) as OutstandingBalance
     , AVG(DATEDIFF(day,ReturnDate, CreditInvoiceDate)) as AverageTimeToPay  
    from Table1 where ManufacturerId = 1;

#3

You can run it in a single SELECT using some CASE:

您可以使用一些CASE在单个SELECT中运行它：

SELECT
   SUM(CASE WHEN StatusId = 8 THEN 1 ELSE 0 end) AS TotalCreditDenied,
   SUM(CASE WHEN StatusId = 7 THEN 1 ELSE 0 end) AS TotalCreditApproved,
   SUM(CreditInvoiceAmount) AS TotalCreditPaid,
   SUM(CASE WHEN StatusId = 3 THEN ApproxCreditDue ELSE 0 end) AS OutstandingBalance,
   AVG(DATEDIFF(DAY,ReturnDate, CreditInvoiceDate)) AS AverageTimeToPay 
FROM Table1 
WHERE ManufacturerId = 1

#4

Use of CASE WHEN come handy to acheive what you desire

使用CASE WHEN可以方便地实现您的需求

SELECT  TotalCreditDenied   = COUNT(CASE WHEN StatusId = 8 THEN 1 END),
        TotalCreditApproved = COUNT(CASE WHEN StatusId = 7 THEN 1 END),
        TotalCreditPaid     = SUM(CreditInvoiceAmount),
        OutstandingBalance  = SUM(CASE WHEN StatusId = 3 THEN ApproxCreditDue END),
        AverageTimeToPay    = AVG(DATEDIFF(day,ReturnDate, CreditInvoiceDate))
FROM Table1 
WHERE ManufacturerId = 1

#1