本来 是想问个sql语句该怎么写,现在写出来了,想问下我写的sql还有优化一下不
id date no_head no_state
1 2013-11-16 01 a
2 2013-11-17 01 b
3 2013-11-18 01 c
4 2013-11-17 02 a
5 2013-11-16 02 b
我想根据no_head进行分组
然后要获得date为最大那行的数据
id,no_head,no_state都要获得
我现在的语句是
select
a.id,
a.date,
a.no_head,
a.no_state
from test a
left join (
select distinct
max (date) as date,
--max(no_state) as no_state,
--max(id) as id,
no_head
from test
group by no_head --,no_state
) b on a.no_head = b.no_head
where a.date = b.date
-- ----------------------------
-- Table structure for [dbo].[test]
-- ----------------------------
DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[id] int NOT NULL ,
[date] datetime NULL ,
[no_head] varchar(2) NULL ,
[no_state] varchar(2) NULL
)
GO
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'1', N'2013-11-16 00:00:00.000', N'01', N'a');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'2', N'2013-11-17 00:00:00.000', N'01', N'b');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'3', N'2013-11-18 00:00:00.000', N'01', N'c');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'4', N'2013-11-17 00:00:00.000', N'02', N'a');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'5', N'2013-11-16 00:00:00.000', N'02', N'b');
GO
-- ----------------------------
-- Indexes structure for table test
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table [dbo].[test]
-- ----------------------------
ALTER TABLE [dbo].[test] ADD PRIMARY KEY ([id])
GO
SELECT *
FROM test a
WHERE EXISTS (SELECT 1 FROM (SELECT no_head,max(date)?as?date FROM test GROUP BY no_head )b WHERE a.no_head=b.no_head AND a.[date]=b.[date])
--感谢楼主提供表和测试数据语句
--楼主,相同no_head 下,date 会相同吗? 如果相同的话,你们的语句会有点问题的。
-- 我改了一下代码,如下。
--我的结果只有2行。你们的有3行。
-- ----------------------------
-- Table structure for [dbo].[test]
-- ----------------------------
DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[id] int NOT NULL ,
[date] datetime NULL ,
[no_head] varchar(2) NULL ,
[no_state] varchar(2) NULL
)
GO
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'1', N'2013-11-16 00:00:00.000', N'01', N'a');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'2', N'2013-11-17 00:00:00.000', N'01', N'b');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'3', N'2013-11-18 00:00:00.000', N'01', N'c');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'4', N'2013-11-17 00:00:00.000', N'02', N'a');
GO
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'5', N'2013-11-16 00:00:00.000', N'02', N'b');
GO
/*add by feiazi*/
INSERT INTO [dbo].[test] ([id], [date], [no_head], [no_state]) VALUES (N'6', N'2013-11-17 00:00:00.000', N'02', N'c');
GO
-- ----------------------------
-- Indexes structure for table test
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table [dbo].[test]
-- ----------------------------
ALTER TABLE [dbo].[test] ADD PRIMARY KEY ([id])
GO
;WITH t AS (
SELECT *
,ROW_NUMBER() OVER ( PARTITION BY t.no_head ORDER BY t.date DESC,t.id) Rn
FROM test t
)SELECT * FROM t WHERE t.Rn=1;