SQL資料分層次排序問題

sql orderby

leo226 2021-03-15 11:07:07 ‧ 1694 瀏覽

分享至

各位先進好,請教個SQL排序問題
用文字有點難解釋清楚，我將資料範本及預期資料排序的結果整理在如下連結～
SQL_DATA DEMO
這邊簡單用文字來說明，可能言不及意，還是盡量說明一下。
首先資料集要先用DateTime欄位排序取出Type最新->最舊的排序
然後再依最新Type資料排序,次要排序為DateTime DESC
不知道這樣的需求是否能夠達到呢？

japhenchen iT邦超人 1 級 ‧ 2021-03-15 11:51:20 檢舉

.......(恕刪)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-03-15 11:59:32

最佳解答

CREATE TABLE TestData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--假設資料樣本如下
INSERT INTO TestData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(1,'TypeA','2020-2-8 06:42:24AM',10),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(3,'TypeB','2021-2-6 03:14:33AM',10),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70);

-- 方法一
SELECT TEMP_M.*
FROM TestData AS TEMP_M
LEFT JOIN (
SELECT Xtype,
ROW_NUMBER() OVER (ORDER BY XDateTime DESC) AS ROWNUM_Y
FROM (
SELECT Xtype,XDateTime,
ROW_NUMBER() OVER (PARTITION BY Xtype ORDER BY XDateTime DESC) AS ROWNUM_X
FROM TestData
) AS TEMP_A
WHERE ROWNUM_X=1
) AS TEMP_Q ON TEMP_Q.Xtype=TEMP_M.Xtype
ORDER BY TEMP_Q.ROWNUM_Y,TEMP_M.XDateTime DESC

-- 方法二
SELECT TEMP_M.*
FROM TestData AS TEMP_M
LEFT JOIN (
SELECT Xtype,MAX(XDateTime) AS XDateTime
FROM TestData
GROUP BY Xtype
) AS TEMP_Q ON TEMP_Q.Xtype=TEMP_M.Xtype
ORDER BY TEMP_Q.XDateTime DESC,TEMP_M.XDateTime DESC

-- 方法三
SELECT TEMP_M.*
FROM TestData AS TEMP_M
LEFT JOIN (
SELECT DISTINCT Xtype,MAX(XDateTime) OVER (PARTITION BY Xtype) AS XDateTime
FROM TestData
) AS TEMP_Q ON TEMP_Q.Xtype=TEMP_M.Xtype
ORDER BY TEMP_Q.XDateTime DESC,TEMP_M.XDateTime DESC

-- 方法四
SELECT TEMP_M.*
FROM TestData AS TEMP_M
ORDER BY MAX(XDateTime) OVER (PARTITION BY Xtype) DESC,XDateTime DESC

Demo

回應 1
分享
檢舉

leo226 iT邦新手 4 級 ‧ 2021-03-17 13:23:44 檢舉

感謝前輩分享指導，受益良多，謝謝～

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 2021-03-15 12:12:33

(更新回答)

CREATE TABLE TestData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--假設資料樣本如下
INSERT INTO TestData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(1,'TypeA','2020-2-8 06:42:24AM',10),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(3,'TypeB','2021-2-6 03:14:33AM',10),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70);

CREATE TABLE TYPEORDER (
  ID int null,
  Xtype nvarchar(40) null
)
INSERT INTO TYPEORDER (ID,Xtype) VALUES
(1 , 'TypeB'),(2,'TypeC'),(3,'TypeA')

SELECT *
FROM TestData;
GO

with T1 AS(
  SELECT *
  FROM TestData

)
--依時間排序,最新時間的Type依序為B->C->A

select * from t1
left join (
  select xtype ,max(xdatetime) xd
  from t1 xt 
  group by xtype
  order by xd desc  OFFSET 0 ROWS
) b  on b.xtype = t1.xtype
order by b.xd desc
GO

CREATE TABLE ExpectSorData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--預期得到的結果=>範例資料
INSERT INTO ExpectSorData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(3,'TypeB','2021-2-6 03:14:33AM',10),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70),
(1,'TypeA','2020-2-8 06:42:24AM',10);

SELECT *
FROM ExpectSorData;
GO

db<>fiddle here

以下是之前看錯題意的回答.唉~全錯!!

多做一個TYPEORDER 表來自定義排序 B>C>A

CREATE TABLE TestData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--假設資料樣本如下
INSERT INTO TestData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(1,'TypeA','2020-2-8 06:42:24AM',10),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(3,'TypeB','2021-2-6 03:14:33AM',10),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70);

CREATE TABLE TYPEORDER (
  ID int null,
  Xtype nvarchar(40) null
)
INSERT INTO TYPEORDER (ID,Xtype) VALUES
(1 , 'TypeB'),(2,'TypeC'),(3,'TypeA')

SELECT *
FROM TestData;
GO

with T1 AS(
  SELECT *
  FROM TestData

)
--依時間排序,最新時間的Type依序為B->C->A
SELECT *
FROM T1
LEFT JOIN TYPEORDER T2 ON T1.Xtype = T2.Xtype
ORDER BY T2.ID ,T1.XDatetime desc
GO

CREATE TABLE ExpectSorData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--預期得到的結果=>範例資料
INSERT INTO ExpectSorData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(3,'TypeB','2021-2-6 03:14:33AM',10),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70),
(1,'TypeA','2020-2-8 06:42:24AM',10);

SELECT *
FROM ExpectSorData;
GO

db<>fiddle here

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

japhenchen iT邦超人 1 級 ‧ 2021-03-15 12:15:06 檢舉

CREATE TABLE TYPEORDER (
  ID int null,
  Xtype nvarchar(40) null
)
INSERT INTO TYPEORDER (ID,Xtype) VALUES
(1 , 'TypeB'),(2,'TypeC'),(3,'TypeA')

TESTDATA跟TYPEORDER之間

SELECT *
FROM T1
LEFT JOIN TYPEORDER T2 ON T1.Xtype = T2.Xtype
ORDER BY T2.ID ,T1.XDatetime desc

leo226 iT邦新手 4 級 ‧ 2021-03-15 13:11:09 檢舉

忘了補充，資料是隨資料增加而會變動TypeB->C->A的順序的～
例如現在這個時間新增了一筆TypeA的資料，那資料排序就會變成A->B->C
明天又新增了一筆TypeC的資料，那資料排序就會變成C->A->B

japhenchen iT邦超人 1 級 ‧ 2021-03-15 14:09:56 檢舉

leo226
SORRY誤解題意，第二段的SQL只要這樣寫

select * from t1
left join (
  select xtype ,max(xdatetime) xd
  from t1 xt 
  group by xtype
  order by xd desc  OFFSET 0 ROWS
) b  on b.xtype = t1.xtype
order by b.xd desc

不用加TABLE了(我好爛...)