我正在使用mysql作为我闪亮的应用程序。我正在使用这个应用程序的查询，它运行完全正常。

Select
    concat(monthname(date_of_test), '-', year(date_of_test)) as 'Time',
    product_group AS 'ProductGroup',
    Pass,
    Case
        when pass='N' then @no:=count(distinct serial_number)
        when pass='Y' then count(distinct serial_number)-@no
    end as Count
from test_data 
where 
    year(date_of_test)=2018 
    and product_group='BHO'
    and month(date_of_test) between 3 and 4
group by
    product_group,
    month(date_of_test),
    pass

但我需要在mssqlserver中更改它。我尝试过声明为变量，并在SQLServer中使用它。
我在sql server中的试用：

declare @no int;
set @no = 0;
Select
    CONCAT(datename(MM, date_of_test), '-', DATENAME(YY,date_of_test)) as 'Time',
    product_group AS 'ProductGroup',
    Pass,
    case
        when pass ='N' then  @no = count(distinct serial_number)    
        when pass ='Y' then count(distinct serial_number)- @no  
    end as 'Count'
from test_data 
where
    year(date_of_test)=2018 
    and product_group='BHO'
    and month(date_of_test) between 3 and 5
group by
    product_group,
    CONCAT(datename(MM, date_of_test),
    '-',
    DATENAME(YY,date_of_test)),
    pass

不带变量的查询如下所示：

Select
    CONCAT(datename(MM, date_of_test), '-', DATENAME(YY,date_of_test)) as 'Time',
    product_group AS 'ProductGroup',
    Pass,
    case
        when pass ='N' then count(distinct serial_number)    
        when pass ='Y' then count(distinct serial_number)
    end as 'Count'
from test_data 
where 
    year(date_of_test)=2018 and product_group='BHO'
    and month(date_of_test) between 3 and 4
group by
    product_group,
    CONCAT(datename(MM, date_of_test),
    '-',
    DATENAME(YY,date_of_test)),
    pass

它产生以下输出：

所需的输出类似于mysql的输出。请看一下pass=y的位置，然后从中减去pass=n的值。

它显示了一个错误。
我最初的假设是：在mysql中，我可以初始化查询中的变量并在其中使用它，但在mssqlserver中，可能还有其他规则。我的语法或过程可能是错误的。

Select Count(distinct serial_number) from Test_Data where year(date_of_test)=2018 and product_group='BHO'and month(date_of_test)=4

503

Select Count(distinct serial_number) from Test_Data where year(date_of_test)=2018 and product_group='BHO' and PASS='Y' and month(date_of_test)=4

503

Select Count(distinct serial_number) from Test_Data where year(date_of_test)=2018 and product_group='BHO' and PASS='N'and month(date_of_test)=4

71
因此，所有503个产品（序列号）都进行了多次测试，并获得通过=y值，但71个产品通过了相同的测试，在某些情况下，它们失败了，在某些情况下，它被记为通过=n。因此，如果我能计算出（distinct serial\ U number with pass=y）-（distinct serial\ U number with pass=n），那么它将给出通过所有测试的产品数量。
我可以这样做，结果是：

Select CONCAT(datename(MM, date_of_test),'-',DATENAME(YY,date_of_test)) as 'Time',product_group AS 'ProductGroup',
                    (Count(Distinct case when PASS='Y' then serial_number end)-Count(Distinct case when PASS='N' then serial_number end)) 
                 as ' All Test Passed',
                 Count(Distinct case when PASS='N' then serial_number end) as 'Min 1 Test Failed'
               from test_data 
               where 
               year(date_of_test)=2018 
               and 
               month(date_of_test) between 3 and 4
               and product_group='BHO'
               group by product_group,CONCAT(datename(MM, date_of_test),'-',DATENAME(YY,date_of_test))

结果是