按照字母数字字符串的相似性排序MS SQL Server 2012
问题描述:
我已经继承了使用PHP脚本访问SQL Server 2012数据库的项目。该应用程序允许用户输入几个参数并根据这些参数提出请求。按照字母数字字符串的相似性排序MS SQL Server 2012
我遇到问题的部分是允许用户使用以字母数字字符串开头的SKU搜索项目的字段。
这是已使用的项目查询:
SELECT top 10 IB.LocalSKU
, Cast(Round(IB.GoalMargin, 2) AS Decimal(10,2)) AS GoalMargin
, CASE WHEN IB.MAP = 0 THEN NULL ELSE IB.MAP END AS Min
, IB.ProductCost
, IB.ShippingEstimate
, EB.Price AS CurrentPrice
FROM intra.InventoryBase IB INNER JOIN intra.DropshipChannelAdvisorSKUs CA
ON IB.LocalSKU = CA.LocalSKU
LEFT JOIN intra.eBayQoHFeedback EB
ON CA.ChannelAdvisorSKU = EB.SKU
WHERE LOWER(IB.LocalSKU) LIKE LOWER('$localSKU%')
AND LOWER(SupplierID) LIKE LOWER('%$supplierId%')
AND LOWER(IB.Category) LIKE LOWER('%$category%')
AND LOWER(IB.Dropship) LIKE LOWER('%$dropship%');
我注意到,该查询返回正确的信息,而不是在一个有用的顺序。
我不知道如何对它排序,以便SKU最接近匹配$ localSKU变量的行排在第一位。
我尝试这一点,但它没有我希望的效果:
ORDER BY Difference(IB.LocalSKU, '$localSKU%') ASC
我也做模糊匹配的字符串一些阅读,但我不知道如何在这里实现它。
是否有一个有效的办法:
- 搜索开始给定的字符串基于该字符串的亲近
- 秩序的结果,然后由值
预期结果为varchar :
参数:
$localSKU = "FMCPL1CY00";
$supplierId = 87;
$category = "Premium Floor Liners";
$dropship = True;
个
预期结果:
--------------------------------------------------------------------------------
| FMCPL1CY00* | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPL1CY01* | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPL1CY02* | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPL1CY03* | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
--------------------------------------------------------------------------------
实际结果:
-------------------------------------------------------------------------------------
| FMCPL1CH04221509 | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPM1SA0021302 | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPL1TY07801509 | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
| FMCPL1TY05721502 | GoalMargin | 0 | ProductCost | ShippingEstimate | CurrentPrice |
-------------------------------------------------------------------------------------
答
尝试此查询:
SELECT top 10 IB.LocalSKU
, Cast(Round(IB.GoalMargin, 2) AS Decimal(10,2)) AS GoalMargin
, CASE WHEN IB.MAP = 0 THEN NULL ELSE IB.MAP END AS Min
, IB.ProductCost
, IB.ShippingEstimate
, EB.Price AS CurrentPrice
,CASE WHEN IB.LocalSKU LIKE '$localSKU%' THEN 0 else 1 END as MyOrder
FROM intra.InventoryBase IB INNER JOIN intra.DropshipChannelAdvisorSKUs CA
ON IB.LocalSKU = CA.LocalSKU
LEFT JOIN intra.eBayQoHFeedback EB
ON CA.ChannelAdvisorSKU = EB.SKU
WHERE LOWER(IB.LocalSKU) LIKE LOWER('$localSKU%')
AND LOWER(SupplierID) LIKE LOWER('%$supplierId%')
AND LOWER(IB.Category) LIKE LOWER('%$category%')
AND LOWER(IB.Dropship) LIKE LOWER('%$dropship%')
ORDER BY MyOrder ASC;
我希望它能帮助。
+0
这给我一个很好的结果,稍作调整: ORDER BY MyOrder ASC,IB.LocalSKU ASC; –
+0
我很高兴它工作:) –
能否请您展示预期的结果和实际结果 – TheGameiswar
另外,您能提供一些'$ localSKU'的例子吗? – scsimon
@TheGameiswar我为你添加了那些,让我知道如果我可以进一步澄清 –