我有一个TestTable
包含此示例数据的表:
|ID | Desc | CommentDate | qty | light
+-----+---------+-----------------------------+-----+--------
| A11 | EYE | 2019-01-01 01:00:00.000 | 10 | 20
| A11 | WOMEN | 2019-01-01 05:00:00.000 | 15 | 13
| A11 | SUG | 2019-01-02 00:00:00.000 | 10 | 20
| B11 | WOMEN | 2019-01-02 00:00:00.000 | 20 | 30
我曾试图从上面检索数据TestTable
。我还没有得到实际的结果返回数据。
如果ID
有多个评论日期,则取所有来自 ID 的组顶日期。预期结果应低于。
|ID | Desc | CommentDate | qty | light
+-----+---------+-----------------------------+-----+--------
| A11 | EYE | 2019-01-01 01:00:00.000 | 10 | 20
| A11 | WOMEN | 2019-01-01 05:00:00.000 | 15 | 13
| B11 | WOMEN | 2019-01-02 00:00:00.000 | 20 | 30
这里
现场测试:https : //dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=33fd9aea8a01dcd1aca98416bcacadfd
;WITH CTE AS(
SELECT
DENSE_RANK() OVER (
PARTITION BY Id
order by convert(date, CommentDate)
) RN,
*
FROM
tbl
)
SELECT
*
FROM
CTE
WHERE RN = 1
输出:
RN ID Desc CommentDate qty light
1 A11 EYE 01/01/2019 01:00:00 10 20
1 A11 WOMEN 01/01/2019 05:00:00 15 13
1 B11 WOMEN 02/01/2019 00:00:00 20 30
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句