为多个列选择不同的值 [英] Selecting distinct values for multiple columns

问题描述

我有一个表，其中许多数据与另一棵树中的另一列相匹配，类似于一棵树，然后在叶子中有关于每个特定叶子的数据

I have a table where many pieces of data match to one in another column, similar to a tree, and then data at the 'leaf' about each specific leaf

eg

Food Group      Name       Caloric Value  
Vegetables      Broccoli   100  
Vegetables      Carrots    80    
Fruits          Apples     120  
Fruits          Bananas    120  
Fruits          Oranges    90

我要设计查询将仅返回每一列的不同值，然后为null以覆盖溢出

I would like to design a query that will return only the distinct values of each column, and then nulls to cover the overflow

例如

Food group    Name     Caloric Value  
Vegetables    Broccoli 100  
Fruit         Carrots  80  
              Apples   120  
              Bananas  90  
              Oranges   

我不确定是否可行，现在我一直在尝试处理案件，但是我希望会有一个更简单的方式

I'm not sure if this is possible, right now I've been trying to do it with cases, however I was hoping there would be a simpler way

推荐答案

似乎您只是在尝试掌握所有不同的值。为什么？出于显示目的？这是应用程序的工作，而不是服务器的工作。您可以简单地进行三个查询，如下所示：

Seems like you are simply trying to have all the distinct values at hand. Why? For displaying purposes? It's the application's job, not the server's. You could simply have three queries like this:

SELECT DISTINCT [Food Group] FROM atable;

SELECT DISTINCT Name FROM atable;

SELECT DISTINCT [Caloric Value] FROM atable;

并相应显示其结果。

但是，如果您坚持要将它们全部放在一个表中，则可以尝试以下操作：

But if you insist on having them all in one table, you might try this:

WITH atable ([Food Group], Name, [Caloric Value]) AS (
  SELECT 'Vegetables', 'Broccoli', 100  UNION ALL
  SELECT 'Vegetables', 'Carrots',  80   UNION ALL
  SELECT 'Fruits',     'Apples',   120  UNION ALL
  SELECT 'Fruits',     'Bananas',  120  UNION ALL
  SELECT 'Fruits',     'Oranges',  90   
),
atable_numbered AS (
  SELECT
    [Food Group], Name, [Caloric Value],
    fg_rank = DENSE_RANK() OVER (ORDER BY [Food Group]),
    n_rank  = DENSE_RANK() OVER (ORDER BY Name),
    cv_rank = DENSE_RANK() OVER (ORDER BY [Caloric Value])
  FROM atable
)
SELECT
  fg.[Food Group],
  n.Name,
  cv.[Caloric Value]
FROM (
  SELECT fg_rank FROM atable_numbered  UNION
  SELECT n_rank  FROM atable_numbered  UNION
  SELECT cv_rank FROM atable_numbered
) r (rank)
  LEFT JOIN (
    SELECT DISTINCT [Food Group], fg_rank
    FROM atable_numbered) fg ON r.rank = fg.fg_rank
  LEFT JOIN (
    SELECT DISTINCT Name, n_rank
    FROM atable_numbered) n  ON r.rank = n.n_rank
  LEFT JOIN (
    SELECT DISTINCT [Caloric Value], cv_rank
    FROM atable_numbered) cv ON r.rank = cv.cv_rank
ORDER BY r.rank

这篇关于为多个列选择不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！