有张table A
ID CODE DATE
1 1_1 2010-01-01
1 1_2 2010-01-02
1 1_3 2010-01-03
2 1_1 2010-02-01
2 1_2 2010-02-03
2 1_3 2010-02-02
3 1_1 2010_03_03
3 1_2 2010_03_02
3 1_3 2010_03_01
通过SQL 查询获得以下结果
ID CODE DATE
1 1_3 2010-01-03
2 1_2 2010-02-03
3 1_1 2010-03-03获得每个ID DATE最大的一笔数据并按DATE升序排列
group by ID实现不了没CODE,请高手指导
ID CODE DATE
1 1_1 2010-01-01
1 1_2 2010-01-02
1 1_3 2010-01-03
2 1_1 2010-02-01
2 1_2 2010-02-03
2 1_3 2010-02-02
3 1_1 2010_03_03
3 1_2 2010_03_02
3 1_3 2010_03_01
通过SQL 查询获得以下结果
ID CODE DATE
1 1_3 2010-01-03
2 1_2 2010-02-03
3 1_1 2010-03-03获得每个ID DATE最大的一笔数据并按DATE升序排列
group by ID实现不了没CODE,请高手指导
[征集]分组取最大N条记录方法征集,及散分....
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[CODE] [nvarchar](10),[DATE] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','1_1','2010-01-01' UNION ALL
SELECT '1','1_2','2010-01-02' UNION ALL
SELECT '1','1_3','2010-01-03' UNION ALL
SELECT '2','1_1','2010-02-01' UNION ALL
SELECT '2','1_2','2010-02-03' UNION ALL
SELECT '2','1_3','2010-02-02' UNION ALL
SELECT '3','1_1','2010_03_03' UNION ALL
SELECT '3','1_2','2010_03_02' UNION ALL
SELECT '3','1_3','2010_03_01'--SELECT * FROM [tb]-->SQL查询如下:
--法1:
SELECT *
FROM tb t
WHERE date = (
SELECT MAX(date)
FROM tb
WHERE id = t.id
)
--法2:
SELECT *
FROM tb t
WHERE NOT EXISTS (
SELECT 1
FROM tb
WHERE id = t.id
AND date>t.date
)
--法3:
SELECT *
FROM tb t
WHERE 1 = (
SELECT COUNT(1)
FROM tb
WHERE date>= t.date
AND id = t.ID
)--...............
/*
ID CODE DATE
----------- ---------- ----------
1 1_3 2010-01-03
2 1_2 2010-02-03
3 1_1 2010_03_03(3 行受影响)
*/