tsql - 如何设计T-SQL查询以一次计算总和？

Question

我正在尝试开发将执行以下操作的T-SQL查询:

ROUND(100 * A / B, 1)

概念上简单，但由于可能的B = 0分母以及A和B变量，因此比较棘手。我期望的是像93.2这样的百分比值(以这种格式给出，不含％)。甚至932也可以接受，因为我以后可以转换它。

但是，相反，我目前的记录数是151。

A = CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END
B = CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END

我当前的逻辑仅在B不等于0时对A / B进行除法。你能帮我解决这个问题吗？ ps。以上所有字段均来自同一表A。

我试过了:

SELECT CASE WHEN t.VarB<>0 THEN ROUND(100 * t.VarA / t.VarB, 1) 
ELSE 0 /* or whatever you'd want to return in this case */ 
END 
FROM (SELECT CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 
ELSE 0 
END AS VarA, 
CASE WHEN [Date_Completed] IS NOT NULL THEN 1 
ELSE 0 
END AS VarB 
FROM EXCEL.Batch_Records A) t

但是我得到了33000行，而不是仅仅返回一行，其中每一行= 100或0。

好主意，康拉德!我测试了您的解决方案，如果我只想要一个值，它就可以工作。但是我没有告诉您的是，我需要从同一查询中返回其他值。当我尝试添加其他值计算时，出现语法错误。所以这是我当前的查询。请如何改写？

select 
SUM(CASE WHEN A.DATE_RECEIVED IS NOT NULL THEN 1 ELSE 0 END) AS NUM_RECEIVED,
SUM(CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END) AS NUM_COMPLETE_OF_OPENED,
SUM(CASE WHEN A.DATE_COMPLETED IS NOT NULL THEN 1 ELSE 0 END) AS NUM_COMPLETED_IN_MONTH,
SUM(CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END) AS NUM_WITHOUT_ERROR,

round(100 * a/b , 1) 
from 
(select 
    sum(CASE  
        WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN  
            1.0  
        ELSE 0.0 END) A, 
    sum(CASE WHEN [Date_Completed] IS NOT NULL THEN 

1.0 ELSE 0.0 END) B 

FROM EXCEL.Batch_Records a 
LEFT JOIN EXCEL.QC_CODES d ON a.Part_Number = d.CODE_ID    
WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received >= @STARTDATE AND A.Date_Received <= @ENDDATE

康拉德正确地告诉我＃TEMP1是一个空表。但是现在我填充了它，并在他的帮助下成功设计了该查询:

SET @STARTDATE = '1/1/11'
SET @ENDDATE = '1/31/11'
SET @GROUP = 'INTERMEDIATES_FISH'
--SET @TABLE_TITLE = 'BATCH RECORD SUCCESS RATE'
--SET @DEPT = 'QC'     

IF EXISTS(SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#TEMP1%')
DROP TABLE #TEMP1

--CREATE TABLE #TEMP1 (     MFG int ,      MFG2 int ,     QC int,      QC2 INT ,      [Group] NVARCHAR(MAX),     [Date_Completed] datetime,     Date_Received datetime)
SELECT
MFG, MFG2, QC, QC2, [GROUP], [DATE_COMPLETED], [DATE_RECEIVED]
INTO #TEMP1
FROM EXCEL.Batch_Records a 
WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received >= @STARTDATE AND A.Date_Received <= @ENDDATE

------------------------------------------  
;WITH CTE AS 
( 
SELECT 
CASE 
WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 
1.0 
ELSE 0.0 END A, 
CASE WHEN [Date_Completed] IS NOT NULL THEN 1.0 ELSE 0.0 END B, 
CASE WHEN A.Date_Received IS NOT NULL THEN 1 ELSE 0 END NUM_RECEIVED, 
CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END NUM_COMPLETE_OF_OPENED, 
CASE WHEN A.DATE_COMPLETED IS NOT NULL THEN 1 ELSE 0 END NUM_COMPLETED_IN_MONTH, 
CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END AS NUM_WITHOUT_ERROR 
FROM 
#TEMP1 a 
--WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received >= @STARTDATE AND A.Date_Received <= @ENDDATE
) 

select 
round(100 * SUM(A)/SUM(b) , 1) , 
SUM(NUM_RECEIVED) NUM_RECEIVED, 
SUM(NUM_COMPLETE_OF_OPENED) NUM_COMPLETE_OF_OPENED, 
SUM(NUM_COMPLETED_IN_MONTH) NUM_COMPLETED_IN_MONTH, 
SUM(NUM_WITHOUT_ERROR) NUM_WITHOUT_ERROR 


FROM CTE 

Answer 1

基本上，您需要使用SUM()来获取总和。您还应该使用1.0和0.0，以便获得十进制值。

您也应该在部门前进行SUM

更新
由于要添加许多SUM(CASE语句，因此将CASE语句移至CTE的可读性更高。

CREATE TABLE #Batch_Records (
    MFG int , 
    MFG2 int ,
    QC int, 
    QC2 INT , 
    [Group] int,
    [Date_Completed] datetime,
    Date_Received datetime)




INSERT INTO #Batch_Records (MFG ,   MFG2 ,  QC ,    QC2  ,  [Group] ,   [Date_Completed] ,  Date_Received )
VALUES (1,null,null,null,1,'1/4/2011','2/4/2011'),
       (null,null,null,null,1,'2/2/2011','3/4/2011'),
       (1,null,null,null,1,'3/6/2011','4/3/2011'),
       (null,null,null,null,1,NULL,'5/4/2011'),
       (1,null,null,null,1,'5/4/2011','6/6/2011'),
       (1,null,null,null,1,NULL,'7/4/2011')


DECLARE @GROUP int
DECLARE @STARTDATE DateTime
DECLARE @ENDDATE DateTime

SET @GROUP = 1
SET @STARTDATE = '1/1/2001'
SET @ENDDATE = '1/1/2012'

;WITH CTE AS
(
    SELECT
        CASE  
            WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN  
                1.0  
            ELSE 0.0 END A, 
        CASE WHEN [Date_Completed] IS NOT NULL THEN 
        1.0 ELSE 0.0 END B,
        CASE WHEN A.Date_Received IS NOT NULL THEN 1 ELSE 0 END  NUM_RECEIVED,
        CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END  NUM_COMPLETE_OF_OPENED,   
        CASE WHEN A.DATE_COMPLETED IS NOT NULL THEN 1 ELSE 0 END  NUM_COMPLETED_IN_MONTH,
        CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END AS NUM_WITHOUT_ERROR
    FROM 
        #Batch_Records a 
    WHERE 
        (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) 
        AND A.Date_Received >= @STARTDATE AND A.Date_Received <= @ENDDATE
)

select 
    round(100 * SUM(A)/SUM(b) , 1) ,
    SUM(NUM_RECEIVED) NUM_RECEIVED,
    SUM(NUM_COMPLETE_OF_OPENED) NUM_COMPLETE_OF_OPENED,
    SUM(NUM_COMPLETED_IN_MONTH) NUM_COMPLETED_IN_MONTH,
    SUM(NUM_WITHOUT_ERROR) NUM_WITHOUT_ERROR


 FROM CTE

 DROP TABLE #Batch_Records