[骚年,又来了- 多条件排序有关问题]_Sql Server

[骚年,又来了--- 多条件排序问题]
首先说下，有三张表分别为：
tEBay_Trade(订单表)
tEBay_TradeProduct(订单商品表)
SKU_PRODUCT_MTM(商品SKU组合表)

tEBay_Trade.Oid与tEBay_TradeProduct.Oid 关联
tEBay_TradeProduct.PRO_CODE与 SKU_PRODUCT_MTM.PRO_CODE 关联

实现功能（按条件排序订单）：
先根据订单表的County,AreaCode,City 排序，
相同AreaCode的再按订单表通过Oid与商品表关联，商品表再与商品SKU组合表通过PRO_CODE关联的SKU_PRODUCT_MTM.SKU_CODE 排序

其中SKU 排序要先比对字母部分，再比对数字部分
比如： SKU100,SKU2 其中SKU2要排前面

下面SQL数据，最终订单排序的结果是：
订单
456791011
12345678
345678910
23456789

SQL code

CREATE TABLE #tEBay_Trade  --订单表(  ID INT,   --ID  Oid VARCHAR(20),  --订单编号  County VARCHAR(20), --国家  AreaCode VARCHAR(20),--地区代码  City VARCHAR(20),    --城市)INSERT INTO #tEBay_Trade SELECT 1,'12345678','United States','A001','Glenn Heights'INSERT INTO #tEBay_Trade SELECT 2,'23456789','United States','A002','Renton'INSERT INTO #tEBay_Trade SELECT 3,'345678910','United States','A002','Renton'INSERT INTO #tEBay_Trade SELECT 4,'456791011','Australia','C001','Burnie'CREATE TABLE #tEBay_TradeProduct   --订单商品表(  ID INT,   --ID  OID VARCHAR(20), --订单编号  PRO_CODE VARCHAR(20),    --产品编码)INSERT INTO #tEBay_TradeProduct SELECT 1,'12345678','ME0153'INSERT INTO #tEBay_TradeProduct SELECT 2,'23456789','ME0154'INSERT INTO #tEBay_TradeProduct SELECT 3,'345678910','ME0155'INSERT INTO #tEBay_TradeProduct SELECT 4,'456791011','ME0156'CREATE TABLE #SKU_PRODUCT_MTM    --商品SKU组合表(  PRO_CODE VARCHAR(20), --产品编号  SKU_CODE VARCHAR(20)    --SKU编号)

------解决方案--------------------

SQL code

SELECT  * FROM #tEBay_Trade a  ,#tEBay_TradeProduct b WHERE a.Oid=b.Oid ORDER BY County,AreaCode,City,PRO_CODE DESC
------解决方案--------------------
SQL codeselect a.County,a.AreaCode,a.City,b.OID,c.* from #tEBay_Trade ajoin #tEBay_TradeProduct b on a.Oid=b.OIDleft join #SKU_PRODUCT_MTM c on c.PRO_CODE=b.PRO_CODEorder by a.County,a.AreaCode,a.City,b.PRO_CODE desc/*County               AreaCode             City                 OID                  PRO_CODE             SKU_CODE-------------------- -------------------- -------------------- -------------------- -------------------- ----------Australia            C001                 Burnie               456791011            NULL                 NULLUnited States        A001                 Glenn Heights        12345678             NULL                 NULLUnited States        A002                 Renton               345678910            NULL                 NULLUnited States        A002                 Renton               23456789             NULL                 NULL*/--你的商品SKU组合表#SKU_PRODUCT_MTM没有数据,所以,如果根据SKU_PRODUCT_MTM.SKU_CODE来排序可能得不到你要的结果--上面的查询结果中最后排序是根据#tEBay_TradeProduct表的PRO_CODE字段来排序
------解决方案--------------------
探讨

SQL code
select a.OID,sku_code,substring(sku_code,1,3) as sku1,SUBSTRING(sku_code,4,LEN(sku_code)-4) as sku2 from #tEBay_Trade a join #tEBay_TradeProduct b on a.Oid=b.OID left join #SKU_PRODUCT_MTM ……

------解决方案--------------------
SQL codeselect a.County,a.AreaCode,a.City,b.OID,c.* from #tEBay_Trade ajoin #tEBay_TradeProduct b on a.Oid=b.OIDleft join #SKU_PRODUCT_MTM c on c.PRO_CODE=b.PRO_CODEorder by a.County,a.AreaCode,a.City,cast(c.SKU_CODE as binary(20)) desc--改进：将SKU_CODE字段转换为二进制后在按倒序排序,这样的排序比较准确/*County               AreaCode             City                 OID                  PRO_CODE             SKU_CODE-------------------- -------------------- -------------------- -------------------- -------------------- --------------------Australia            C001                 Burnie               456791011            ME0156               SKU099United States        A001                 Glenn Heights        12345678             ME0153               SKU2United States        A002                 Renton               23456789             ME0154               SKU099(WHITE)United States        A002                 Renton               345678910            ME0155               SKU099(BLACK)United States        A002                 Renton               345678910            ME0155               SKU099United States        A002                 Renton               23456789             ME0154               SKU099*/