如何使用分组行排序SQL查询

I have the table (Product_Id, category priority, atribute1, atribute2...) in MS Access, and I am trying to make a query that orders the data grouped by Category and ordered by the highest priority. Priority can be Null, so it should be placed at the end. Example: Table

我在MS Access中有表(Product_Id,category priority,atribute1,atribute2 ...),我正在尝试进行查询,按类别对数据进行排序并按最高优先级排序。优先级可以为空,因此应该放在最后。示例:表格

1, 100, 2, atr1, atr2
2, 300,  , atr1, atr2
3, 100, 5, atr1, atr2
4, 200, 9, atr1, atr2
5, 100,  , atr1, atr2
6, 200, 1, atr1, atr2

Result expected in Query:

查询中预期的结果:

6, 200, 1, atr1, atr2
4, 200, 9, atr1, atr2
1, 100, 2, atr1, atr2
3, 100, 5, atr1, atr2
5, 100,  , atr1, atr2
2, 300,  , atr1, atr2

5 个解决方案

#1

In Jet SQL, this may suit:

在Jet SQL中,这可能适合:

SELECT t2.MinOfPriority, tn.Field2, Nz([tn.Field3],999) AS Priority, 
       tn.Field4, tn.Field5
FROM tn 
INNER JOIN (SELECT Min(Nz(tn.Field3,999)) AS MinOfPriority, tn.Field2
            FROM  tn GROUP BY tn.Field2) AS t2 ON tn.Field2 = t2.Field2
ORDER BY t2.MinOfPriority, tn.Field2, Nz([Field3],999);

#2

The easiest solution (not necessarily the best in some cases) is to use column numbers in your ordering expressions:

最简单的解决方案(在某些情况下不一定是最好的)是在排序表达式中使用列号:

SELECT t2.MinOfPriority, 
       tn.Field2, 
       Nz([tn.Field3],999) AS Priority,        
       tn.Field4, 
       tn.Field5

ORDER BY 1,2,3

#3

You need to weight the categories (I'm weighting null with some suitably large value):

你需要对类别进行加权(我用一些适当大的值加权null):

select  t1.* 
from    myTable t1 
join 
( 

    select  category, min(coalesce(priority, 1000)) weight
    from    myTable 
    group by category
) t2 
on t1. category = t2. category
order by t2.weight, coalesce(t1. priority, 1000)

#4

As far as I know, Jet always sorts NULLs to the end of a resultset when an explicit ORDER BY is used.

据我所知,当使用显式ORDER BY时,Jet总是将NULL排序到结果集的末尾。

See: ADO Provider Properties and Settings

请参阅:ADO提供程序属性和设置

"NULL Collation Order: A Long value (read-only) that specifies where Null values are collated (sorted). For the Microsoft Jet provider, the value is always 4, which indicates that null values are sorted at the low end of the list."

“NULL归类顺序:长值(只读),指定Null值的整理(排序)。对于Microsoft Jet提供程序,值始终为4,表示空值在列表的低端排序“。

The fact the property NULL Collation Order is read-only for the provider strongly suggests the Jet engine has only one NULL collation and, happily for you, it's the one you desire.

属性NULL Collation Order对提供程序是只读的这一事实强烈建议Jet引擎只有一个NULL归类,并且很高兴为您,它是您想要的。

#5

Apparently NZ(Value, ValueToReturnIfNull) can be used on MSAccess as a substitute for ISNULL so ...

显然,NZ(Value,ValueToReturnIfNull)可以在MSAccess上用作ISNULL的替代品,所以......

SELECT a.*
FROM this_table AS a 
    INNER JOIN 
    (
        SELECT category,min(NZ(priority,999999)) as min_priority_in_cat 
        FROM this_table group by category
    ) AS b ON a.category = b.category
ORDER BY  b.min_priority_in_cat, a.category, NZ(a.priority,999999)

#1