iT邦幫忙

0

SQL資料分層次排序問題

  • 分享至 

  • xImage

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

.......(恕刪)
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
rogeryao
iT邦超人 7 級 ‧ 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

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

感謝前輩分享指導,受益良多,謝謝~

1
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

看更多先前的回應...收起先前的回應...
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

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

https://ithelp.ithome.com.tw/upload/images/20210315/20117954qMQtUDi3PX.jpg
不用加TABLE了(我好爛...)

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

感謝經驗分享,受益良多,謝謝~

1
一級屠豬士
iT邦大師 1 級 ‧ 2021-03-15 13:45:35

測試資料多補了一筆

(11, 'TypeB', '2021-2-5 08:14:33AM',12)

db<>fiddle here

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

感謝經驗分享,受益良多,謝謝~

我要發表回答

立即登入回答