如何根据“案例陈述”中的值显示行

编解码器

我有一个查询，如下所示，并且仅当使用CASE的值是1时才要显示一行。请您告诉我我该怎么做

SELECT DISTINCT 
    a.AccountID, 
    a.ForeName, 
    a.Surname, 
    a.Gender, 
    CASE 
        WHEN B.Value = '1145' THEN '1' 
        WHEN B.Value = '1007' THEN '2' ELSE '0' 
    END AS Value, 
    b.Address,
     b.Town
FROM 
    Customer a
LEFT OUTER JOIN 
    AdditionalDetails b
ON 
    b.ID = a.AccountID

我得到的结果是：

 AccountID  ForeName  Surname  Gender NoName  Address Town
  00012       Eric      Manse   Male    0      Porto   Porto
  00013       Peter     Mark    Male    0      Porto   Porto
  00014       Tom       Jerry   Male    0      Porto   Porto
  00014       Tom       Jerry   Male    1      Porto   Porto
  00015       Sarah     Parker  Female  0      Porto   Porto
  00015       Sarah     Parker  Female  1      Porto   Porto

如果CASE语句中有1，则不应仅将值为1的行显示0。

戈登·利诺夫

我推测您想要MAX()或MIN()：

SELECT c.AccountID, c.ForeName, c.Surname, c.Gender, 
       MAX(CASE WHEN ad.Value = '1145' THEN '1'
                WHEN ad.Value = '1007' THEN '2'
                ELSE '0' 
           END),
      ad.Address, ad.Town
FROM Customer c LEFT OUTER JOIN
     AdditionalDetails ad
     ON c.ID = ad.ID
GROUP BY c.AccountID, c.ForeName, c.Surname, c.Gender, ad.Address, ad.Town;

编辑：

您似乎想要确定优先级：

SELECT cad.*
FROM (SELECT c.AccountID, c.ForeName, c.Surname, c.Gender, 
             ad.Address, ad.Town,
             ROW_NUMBER() OVER (PARTITION BY c.ACCOUNTID
                                ORDER BY (CASE WHEN ad.Value = '1145' THEN 1
                                               WHEN ad.Value = '1007' THEN 2
                                               ELSE 0'
                                          END) DESC
                               ) as seqnum
      FROM Customer c LEFT OUTER JOIN
           AdditionalDetails ad
           ON c.ID = ad.ID
     ) cad
WHERE seqnum = 1;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。