带条件的SQL查询大小写

Question

我有一个sql查询，我必须得到记录计数，我得到计数正确与当前查询，但我想只得到计数的记录，其中'未检查计数'不为零。

   SELECT        
    dbo.Customer.AccountNo AS Cust_Acc_No, 
    dbo.Customer.Name AS [Customer Name], 
    dbo.Customer.Adrs1 AS Cust_Address_1,    
    dbo.Customer.Adrs2 AS Cust_Address_2, 
    dbo.Customer.City AS Cust_City, 
    dbo.Customer.Province, 
    dbo.Customer.PostalCode AS Cust_Postal_Code, 
    dbo.Customer.Email1 AS Email, 
    CAST(dbo.Customer.AccStatus AS int) AS [Account Status], 
    dbo.Customer.ID AS CID, 
    case when  COUNT(dbo.Manifest.ID) <  
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
    then  COUNT(dbo.Manifest.ID) else
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
         (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
    end as [Unchecked Count],
    case when  COUNT(dbo.Manifest.ID) <  
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
    then  COUNT(dbo.Manifest.ID) else
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
    end as [Checked_Count],
    COUNT(dbo.Manifest.ID) as Total
   FROM            
    dbo.Customer inner JOIN dbo.Manifest ON dbo.Customer.AccountNo = dbo.Manifest.FKAccountNo
   GROUP BY 
    dbo.Customer.AccountNo, 
    dbo.Customer.Name, 
    dbo.Customer.Adrs1, 
    dbo.Customer.City, 
    dbo.Customer.Province, 
    dbo.Customer.Adrs2, 
    dbo.Customer.Email1, 
    dbo.Customer.PostalCode, 
    dbo.Customer.AccStatus, 
    dbo.Customer.ID,Manifest.FKAccountNo

Answer 1

在group by条款之后尝试Having条款

having [Unchecked Count] > 0

完整查询

SELECT        dbo.Customer.AccountNo AS Cust_Acc_No, dbo.Customer.Name AS [Customer Name], dbo.Customer.Adrs1 AS Cust_Address_1, dbo.Customer.Adrs2 AS Cust_Address_2, dbo.Customer.City AS Cust_City, 
                     dbo.Customer.Province, dbo.Customer.PostalCode AS Cust_Postal_Code, dbo.Customer.Email1 AS Email, CAST(dbo.Customer.AccStatus AS int) AS [Account Status], dbo.Customer.ID AS CID, 
                    case when  COUNT(dbo.Manifest.ID) <  sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
                     then  COUNT(dbo.Manifest.ID) else
                     sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
                     end
                     as [Unchecked Count],
                      case when  COUNT(dbo.Manifest.ID) <  sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
                     then  COUNT(dbo.Manifest.ID) else
                     sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
                     end
                     as [Checked_Count],
                     COUNT(dbo.Manifest.ID) as Total
                     FROM            dbo.Customer inner JOIN
                     dbo.Manifest ON dbo.Customer.AccountNo = dbo.Manifest.FKAccountNo
                     GROUP BY dbo.Customer.AccountNo, dbo.Customer.Name, dbo.Customer.Adrs1, dbo.Customer.City, dbo.Customer.Province, dbo.Customer.Adrs2, dbo.Customer.Email1, dbo.Customer.PostalCode, dbo.Customer.AccStatus, 
                     dbo.Customer.ID,Manifest.FKAccountNo
    having [Unchecked Count] > 0

Answer 2

可以查询另一条查询的结果。SELECT * FROM ( <your query> ) AS T WHERE [Unchecked Count] > 0

Answer 3

感谢AlexanderW和Shakti的回答。我尝试了你的两个建议，但"未检查计数"是一个别名，这就是为什么我得到"无效列错误"。但是你的回答帮助我解决了这个问题。这是目前的工作情况，如果你有更好的建议，请告诉我。

WITH OnlyUnchecked AS(<MyQuery>)
SELECT * 
FROM OnlyUnchecked 
WHERE [Unchecked Count]>0