ID IDP TIME
1 A 2014-03-15
1 B 2014-03-16
2 A 2014-03-12
2 C 2014-03-25
3 B 2014-03-01
.......
结果
ID IDP TIME
1 B 2014-03-16
2 C 2014-03-25
3 B 2014-03-01
.......
------解决方案--------------------
select id,max(idp)idp,time
from tb
group by id,time
这样？
------解决方案--------------------
什么需求？？
------解决方案--------------------
什么规律请说清楚
------解决方案--------------------
怎么感觉跟日期有关系呢、、、
------解决方案--------------------
select * from tb t where not exists(select 1 from tb where id=t.id and time>t.time)
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-02 11:50:53
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[IDP] nvarchar(2),[TIME] datetime)
insert [huang]
select 1,'A','2014-03-15' union all
select 1,'B','2014-03-16' union all
select 2,'A','2014-03-12' union all
select 2,'C','2014-03-25' union all
select 3,'B','2014-03-01'
--------------生成数据--------------------------
SELECT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select Id,MAX([time])[TIME]
from [huang]
GROUP BY id)b WHERE a.id=b.id AND a.[time]=b.[time])
----------------结果----------------------------
/*
ID IDP TIME
----------- ---- -----------------------
1 B 2014-03-16 00:00:00.000
2 C 2014-03-25 00:00:00.000
3 B 2014-03-01 00:00:00.000
*/
------解决方案--------------------
select id,idp,time
from TB
where idp <>'A'
???
------解决方案--------------------
select ID,max(idp),MAX(time) from tb group by ID
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-04-02 12:28:53
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[IDP] varchar(1),[TIME] datetime,[value] int)
insert [tb]
select 1,'A','2014-03-15',10 union all
select 1,'B','2014-03-16',20 union all
select 2,'A','2014-03-12',12 union all
select 2,'C','2014-03-25',10 union all
select 3,'B','2014-03-01',13
--------------开始查询--------------------------
SELECT
a.id, a.IDP, a.time, b.VALUE
FROM
TB AS a
INNER JOIN (SELECT
id, AVG(value) AS VALUE
FROM
TB