mysql - 从案例陈述中选择

Question

我在查询时遇到了困难，但我不知道问题出在哪里。我必须根据 APPROVAL_PERIOD 计算财政年度，但 APPROVAL_PERIOD 是在 CASE WHEN 语句中计算的。

CASE WHEN DD.STATUS_CODE ='Denied' THEN
           CASE WHEN  CHK.MAXTRAILDATE BETWEEN '2015-05-22 00:00:00.0000000' AND '2015-05-31 00:00:00.0000000' THEN '201506'  
                ELSE  
                (CONVERT(CHAR(4),CHK.MAXTRAILDATE, 120) +''+SUBSTRING(CONVERT(nvarchar(6),CHK.MAXTRAILDATE, 112),5,2)) 
           END   
       ELSE 
           CASE WHEN  DD.APPROVAL_DATE BETWEEN '2015-05-22 00:00:00.0000000' AND '2015-05-31 00:00:00.0000000' THEN '201506'  
                ELSE   
                (CONVERT(CHAR(4),DD.APPROVAL_DATE, 120) +''+SUBSTRING(CONVERT(nvarchar(6),DD.APPROVAL_DATE, 112),5,2)) 
           END 
 END AS APPROVAL_PERIOD,
  CASE 
 WHEN APPROVAL_DATE between '2004-10-01 00:00:00.000' and '2005-09-30 23:59:00.000' THEN 'FY05'
 WHEN APPROVAL_DATE between '2005-10-01 00:00:00.000' and '2006-09-30 23:59:00.000' THEN 'FY06'
 WHEN APPROVAL_DATE between '2006-10-01 00:00:00.000' and '2007-09-30 23:59:00.000' THEN 'FY07'
 WHEN APPROVAL_DATE between '2007-10-01 00:00:00.000' and '2008-09-28 23:59:00.000' THEN 'FY08'
 WHEN APPROVAL_DATE between '2008-09-29 00:00:00.000' and '2009-09-30 23:59:00.000' THEN 'FY09'
 WHEN APPROVAL_DATE between '2009-10-01 00:00:00.000' and '2010-09-30 23:59:59.000' THEN 'FY10'
 WHEN APPROVAL_DATE between '2010-10-01 00:00:00.000' and '2011-09-30 23:59:59.000' THEN 'FY11'
 WHEN APPROVAL_DATE between '2011-10-01 00:00:00.000' and '2012-09-30 23:59:59.000' THEN 'FY12'
 WHEN APPROVAL_DATE between '2012-10-01 00:00:00.000' and '2013-09-30 23:59:59.000' THEN 'FY13'
 WHEN APPROVAL_DATE between '2013-10-01 00:00:00.000' and '2014-09-30 23:59:59.000' THEN 'FY14'
 WHEN APPROVAL_DATE between '2014-10-01 00:00:00.000' and '2015-09-30 23:59:59.000' THEN 'FY15'
 WHEN APPROVAL_DATE between '2015-10-01 00:00:00.000' and '2016-09-30 23:59:59.000' THEN 'FY16'
 WHEN APPROVAL_DATE between '2016-10-01 00:00:00.000' and '2017-09-30 23:59:59.000' THEN 'FY17'
 WHEN APPROVAL_DATE between '2017-10-01 00:00:00.000' and '2018-09-30 23:59:59.000' THEN 'FY18'
 WHEN APPROVAL_DATE between '2018-10-01 00:00:00.000' and '2019-09-30 23:59:59.000' THEN 'FY19'
 WHEN APPROVAL_DATE between '2019-10-01 00:00:00.000' and '2020-09-30 23:59:59.000' THEN 'FY20'
 WHEN APPROVAL_DATE between '2020-10-01 00:00:00.000' and '2021-09-30 23:59:59.000' THEN 'FY21'
 WHEN APPROVAL_DATE between '2021-10-01 00:00:00.000' and '2022-09-30 23:59:59.000' THEN 'FY22'
 ELSE ' '
 END AS FY
