按自定义排序

编程入门 行业动态 更新时间:2024-10-28 20:23:17
本文介绍了按自定义排序 - SQL对ORDER按值分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

我一直在尝试订购包含在我的表中的对象的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);

更多推荐

按自定义排序

本文发布于:2023-10-15 01:34:24,感谢您对本站的认可!
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:自定义

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!