在SQL中自定义订单 [英] CUSTOMIZE ORDERING IN SQL

问题描述

  SELECT 类别名称
 FROM 类别位置 CatId  IN ( 55 ， 568 ， 5412 ， 8991 ， 928 ， 3213 ， 9250 ， 2983 )

我希望以与(55,568,5412,8991,928,3213,142,9250,12330,2983)相同的顺序显示记录，但是我得到了一些随机的顺序.方案

我明白了

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)
order by case GroupId 
    when 7 then 1 -- First in ordering
    when 14 then 2 -- Second
    else 3
end

这可以做到

  SELECT 类别名称
 FROM 类别
加入(
        选择  55  > AS  CatId  UNION 
        选择  568  > UNION 
        选择  5412  > UNION 
        选择  8991   UNION 
        选择  928  > UNION 
        选择  3213  > UNION 
        选择  142  > UNION 
        选择  9250  > UNION 
        选择  12330  > UNION 
        选择  2983 
        ) AS  CatFilter
        打开 CatFilter.CatId = Category.CatId
订购
 BY  CatFilter.CatId 

我认为这不可能.

如果您的表包含主键，则
SQL获取记录并根据主键列对记录进行排序

如果您的表不包含主键，那么
Sql提取记录并基于所提取记录中的第一列对记录进行排序.

SELECT CategoryName
FROM Category WHERE CatId IN (55,568,5412,8991,928,3213,142,9250,12330,2983)

I would like the records to be shown in the same order as (55,568,5412,8991,928,3213,142,9250,12330,2983), but I am getting some random order.

解决方案

i got it

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)
order by case GroupId 
    when 7 then 1 -- First in ordering
    when 14 then 2 -- Second
    else 3
end

This would do it

SELECT  CategoryName
FROM    Category
JOIN    (
        SELECT 55 AS CatId UNION
        SELECT 568 UNION
        SELECT 5412 UNION
        SELECT 8991 UNION
        SELECT 928 UNION
        SELECT 3213 UNION
        SELECT 142 UNION
        SELECT 9250 UNION
        SELECT 12330 UNION
        SELECT 2983
        ) AS CatFilter
        ON CatFilter.CatId = Category.CatId
ORDER
BY      CatFilter.CatId

I dont think its possible.

If your table contains primary key then
Sql fetches the records and sort the records based on primarykey column

If your table does not contain primary key then
Sql fetches the records and sort the records based on first column in fetched records.

这篇关于在SQL中自定义订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！