按自定义排序 - SQL对ORDER按值分组 [英] ORDER grouped values by custom rank - SQL

问题描述

我一直在尝试订购包含在我的表中的对象的ID。为此，我使用以下层次结构，它基于'IN'子句中属性的顺序：

ORDER BY

第一：与所有属性匹配的ID：'Solid'和'Metal'和'Red'

第二个：与匹配属性的ID：'Solid'和'Metal'

$ b

第三：属性：'Solid'

第四：与属性匹配的ID：'Metal'和'Red'

第五： ID仅与属性匹配：'Metal'

第六： ID的匹配只是属性：'红'

我使用的主要查询是：

  SELECT objectattributelink.ID，COUNT（*）属性
 FROM objectattributelink，属性列表
 WHERE objectattributelink.ID = attributelist.AttributeID 
 AND属性列表.AttributeDesc IN（'Solid'，'Metal'，'Red'）
 AND objectattributelink.AttributeID BETWEEN 1000和1200 
 GROUP BY objectattributelink.ID 
 ORDER BY COUNT（*）DESC 
  

它以简单的顺序返回所有的ID。在这种情况下，顶部的（匹配3个属性）是可以的，但其余的顺序是随机的。

我目前的解决方案运行6个不同的查询，但我想知道它是否可以在一个完成。

我基本上为每个查询做的是：

其中一个带有：IN（'Solid'，'Metal'，'Red'）和HAVING Attributes = 3，第二个带有：IN（'Solid'，'Metal'）和HAVING Attributes = 2，第三个与：IN（'Solid'），第四个与：IN（'金属'，'红'）和HAVING属性= 2，第五个与：IN（'金属'）和第六个与IN（'红色'）

解决方案

以下查询执行下列操作： code> SELECT oal.ID，COUNT（*）属性
FROM objectattributelink oal join
（选择al。*，
（当AttributeDesc ='Solid'的情况下，则1 else 0 end ）为IsSolid，
（当AttributeDesc ='Metal'时，则为1 e lse 0 end）作为IsMetal，
（当AttributeDesc ='Red'，然后1 else 0结束时）作为IsRed
从属性列表al
）al
在oal.ID = al .AttributeID
其中al.AttributeDesc IN（'Solid'，'Metal'，'Red'）
AND oal.AttributeID BETWEEN 1000和1200
GROUP BY oal.ID
ORDER当max（IsSolid）= 1且max（IsMetal）= 1时，当max（IsSolid）= 1且max（IsMetal）= 1且max（IsRed）= 1时，则为1
，则2
当max（IsMetal）= 1时Max（IsSolid）= 1，则3
，max（IsMetal）= 1且max（IsRed）= 1时为3
，则max（IsMetal）= 1时为4
， ）= 1，然后6
结束）;

为了便于编写逻辑，查询为每个值引入了三个新变量。然后， max（）函数确定组是否具有这些变量。

我还修复了要使用的连接语法在子句上，并引入表别名以提高可读性。

编辑：

我想你也可以用来完成命令：



  order by max（IsSolid）* 4 + max（IsMetal）* 2 + max（IsRed）; 
  




I've been trying to order the ID's of the Objects contained in my Table. For this, I'm using the following hierarchy, which is based on the order of attributes within the 'IN' clause:

ORDER BY

First: ID's that match ALL the attributes: 'Solid' and 'Metal' and 'Red'

Second: ID's that match the attributes: 'Solid' and 'Metal'

Third: ID's that match just the attribute: 'Solid'

Fourth: ID's that match attributes: 'Metal' and 'Red'

Fifth: ID's that match just the attribute: 'Metal'

Sixth: ID's that match just the attribute: 'Red'

The main query I'm using is:

SELECT objectattributelink.ID, COUNT(*) Attributes
FROM objectattributelink, attributelist
WHERE objectattributelink.ID = attributelist.AttributeID
AND attributelist.AttributeDesc IN ('Solid', 'Metal', 'Red')
AND objectattributelink.AttributeID BETWEEN 1000 AND 1200
GROUP BY objectattributelink.ID
ORDER BY COUNT(*) DESC

Which returns all the ID's in a simplified order. In this case, the ones on the top (matching 3 attributes) would be ok, but for the rest the order is random.

My current solution is running 6 different queries, but I want to know if it can be done in one.

What I'm basically doing for each query is:

One with: IN ('Solid', 'Metal', 'Red') and HAVING Attributes = 3, Second one with: IN ('Solid', 'Metal') and HAVING Attributes = 2, Third one with: IN ('Solid'), Fourth one with: IN ('Metal', 'Red') and HAVING Attributes = 2, Fifth one with: IN ('Metal') and Sixth one with IN ('Red')

解决方案

The following query does the ordering:

SELECT oal.ID, COUNT(*) Attributes
FROM objectattributelink oal join
     (select al.*,
             (case when AttributeDesc = 'Solid' then 1 else 0 end) as IsSolid,
             (case when AttributeDesc = 'Metal' then 1 else 0 end) as IsMetal,
             (case when AttributeDesc = 'Red' then 1 else 0 end) as IsRed
      from attributelist al
     ) al
     on oal.ID = al.AttributeID
where al.AttributeDesc IN ('Solid', 'Metal', 'Red')
AND oal.AttributeID BETWEEN 1000 AND 1200
GROUP BY oal.ID
ORDER BY (case when max(IsSolid) = 1 and max(IsMetal) = 1 and max(IsRed) = 1 then 1
               when max(IsSolid) = 1 and max(IsMetal) = 1 then 2
               when max(IsSolid) = 1 then 3
               when max(IsMetal) = 1 and max(IsRed) = 1 then 4
               when max(IsMetal) = 1 then 5
               when max(IsRed) = 1 then 6
          end);

To facilitate writing the logic the query introduces three new variables for each value. The max() function then determines if the group has those variables.

I also fixed the join syntax to use the on clause and introduced table aliases for readability.

EDIT:

I think you can also do the order by using:

order by max(IsSolid) * 4 + max(IsMetal) * 2 + max(IsRed);

这篇关于按自定义排序 - SQL对ORDER按值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！