sql语句问题

[youngtsinghua]

获取name相同的，date最大的一个数据
name date
a 2008.6.1
a 2008.6.2
a 2008.6.3
b 2008.6.1
b 2008.6.2
b 2008.6.3
求sql，得出结果是
name date
a 2008.6.3
b 2008.6.3
各位大虾

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([name] varchar(1),[date] datetime)
insert [TB]
select 'a','2008.6.1' union all
select 'a','2008.6.2' union all
select 'a','2008.6.3' union all
select 'b','2008.6.1' union all
select 'b','2008.6.2' union all
select 'b','2008.6.3'

select * from TB t where not exists(select 1 from tb where t.name=name and t.date<date)
/*
name date
---- -----------------------
a    2008-06-03 00:00:00.000
b    2008-06-03 00:00:00.000

(2 行受影响)

*/

drop table TB

[feixianxxx]

select * 
from tb k
where not exists(select * from  tb where name1=k.name and DATEDIFF(DAY,GETDATE(),date)>0)

[feixianxxx]

select name, MAX(date) as [date] 
from tb k
group by name

[feixianxxx]

if object_id('[TB]') is not null 
drop table [TB]
go
create table [TB]([name] varchar(1),[date] datetime)
insert [TB]
select 'a','2008.6.1' union all
select 'a','2008.6.2' union all
select 'a','2008.6.3' union all
select 'b','2008.6.1' union all
select 'b','2008.6.2' union all
select 'b','2008.6.3'


select name, MAX(date) as [date] 
from tb k
group by name
/*
name date
---- -----------------------
a    2008-06-03 00:00:00.000
b    2008-06-03 00:00:00.000

*/

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-27 19:37:41
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[date] datetime)
insert [tb]
select 'a','2008.6.1' union all
select 'a','2008.6.2' union all
select 'a','2008.6.3' union all
select 'b','2008.6.1' union all
select 'b','2008.6.2' union all
select 'b','2008.6.3'
--------------开始查询--------------------------
--方法一：
select 
name,CONVERT(varchar(12) ,[date] , 102 )
from 
  tb t 
where 
  not exists(select 1 from tb where t.name=name and t.date<date)
--方法二：
select 
name, MAX(CONVERT(varchar(12) ,[date] , 102 )) as [date] 
from 
tb 
group by 
name
----------------结果----------------------------
/*name 
---- ------------
a    2008.06.03
b    2008.06.03
*/

[feixianxxx]

convert(varchar,MAX(date),102) as [date]
这个是你要的格式

给你张转化表

Select     
CONVERT(varchar, getdate(), 1),--mm/dd/yy    
CONVERT(varchar, getdate(), 2),--yy.mm.dd     
CONVERT(varchar, getdate(), 3),--dd/mm/yy     
CONVERT(varchar, getdate(), 4),--dd.mm.yy     
CONVERT(varchar, getdate(), 5),--dd-mm-yy     
CONVERT(varchar, getdate(), 10),--mm-dd-yy     
CONVERT(varchar, getdate(), 11),--yy/mm/dd         
CONVERT(varchar, getdate(), 12)--yymmdd
----带世纪号 
select convert(varchar(10),getdate(),100) --06 15 2007 或0
select convert(varchar(10),getdate(),102) --2007.06.15
select convert(varchar(10),getdate(),103) --15/06/2007
select convert(varchar(10),getdate(),104) --15.06.2007s
select convert(varchar(10),getdate(),105) --15-06-2007
select convert(varchar(10),getdate(),106) --15 06 2007
select convert(varchar(10),getdate(),107) --06-15,200
select convert(varchar(10),getdate(),108) --10:06:46 当前时间
select convert(varchar(10),getdate(),109) --06 15 2007 或者9
select convert(varchar(10),getdate(),110) --06-15-2007
select convert(varchar(10),getdate(),111) --2007/06/15
select convert(varchar(10),getdate(),112) --20070615
select convert(varchar(10),getdate(),113) --15 06 2007 或者13
select convert(varchar(10),getdate(),114) --10:10:37:0
select convert(varchar(10),getdate(),120) --2007-06-15 或20
select convert(varchar(30),getdate(),121) --2007-06-15 10:11:45.040 或21
select convert(varchar(30),getdate(),126) --2007-06-15T10:12:44:603
select convert(varchar(50),getdate(),127) --2007-06-15T10:14:35:433 说带时区
select convert(varchar(40),getdate(),130) --回历dd mon yyyy hh:mi:ss:mmmAM
select convert(varchar(30),getdate(),131) --30/05/1428 10:17:19:470AM

GO

[华夏小卒]

论坛里到处都是

select * from tb t
where not exists(select * from tb where name=t.name and data>t.date)

[jinjazz]

select name, max(date)
from tb
group by name

[jinjazz]

真难看

[fanzhouqi]

[Quote=引用 7 楼 js_szy 的回复:]
论坛里到处都是

select * from tb t
where not exists(select * from tb where name=t.name and data>t.date)
[/Quote]
真的到处都是

[ai_li7758521]

select [name], [date]= max(convert(varchar(12) ,[date] , 102 ))
from tb 
group by name

[lihan6415151528]

select * from table t
 where not exists
(select 1 from tb where t.name=name and t.date<date)

[华夏小卒]

[Quote=引用 9 楼 jinjazz 的回复:]
真难看
[/Quote]

[soft_wsx]

来晚了！

[xiaozejun]

分组 降序排列 应该可以吧

[phonix77]

select name, MAX(date) as [date] 
from tb k
group by name

[xuejiecn]

[Quote=引用 14 楼 soft_wsx 的回复:]
来晚了！
[/Quote]
小乔好久 不见了，呵呵。

[xuejiecn]

不查所有数据的话，下面的就行了。

select name, MAX(date) as [date] 
from tb k
group by name

[cupwei]

select name, MAX(date) as [date]
from tb k
group by name

[taoistong]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[date] datetime)
insert [tb]
select 'a','2008.6.1' union all
select 'a','2008.6.2' union all
select 'a','2008.6.3' union all
select 'b','2008.6.1' union all
select 'b','2008.6.2' union all
select 'b','2008.6.3'
--------------开始查询--------------------------
--方法一：
select 
name,CONVERT(varchar(12) ,[date] , 102 )
from 
  tb t 
where 
  not exists(select 1 from tb where t.name=name and t.date<date)
--方法二：
select 
name, MAX(CONVERT(varchar(12) ,[date] , 102 )) as [date] 
from 
tb 
group by 
name
----------------结果----------------------------
/*name 
---- ------------
a    2008.06.03
b    2008.06.03
*/