各位先進好,請教個SQL排序問題
用文字有點難解釋清楚,我將資料範本及預期資料排序的結果整理在如下連結~
SQL_DATA DEMO
這邊簡單用文字來說明,可能言不及意,還是盡量說明一下。
首先資料集要先用DateTime欄位排序取出Type最新->最舊的排序
然後再依最新Type資料排序,次要排序為DateTime DESC
不知道這樣的需求是否能夠達到呢?
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
(更新回答)
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
忘了補充,資料是隨資料增加而會變動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
不用加TABLE了(我好爛...)
感謝經驗分享,受益良多,謝謝~