求教一个sql语句,关于多字段排序的

[狠狠菂圉湢]

材料订单明细表A中,有四个字段id,供应商,时间,所属订单号,项目的要求是先判断'时间'最近的在最上面,然后将'时间'最近的该条记录的'供应商'汇总在一起(但是汇总起来又会破坏'时间'的降序排序),接着再对'所属订单号'进行升序排序。想要实现的效果如图显示的第三种，请问这种效果能实现出来吗？

[狠狠菂圉湢]

啊,上面的图中单号排错了,忽略吧..反正不需要看它- -

[alongkuku]

请自己整理下哦，时间紧迫。 SELECT 'A' ID ,'2014-11-11' STRDATE,'4' BILLNO INTO # UNION ALL SELECT 'A' ID ,'2014-11-10' STRDATE,'8' BILLNO UNION ALL SELECT 'C' ID ,'2014-11-09' STRDATE,'4' BILLNO UNION ALL SELECT 'C' ID ,'2014-11-09' STRDATE,'5' BILLNO UNION ALL SELECT 'A' ID ,'2014-11-08' STRDATE,'7' BILLNO UNION ALL SELECT 'B' ID ,'2014-11-07' STRDATE,'6' BILLNO SELECT * FROM # ORDER BY ID SELECT * FROM # ORDER BY STRDATE DESC SELECT * INTO #2 FROM # SELECT * INTO #1 FROM # WHERE 1=2 DECLARE @RowCount INT DECLARE @ID NVARCHAR(10) DECLARE @MaxDate NVARCHAR(10) SELECT @MaxDate=MAX(STRDATE) FROM #2 SELECT TOP 1 @ID=ID FROM #2 WHERE STRDATE=@MaxDate WHILE(ISNULL(@MaxDate,'')<>'') BEGIN INSERT INTO #1 SELECT * FROM #2 WHERE ID=@ID ORDER BY STRDATE DESC DELETE FROM #2 WHERE ID=@ID SELECT @MaxDate=MAX(STRDATE) FROM #2 SELECT TOP 1 @ID=ID FROM #2 WHERE STRDATE=@MaxDate END SELECT * FROM #1

[Tiger_Zhao]

WITH table1(id,dt) AS (
    SELECT 'A','2014-11-11' UNION ALL
    SELECT 'A','2014-11-10' UNION ALL
    SELECT 'C','2014-11-09' UNION ALL
    SELECT 'C','2014-12-09' UNION ALL
    SELECT 'A','2014-11-08' UNION ALL
    SELECT 'B','2014-11-07'
)
,o AS (
    SELECT id, MAX(dt) mdt
      FROM table1
  GROUP BY id
)
SELECT t.*
  FROM table1 t
  JOIN o
    ON o.id = t.id
ORDER BY o.mdt DESC, t.id, t.dt DESC

id   dt
---- ----------
C    2014-12-09
C    2014-11-09
A    2014-11-11
A    2014-11-10
A    2014-11-08
B    2014-11-07

[jph1122]

什么先分组在排序，完全误导人呢，直接排序就完成了 SELECT * FROM # ORDER BY ID,STRDATE DESC

[狠狠菂圉湢]

能用一句sql写出来吗?~ ~

[xxfvba]

--可能理解的不对 Order by 供应商,时间 desc,订单号 asc

[狠狠菂圉湢]

引用 4 楼 xxfvba 的回复:

--可能理解的不对 Order by 供应商,时间 desc,订单号 asc

哦哦,上面的图已经画出来这个写法是不行的..谢谢你了~

[hepe00]

SELECT 'A' ID ,'2014-11-11' STRDATE,'4' BILLNO INTO #t
UNION ALL
SELECT 'A' ID ,'2014-11-10' STRDATE,'8' BILLNO 
UNION ALL
SELECT 'C' ID ,'2014-11-09' STRDATE,'4' BILLNO 
UNION ALL
SELECT 'C' ID ,'2014-12-09' STRDATE,'5' BILLNO 
UNION ALL
SELECT 'A' ID ,'2014-11-08' STRDATE,'7' BILLNO 
UNION ALL
SELECT 'B' ID ,'2014-11-07' STRDATE,'6'  BILLNO  

;with cte as(
select ID,MAX(STRDATE) AS MX
from #t
Group by ID
),
cte1 as(
select ID,MX,
	row_number()over(order by mx desc) as SN	
	FROM cte
)
select #t.ID,#t.STRDATE,#t.BILLNO,SN from cte1
	JOIN #t ON #t.ID=cte1.ID
Order by SN

drop table #t

[hepe00]

SELECT 'A' ID ,'2014-11-11' STRDATE,'4' BILLNO INTO #t
UNION ALL
SELECT 'A' ID ,'2014-11-10' STRDATE,'8' BILLNO 
UNION ALL
SELECT 'C' ID ,'2014-11-09' STRDATE,'4' BILLNO 
UNION ALL
SELECT 'C' ID ,'2014-12-09' STRDATE,'5' BILLNO 
UNION ALL
SELECT 'A' ID ,'2014-11-08' STRDATE,'7' BILLNO 
UNION ALL
SELECT 'B' ID ,'2014-11-07' STRDATE,'6'  BILLNO  

;with cte as(
select ID,MAX(STRDATE) AS MX
from #t
Group by ID
),
cte1 as(
select ID,MX,
	row_number()over(order by mx desc) as SN	
	FROM cte
)
select #t.ID,#t.STRDATE,#t.BILLNO,SN from cte1
	JOIN #t ON #t.ID=cte1.ID
Order by SN asc,STRDATE desc

drop table #t