WHERE CASE返回多行

Question

我希望这个查询只返回一行。我把CASE语句的WHEN子句按照它们最可能出现的顺序放在那里，所以最可能的TRUE语句总是首先求值。然而，似乎每个WHEN语句都在查询结束之前被求值，尽管它发现了多个TRUE语句。

SELECT To_Char(T.PRFINISH, 'DD/MM/YY') FINISH,
       T.PRNAME  
FROM  PRTASK T
      LEFT OUTER JOIN INV_INVESTMENTS ON T.PRPROJECTID = INV_INVESTMENTS.ID
WHERE T.PRNAME = CASE 
                    WHEN (T.PRNAME = 'Concept Tech PEP Meeting Date' AND T.PRSTATUS != 2) THEN 'Concept Tech PEP Meeting Date'
                    WHEN (T.PRNAME = 'Concept BRU Meeting Date' AND T.PRSTATUS != 2) THEN 'Concept BRU Meeting Date'
                    WHEN (T.PRNAME = 'End of Concept Phase' AND T.PRSTATUS != 2) THEN 'End of Concept Phase'                                                                                                                 
                    WHEN (T.PRNAME = 'Evaluate Tech PEP Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate Tech PEP Meeting Date'
                    WHEN (T.PRNAME = 'Evaluate BRU Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate BRU Meeting Date'
                    WHEN (T.PRNAME = 'Evaluate TSC Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate TSC Meeting Date'
                    WHEN (T.PRNAME = 'End of Evaluate Phase' AND T.PRSTATUS != 2) THEN 'End of Evaluate Phase'                       
                    WHEN (T.PRNAME = 'End of Analyse Phase' AND T.PRSTATUS != 2) THEN 'End of Analyse Phase'
                    WHEN (T.PRNAME = 'End of Design Phase' AND T.PRSTATUS != 2) THEN 'End of Design Phase'
                    WHEN (T.PRNAME = 'End of Build Phase' AND T.PRSTATUS != 2) THEN 'End of Build Phase'
                    WHEN (T.PRNAME = 'End of Test Phase' AND T.PRSTATUS != 2) THEN 'End of Test Phase'
                    WHEN (T.PRNAME = 'In Service' AND T.PRSTATUS != 2) THEN 'In Service'                    
                    WHEN (T.PRNAME = 'End of Implement Phase' AND T.PRSTATUS != 2) THEN 'End of Implement Phase'  
                    WHEN (T.PRNAME = 'End of Closure Phase' AND T.PRSTATUS != 2) THEN 'End of Closure Phase'
                    ELSE 'In Service'
               END
          AND INV_INVESTMENTS.CODE = '007058'

返回不止一行，即使第一个when子句为真。我认为当他们发现一个TRUE语句时，CASE语句就结束了。

编辑:也许我没有解释好，让我们试试这个:

INV_INVESTMENTS表中的每个ID都是一个项目。每个项目在PRTASK表中都有一组任务。在这个PRTASK表中，我们将拥有任务的名称(PRNAME)、状态(PRSTATUS)和完成日期(PRFINISH)。任务列表，按照它们在项目中出现的顺序如下:

1。概念技术PEP会议日期
2。概念BRU会议日期
3。概念阶段结束
4。评估技术PEP会议日期
5。评估BRU会议日期
6。评估TSC会议日期

其余的任务按照我在CASE语句中列出的顺序排列。我希望这个查询返回的是列表中第一个任务的完成日期，PRSTATUS为0或1。

Answer 1

试试这个。您可以将已知的名称放入表中，然后在连接中使用它。这将使您的选择简化为以下内容:

SELECT TOP 1 To_Char(T.PRFINISH, 'DD/MM/YY') FINISH,
       T.PRNAME  
FROM  PRTASK T
      LEFT OUTER JOIN INV_INVESTMENTS ON T.PRPROJECTID = INV_INVESTMENTS.ID
      JOIN ( 
      SELECT 'Concept Tech PEP Meeting Date' as Name, 1 as [Order]
      UNION All
      SELECT '<your next PRNAME here', 2 as [Order]
      UNION ALL ...
      ) names on names.Name = T.PRNAME
where PRSTATUS IN (0,1) AND PRINV_INVESTMENTS.CODE = '007058'
ORDER BY names.Order

Answer 2

我仍然不明白你想做什么(没有给出样本数据/结果)，但我会像这样重组它:

SELECT To_Char(PRFINISH, 'DD/MM/YY') AS FINISH, PRNAME
FROM ( 
  SELECT MAX(PRFINISH),
         PRNAME  
  FROM  PRTASK AS T
  WHERE  (PRNAME = 'In Service' OR PRSTATUS != 2)
  AND    PRPROJECTID IN 
    (SELECT ID 
     FROM INV_INVESTMENTS
     WHERE CODE = '007058' )
  GROUP BY PRNAME
  ) AS TABLE1

注意，这是假设您在查询中列出了PRNAME的所有可能值。