0

## SQL資料分層次排序問題

SQL_DATA DEMO

.......(恕刪)

### 3 個回答

1
rogeryao
iT邦大師 3 級 ‧ 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

``````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 檢舉

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

``````

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 檢舉