按整数排序，底部有空白字段

We're rewriting our CMS at the moment and we want our clients to be able to re-order items in a table using a "position" field. So, if they mark an items as position 1 it goes at the top, then position 2 underneath it etc.

我们目前正在重写我们的CMS,我们希望我们的客户能够使用“位置”字段重新订购表格中的项目。因此,如果他们将项目标记为位置1,则将其标记为顶部,然后将位于其下方的位置2等。

The problem is that we don't want to them to have to fill in a position every time, only if they want to re-order something so the position field will often be blank. So you might have the following...

问题是我们不希望他们每次都必须填写一个位置,只要他们想重新订购某些东西,所以位置字段通常是空白的。所以你可能有以下......

Car - 1

车 - 1

Bike - 2

自行车 - 2

House

Computer

Dog

This causes a problem because if you use the following SQL...

这会导致问题,因为如果您使用以下SQL ...

SELECT ProductName FROM Products ORDER BY Position DESC;

All the blank ones go to the top, not the bottom.

所有空白的都是顶部,而不是底部。

Can they be put into the correct order using a SQL statement?

可以使用SQL语句将它们放入正确的顺序吗?

4 个解决方案

#1

SELECT ProductName
FROM Products
ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position

If you want to order by Position Descending while maintaining nulls at the bottom, this will do it:

如果您想按位置降序排序,同时在底部保持空值,则可以执行以下操作:

ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position desc

If you want consistent sorting (you might be paging), then add ProductName or ProductID on the end to break all the ties between unpositioned products.

如果您想要一致的排序(可能是分页),那么在末尾添加ProductName或ProductID以打破未定位产品之间的所有联系。

ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position,
  ProductID

#2

SELECT  ProductName
FROM    Products
ORDER BY
        COALESCE(position, (SELECT MAX(position) + 1 FROM Products))

Alternatively, you can use:

或者,您可以使用:

SELECT  *
FROM    (
        SELECT  ProductName
        FROM    Products
        WHERE   position IS NOT NULL
        ORDER BY
                position
        ) q
UNION ALL
SELECT  ProductName
FROM    Products
WHERE   position IS NULL

This will be more efficient, since the first subquery will use the index on position for ordering is there is one.

这将更有效,因为第一个子查询将使用位置上的索引进行排序是否有一个。

#3

Only use NULLs when necessary

仅在必要时使用NULL

you can easily eliminate the use of nulls, and the resulting sort problem, by defaulting "unset" rows to the integer max value:

通过将“未设置”行默认为整数最大值,您可以轻松地消除空值的使用以及由此产生的排序问题:

UPDATE Products SET Position=2147483647 WHERE Position IS NULL

更新产品SET位置= 2147483647 WHERE位置为空

set the column to NOT NULL, add a default value of 2147483647

将列设置为NOT NULL,添加默认值2147483647

use your ORDER BY as intended

按预期使用您的ORDER BY

in your app or in your load query, suppress the display of any value of 2147483647

在您的应用或加载查询中,禁止显示任何值2147483647

here is a sample load:

这是一个示例加载:

SELECT 
    ProductName
        ,CASE
             WHEN Position=2147483647 THEN NULL  --or cast this as a varchar and use 'N/A'
             ELSE Position
         END
    FROM ...

If you must use nulls, do it this way:

如果必须使用空值,请按以下方式执行:

SELECT
    ProductName
        ,Position
    FROM Test
    ORDER BY COALESCE(Position,2147483647)

#4

This should do the the job for you.

这应该为你完成这项工作。

CREATE TABLE [dbo].[Test](
    [Position] [int] NULL,
    [Title] [varchar](15) NULL
) 
GO

INSERT INTO Test Values(1, 'P1')
INSERT INTO Test Values(2, 'P2')
INSERT INTO Test Values(NULL, 'P3')
INSERT INTO Test Values(NULL, 'P4')
INSERT INTO Test Values(NULL, 'P5')
GO

SELECT Title + ' - ' +  CASE  
    WHEN POSITION IS NULL THEN ''
    ELSE CAST(Position AS CHAR(3))
    END
FROM Test
ORDER BY CASE WHEN Position is null THEN 1 ELSE 0 END

#1

SELECT ProductName
FROM Products
ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position