FROM table

我根据APPROVAL DATE计算了财政年度，但我需要根据APPROVAL_PERIOD来计算。有什么办法可以做到吗？

Answer 1

只是一个如何在查询中使用动态 mysql 变量的示例:

http://sqlfiddle.com/#!9/9eecb7d/24641

SELECT
case when 1 then @var := 'one' else @var := 'two' end field1,
case when @var ='one' then 'two' else 'three' end field2

更新在你的情况下，它会是这样的

CASE WHEN DD.STATUS_CODE ='Denied' THEN
           CASE WHEN  CHK.MAXTRAILDATE BETWEEN '2015-05-22 00:00:00.0000000' AND '2015-05-31 00:00:00.0000000' THEN @var := '201506'  
                ELSE  
                @var := (CONVERT(CHAR(4),CHK.MAXTRAILDATE, 120) +''+SUBSTRING(CONVERT(nvarchar(6),CHK.MAXTRAILDATE, 112),5,2)) 
           END   
       ELSE 
           CASE WHEN  DD.APPROVAL_DATE BETWEEN '2015-05-22 00:00:00.0000000' AND '2015-05-31 00:00:00.0000000' THEN @var := '201506'  
                ELSE   
                @var := (CONVERT(CHAR(4),DD.APPROVAL_DATE, 120) +''+SUBSTRING(CONVERT(nvarchar(6),DD.APPROVAL_DATE, 112),5,2)) 
           END 
 END AS APPROVAL_PERIOD,
  CASE 
 WHEN @var between '2004-10-01 00:00:00.000' and '2005-09-30 23:59:00.000' THEN 'FY05'
 WHEN @var between '2005-10-01 00:00:00.000' and '2006-09-30 23:59:00.000' THEN 'FY06'
 WHEN @var between '2006-10-01 00:00:00.000' and '2007-09-30 23:59:00.000' THEN 'FY07'
 WHEN @var between '2007-10-01 00:00:00.000' and '2008-09-28 23:59:00.000' THEN 'FY08'
 WHEN @var between '2008-09-29 00:00:00.000' and '2009-09-30 23:59:00.000' THEN 'FY09'
 WHEN @var between '2009-10-01 00:00:00.000' and '2010-09-30 23:59:59.000' THEN 'FY10'
 WHEN @var between '2010-10-01 00:00:00.000' and '2011-09-30 23:59:59.000' THEN 'FY11'
 WHEN @var between '2011-10-01 00:00:00.000' and '2012-09-30 23:59:59.000' THEN 'FY12'
 WHEN @var between '2012-10-01 00:00:00.000' and '2013-09-30 23:59:59.000' THEN 'FY13'
 WHEN @var between '2013-10-01 00:00:00.000' and '2014-09-30 23:59:59.000' THEN 'FY14'
 WHEN @var between '2014-10-01 00:00:00.000' and '2015-09-30 23:59:59.000' THEN 'FY15'
 WHEN @var between '2015-10-01 00:00:00.000' and '2016-09-30 23:59:59.000' THEN 'FY16'
 WHEN @var between '2016-10-01 00:00:00.000' and '2017-09-30 23:59:59.000' THEN 'FY17'
 WHEN @var between '2017-10-01 00:00:00.000' and '2018-09-30 23:59:59.000' THEN 'FY18'
 WHEN @var between '2018-10-01 00:00:00.000' and '2019-09-30 23:59:59.000' THEN 'FY19'
 WHEN @var between '2019-10-01 00:00:00.000' and '2020-09-30 23:59:59.000' THEN 'FY20'
 WHEN @var between '2020-10-01 00:00:00.000' and '2021-09-30 23:59:59.000' THEN 'FY21'
 WHEN @var between '2021-10-01 00:00:00.000' and '2022-09-30 23:59:59.000' THEN 'FY22'
 ELSE ' '
 END AS FY
FROM table

如有任何疑问，非常欢迎您