代码之家 › 专栏 › 技术社区 › Subhasish1315

Case语句变量声明及其在mssqlserver中的应用

database-administration database sql-server mysql

Subhasish1315 · 技术社区 · 6 年前

我正在使用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中使用它。

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

因此,所有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))

结果是

1 回复 | 直到 4 年前

Panagiotis Kanavos 6 年前

看起来MySQL查询正在尝试模拟 LEAD() LAG() MySQL 8中引入的分析函数。自2012年以来,SQL Server就已经提供了这些功能(我想)。

MySQL查询假设结果将按特定顺序返回,即使没有orderby子句。它还假设没有并行处理,至少在处理变量时是这样。

CASE 可以重写为:

count(distinct serial_number) - 
LAG(count(distinct serial_number),1,0) OVER (
                        PARTITION BY product_group,month 
                        ORDER BY pass)

这使我们分道扬镳 GROUP BY product_group,month 然后命令他们 pass . LAG 然后返回在该分区中计数,如果没有上一行,则为0。这意味着将为返回0 N N代表Y

select 
    year(date_of_test),
    month(date_of_test),
    product_group,
    pass,
    count(distinct serial_number) - 
    LAG( COUNT(distinct serial_number),1,0) 
         OVER ( PARTITION BY product_group,month(date_of_test) 
                ORDER BY pass)
from test_data
where 
    year(date_of_test)=2018 
    and month(date_of_test) between 3 and 4
    and product_group='BHO'
group by 
    year(date_of_test),
    month(date_of_test),
    product_group,
    pass

许多 a Calendar table . 日历表是一个预先填充了20年的日期的表,其中包含额外的字段,如月、月名、周数、工作或假日等。这使得编写基于日期的查询变得更容易,生成的查询也更快。

calendar

select 
    calendar.month_name + '-' + calendar.year,
    product_group,
    pass,
    count(distinct serial_number) - 
    LAG( COUNT(distinct serial_number),1,0) 
         OVER ( PARTITION BY product_group,calendar.month 
                ORDER BY pass)
from 
    test_data
    inner join calendar on date_of_test=date
where 
    calendar.year =2018 
    and calendar.month between 3 and 4
    and product_group='BHO'
group by 
    calendar.year,
    calendar.month,
    product_group,
    pass

此查询可以利用 date_of_test , calendar.date , calendar.year calendar.month 列以查找结果。