SELECT * FROM table
WHERE C_Time =
(SELECT max(C_Time) FROM table t1 WHERE C_Date =
(SELECT max(C_Date) FROM table t2 WHERE t1.ID = t2.ID)
);
这为您提供了每个 ID 的最高 C_Date 和 C_Time 值的条目
0
我会使用窗口函数:
select t.*
from (select t.*,
row_number() over (partition by id order by date desc, time desc) as seqnum
from t
) t
where seqnum = 1;
或者,如果您有一个每个客户有一行的表,那么 apply 可能是最好的:
select t.*
from customers c cross apply
(select top (1) t.*
from t
where t.id = c.id
order by date desc, time desc
) t;
0
怎么样
SELECT T1.*
FROM T T1 INNER JOIN
(
SELECT ID,
CName,
MAX(CDate) CDate,
MAX(CTime) CTime
FROM T
GROUP BY ID,
CName
) T2
ON T1.CDate = T2.CDate
AND
T1.CTime = T2.CTime
AND T1.CName = T2.CName;
SELECT T1.*
FROM T T1 INNER JOIN
(
SELECT ID,
MAX(CDate) CDate,
MAX(CTime) CTime
FROM T
GROUP BY ID
) T2
ON T1.CDate = T2.CDate
AND
T1.CTime = T2.CTime;
哪个会回归
+---------------------+----------+--------+----+-------+
| CDate | CTime | Status | ID | CName |
+---------------------+----------+--------+----+-------+
| 22/12/2018 00:00:00 | 16:27:57 | 1 | 1 | A |
| 21/12/2018 00:00:00 | 15:41:13 | 4 | 2 | B |
| 21/12/2018 00:00:00 | 15:29:46 | 4 | 3 | D |
+---------------------+----------+--------+----+-------+
3 回答
这为您提供了每个
ID
的最高C_Date
和C_Time
值的条目我会使用窗口函数:
或者,如果您有一个每个客户有一行的表,那么
apply
可能是最好的:怎么样
哪个会回归
要么
哪个会回归
Demo