SQL Server 使用案例条件的SQL代码无法运行

rxztt3cl 于 2022-11-21 发布在其他

关注(0)|答案(1)|浏览(131)

我使用的是SQL Server Management Studio。哪里出错了？
下面是我想做的-我想得到一个值：

如果exstart_start和dateord之间的持续时间（以月为单位）〈= 0，则
（从exstart_start到datecoms的持续时间减去24个月，即仅当解决方案〉=0
（如果不满足上述条件，则从exstart_start到datecoms的持续时间减去12个月，即仅当解决方案〉= 0时
如果都不满意，则将其设置为0
如果exstart_start和dateord之间的月持续时间〉0，则使其等于exstart_start和dateord之间的月持续时间

我在CASE上的尝试：

CASE 
    WHEN DATEDIFF (m, exstart_start, dateord) <= 0
        THEN
            (CASE 
                 WHEN ((DATEDIFF (m, exstart_start, datecoms)) - 24 <= 0) 
                     THEN 0 
                     ELSE (DATEDIFF (m, exstart_start, datecoms)) - 24 
             END)
        ELSE
            ((CASE 
                  WHEN DATEDIFF (m, exstart_start, datecoms)) - 12 <= 0) 
                      THEN 0 
                      ELSE (DATEDIFF (m, exstart_start, datecoms)) - 12) 
              END) 
       END)

ELSE 
    DATEDIFF (m, exstart_start, dateord) END AS diffexstarttoordered

sql-server

来源：https://stackoverflow.com/questions/74487918/sql-code-using-case-criteria-however-unable-to-run

1条答案

按热度按时间

hmae6n7t1#

对于初学者，我认为有1个END太多或1个CASE太少，因为1个CASE有4个END和2个ELSE：

CASE --1.
WHEN DATEDIFF (m, exstart_start, dateord) <= 0
    THEN
-- ? here might be missing a CASE
        (CASE --2.
             WHEN ((DATEDIFF (m, exstart_start, datecoms)) - 24 <= 0) 
                 THEN 0 
                 ELSE (DATEDIFF (m, exstart_start, datecoms)) - 24 
         END --2.)
    ELSE --(1 else from first case)
        ((CASE --3.
              WHEN DATEDIFF (m, exstart_start, datecoms)) - 12 <= 0) 
                  THEN 0 
                  ELSE (DATEDIFF (m, exstart_start, datecoms)) - 12) 
          END--3.) 
   END --?)
ELSE(--? else from 1.CASE if 1 CASE is missing or too much)
  DATEDIFF (m, exstart_start, dateord) 
END --1.) 
AS diffexstarttoordered

经过一番思考，这可能是你想要的解决方案：

CASE WHEN DATEDIFF (m, exstart_start, dateord)<=0
 THEN
 (CASE WHEN ((DATEDIFF (m, exstart_start, datecoms)-24) >=0) 
                  THEN (DATEDIFF (m, exstart_start, datecoms)-24)
       WHEN ((DATEDIFF (m, exstart_start, datecoms)-12) >=0)
                  THEN (DATEDIFF (m, exstart_start, datecoms)-12)
       ELSE 0           
  END) 
ELSE DATEDIFF (m, exstart_start, dateord) 
END AS diffexstarttoordered

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 使用案例条件的SQL代码无法运行

1条答案

相关问题

热门标签

最新问答