独立地对列进行排序，以便所有null都在每列的最后 [英] Sort columns independently, such that all nulls are last per column

问题描述

这是一个名为animal的示例表:

Here is an example table called animal:

name | color
------------
fox  | brown
fox  | red
dog  | gold

现在，我想要的是这个结果:

Now, what I want is this result:

fox   | dog
-------------
brown | gold
red   | 

名称应该是结果的列，其颜色值与行不同.

The names should be columns of the result with the different color values as rows.

我的第一个想法是:

SELECT color
FROM animal
WHERE name='fox'

[some sort of join?]

SELECT color 
FROM animal
WHERE name='dog'

但是我不知道什么样的联接会达到目的.

But I don't know what kind of join would do the trick.

第二个想法:

SELECT CASE WHEN name = 'fox' THEN color ELSE NULL END AS fox,
CASE WHEN name = 'dog' THEN color ELSE NULL END AS dog
FROM animal

这将返回:

fox   | dog
-----------
red   | 
brown |
      | gold

我想将此表中的空值移到末尾. 我试图:

I would like to move the null values in this table to the end. I tried to:

ORDER BY CASE name
        WHEN 'fox' THEN fox
        WHEN 'dog' THEN dog
    END

但是我不确定这是否真的是我想要的，尽管我可以执行ORDER BY fox，但Postgres抱怨Fox不是专栏.

But I'm not sure if this is really what I want and Postgres is nagging that fox is not a column although I can do ORDER BY fox.

也许我的方法完全是胡说八道，还是有某种凝聚力魔术可以解决问题?

Maybe my approach is total nonsense or there is some kind of coalesce magic that can do the trick?

推荐答案

您似乎对表格(例如电子表格)中的表有自然"的印象，但事实并非如此.如果不使用ORDER BY，则将以任意的顺序返回行-对于尚未更新的小型表，该顺序通常与输入顺序相同).

You seem to be under the impression that there would be a "natural" order in a table (like in a spreadsheet), but there is not. Without ORDER BY, rows are returned in arbitrary order - which often happens to be identical to input order for small tables that have not been updated, yet).

WITH cte AS (
   SELECT row_number() OVER (PARTITION BY name ORDER BY color) AS rn, * 
   FROM   animal
   )
SELECT f.color AS fox, d.color AS dog
FROM        (SELECT rn, color FROM cte WHERE name = 'fox') f
FULL   JOIN (SELECT rn, color FROM cte WHERE name = 'dog') d USING (rn)
ORDER  BY rn;

要点

• 在 CTE .
• FULL [OUTER] JOIN 非常重要，因为狐狸和狗的行数不同.
• 颜色按字母顺序排序，NULL值自动排在最后，因为FULL [OUTER] JOIN在表末尾为缺失的行填充了它们.
• 这只会扫描表一次.

Major points

• Attach sequential numbers per color for each type of animal separately in a CTE.
• The FULL [OUTER] JOIN is crucial, since the number of rows for fox and dog differ.
• Colors are sorted alphabetically, NULL values are last automatically, since they are filled in for missing rows at the end of the table by the FULL [OUTER] JOIN.
• This only scans the table once.

您收到的错误消息的说明("fox"不是列):

Explanation for the error message you got ("fox" is not a column):

• PostgreSQL的位置计数条件
• GROUP BY + CASE语句
• PostgreSQL Where count condition
• GROUP BY + CASE statement

这篇关于独立地对列进行排序，以便所有null都在每列的最后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！