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编号
)

SELECT  * FROM #tEBay_Trade a  ,#tEBay_TradeProduct b WHERE a.Oid=b.Oid 
ORDER BY County,AreaCode,City,PRO_CODE DESC

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

SQL code
select a.County,a.AreaCode,a.City,b.OID,c.* from #tEBay_Trade a
join #tEBay_TradeProduct b on a.Oid=b.OID
left join #SKU_PRODUCT_MTM c on c.PRO_CODE=b.PRO_CODE
order by a.County,a.AreaCode,a.City,b.PRO_CODE desc
/*
County               AreaCode             City                 OID                  PRO_CODE             SKU_CODE
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
Australia            C001                 Burnie               456791011            NULL                 NULL
United States        A001                 Glenn Heights        12345678             NULL                 NULL
United States        A002                 Renton               345678910            NULL                 NULL
United 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 code
select a.County,a.AreaCode,a.City,b.OID,c.* from #tEBay_Trade a
join #tEBay_TradeProduct b on a.Oid=b.OID
left join #SKU_PRODUCT_MTM c on c.PRO_CODE=b.PRO_CODE
order 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