sql - 避免在温暖的时候显示重复的Poptart香精

请查看以下选择语句：

  SELECT
    fc.flavorid,
    fc.flavorname,
    CASE
      WHEN sa.flavorid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsEaten
    CASE
      WHEN t.tempid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsWarm

  FROM [poptart] p

    LEFT JOIN [diet] d ON d.Active = 1
    LEFT JOIN [toaster] ts ON p.poptartid = ts.poptartid AND d.dietname = ts.dietname
    LEFT JOIN [flavor] fl ON fl.poptartid = ts.poptartid
    LEFT JOIN [flavorcolor] fc ON fc.flavorid = fl.flavorid

    LEFT OUTER JOIN [stomach] sa ON sa.flavorid = fc.flavorid

    LEFT JOIN [poptart] p2 ON ts.poptartid = p2.poptartid
    LEFT JOIN [temp] t ON t.tempid = p2.tempid AND t.Active = 1

  GROUP BY fc.flavorid,fc.flavorname,sa.flavorid,t.tempid
  ORDER By fc.flavorname

正如您在下面看到的那样，这对于“ berry”的每个“ IsWarm”值返回一个值。我只想在此列表中返回不同的风味名称，但是“ IsWarm”字段成为一个问题，因为它可以是“是”或“否”，并且我只想在“是”可用的情况下仅显示“是”，而不是“是”和“否”，因此不会拉取重复的风味名称，例如“ berry”：

    flavorid  |  flavorname  |  IsEaten  |  IsWarm
      123     |    berry     |    No     |   Yes
      123     |    berry     |    No     |   No
      234     |    fudge     |    Yes    |   No
      235     |    honey     |    No     |   No

我如何使该语句仅返回此..？

  flavorid  |  flavorname  |  IsEaten  |  IsWarm
    123     |    berry     |    No     |   Yes
    234     |    fudge     |    Yes    |   No
    235     |    honey     |    No     |   No

有什么建议？

最佳答案

试试这个：

;WITH FlavorsList
AS
(
    //Your query here
    SELECT
      fc.flavorid,
      fc.flavorname,
    CASE
      WHEN sa.flavorid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsEaten
    CASE
      WHEN t.tempid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsWarm

  FROM [poptart] p

    LEFT JOIN [diet] d ON d.Active = 1
    LEFT JOIN [toaster] ts ON p.poptartid = ts.poptartid
                           AND d.dietname = ts.dietname
    LEFT JOIN [flavor] fl ON fl.poptartid = ts.poptartid
    LEFT JOIN [flavorcolor] fc ON fc.flavorid = fl.flavorid

    LEFT OUTER JOIN [stomach] sa ON sa.flavorid = fc.flavorid

    LEFT JOIN [poptart] p2 ON ts.poptartid = p2.poptartid
    LEFT JOIN [temp] t ON t.tempid = p2.tempid AND t.Active = 1

  GROUP BY fc.flavorid,fc.flavorname,sa.flavorid,t.tempid
)
SELECT fl.flavorid, fl.flavorname, fl.IsEaten, Max(fl.IsWarm)
FROM FlavorsList fl
GROUP BY fl.flavorid, fl.flavorname, fl.IsEaten
ORDER BY fl.flavorname

我在这里所做的是用查询包装您的结果，该查询将对第一个查询GROUP的结果进行BY flavorid, flavorname, IsEated。使用MAX(IsWarm)作为聚合函数，将保持vlafors名称的IsWarm值DISTINCT。我使用CTE编写此查询只是出于简化和可读性的考虑，但您可以将其编写为嵌套查询。

关于sql - 避免在温暖的时候显示重复的Poptart香精，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10256486/