22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM TB WHERE ID IN(SELECT ID FROM (SELECT MAX(ID)ID,日期 FROM TB GROUP BY 日期 )AS T)
SELECT * FROM TB WHERE ID IN(SELECT ID FROM (SELECT ID,MAX(日期)日期 FROM TB )AS T)
DECLARE @TB TABLE([id] INT, [MID] INT, [istop] INT, [date] NVARCHAR(3))
INSERT @TB
SELECT 1, 1, 1, N'日期1' UNION ALL
SELECT 2, 1, 0, N'日期2' UNION ALL
SELECT 3, 2, 3, N'日期3' UNION ALL
SELECT 4, 2, 2, N'日期4' UNION ALL
SELECT 5, 2, 2, N'日期4' UNION ALL
SELECT 6, 3, 1, N'日期4'
SELECT * FROM @TB T WHERE ID IN(SELECT TOP 2 ID FROM @TB WHERE MID=T.MID)
/*
id MID istop date
----------- ----------- ----------- ----
1 1 1 日期1
2 1 0 日期2
3 2 3 日期3
4 2 2 日期4
6 3 1 日期4
(影響 5 個資料列)
*/
DECLARE @TB TABLE([id] INT, [MID] INT, [istop] INT, [date] NVARCHAR(3))
INSERT @TB
SELECT 1, 1, 1, N'日期1' UNION ALL
SELECT 2, 1, 0, N'日期2' UNION ALL
SELECT 3, 2, 3, N'日期3' UNION ALL
SELECT 4, 2, 2, N'日期4' UNION ALL
SELECT 5, 2, 2, N'日期4' UNION ALL
SELECT 6, 3, 1, N'日期4'
SELECT *
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY MID ORDER BY istop,date)
FROM @TB
) T
WHERE SEQ<=2
/*
id MID istop date SEQ
----------- ----------- ----------- ---- --------------------
2 1 0 日期2 1
1 1 1 日期1 2
4 2 2 日期4 1
5 2 2 日期4 2
6 3 1 日期4 1
*/
;with cte as
(
select *,px = row_number() over(partition by MID order by date) from table
)
select * from cte where px <=2
SELECT * FROM TB T WHERE ID IN(SELECT TOP 2 ID FROM TB WHERE MID=T.MID)