sql-server - SQL Server CASE 1 WHEN 1，WHERE 1=1 AND 1=1

Question

我从以前的应用程序中继承了以下查询。我也很难理解“Select”和“Where”子句中的“Case”。

SELECT J1.AC_CODE, J1.PERIOD, J1.JRNAL_NO, J1.DESCRIPTN, - J1.AMOUNT ,
                                                              J1.ANAL_T3,
                                                              CASE 1
                                                                  WHEN 1 THEN 'A'
                                                                  ELSE J1.ACCNT_CODE
                                                              END ,
                                                              J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE 1=1
  AND 1=1
  AND NOT ('A' LIKE '%Z%'
           AND J1.JRNAL_SRCE IN ('B/F',
                                 'CLRDN')
           AND J1.JRNAL_NO = 0)
  AND CASE 1
          WHEN 1 THEN 'A'
          ELSE J1.AC_CODE
      END ='A'
  AND J1.AC_CODE='156320'
  AND J1.PERIOD BETWEEN 2014001 AND 2014012
  AND J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8

我不确定我是否正确理解了以下条款:

第 1 条:

CASE 1
 WHEN 1 THEN 'A'
 ELSE J1.AC_CODE
END

I understood as: If column 1 is true, then choose literal A ortherwise choose J1.AC_CODE.

第二条:

WHERE 1=1
  AND 1=1
  AND NOT ('A' LIKE '%Z%'
           AND J1.JRNAL_SRCE IN ('B/F',
                                 'CLRDN')
           AND J1.JRNAL_NO = 0)
  AND CASE 1
          WHEN 1 THEN 'A'
          ELSE J1.AC_CODE
      END ='A'
  AND J1.AC_CODE='156320'
  AND J1.PERIOD BETWEEN 2014001 AND 2014012
  AND J1.ANAL_T3='ANAL001'

I'm totally lost with this "Where" clause.

你能帮忙解释这个查询并为整个查询写一个更好的版本吗？我在 SQL Server 2008 (R2) 上运行此查询

Answer 1

I understood as: If column 1 is true, then choose literal A ortherwise choose J1.AC_CODE.

不，它正在将值 1 与值 1 进行比较，如果为真，则返回一个 A，即course 始终为真，因此 case 语句将始终返回 A。

您的 where 子句根本不执行任何操作。

1=1
  AND 1=1

将始终为真，大小写将始终为真，并且 'A' LIKE '%Z%' 将始终为 false，这使得整个 AND NOT 'A' LIKE ' %Z%' .... 表达式始终为真。

您的查询的更简单版本如下所示。

SELECT J1.AC_CODE,
       J1.PERIOD,
       J1.JRNAL_NO,
       J1.DESCRIPTN, 
       - J1.AMOUNT,
       J1.ANAL_T3,
       'A',
       J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE J1.AC_CODE='156320' AND 
      J1.PERIOD BETWEEN 2014001 AND 2014012 AND
      J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8