求一SQL语句 ( 积分: 100 )

J

jimmy_che

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #1

现有一数据库表，里面有name,date,act三个字段，同一个name根据date的不同act也不同，我想选择最后日期(date)的act,应该怎么写？
例子： name date act
aa 2006-1-1 1
bb 2006-2-1 1
aa 2007-3-3 2
bb 2007-3-1 2
我现在想找出aa在act字段中的2和bb在act字段中的2，这个sql语句应该怎么写，谢谢了！

 

D

Dong_HC

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #2

select distinct name,date,cat from table where date in (select max(date) form table group by date)

 

S

songrun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #3

no.1
select name,[date],act
from tb
where [date] =(select top 1 [date] from tb b where tb.name=b.name order by date desc )
group by name,[date],act
order by name
No2
select name,[date],act
from tb
where name in ('aa','bb')and act='2'

 

S

Sewim

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #4

select *
from table1
where date in( select max(date) from table1 group by name)
我已经验证过了,哈哈

 

Y

yyimen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #5

SELECT *
FROM TABLE1 as a INNER JOIN
(SELECT Name,MAX(Date) FROM TABLE1 GROUP Name,Date) as b on a.Name=b.Name and a.Date=b.Date

 

J

jenhon

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #6

select a.name,a.date,b.act
from
(select name,max(date)as date from table group by name)a
left join
(select * from table)b on a.name=b.name and a.date=b.date

 

J

jenhon

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #7

以前试过，开始觉得很难很乱，后来倒过来想就容易了。

不过相同的date、name会出现重复的，建议加上time比较精确一点，要不命中的范围太大了。

 

D

deardai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #8

SELECT distinct a.aname,a.act FROM TABLE1 a INNER JOIN
(SELECT Name,MAX(Date) FROM TABLE1 GROUP Name,Date) b on a.Name=b.Name and a.Date=b.Date

 

J

jimmy_che

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #9

有一点没说明白我这里的date是这样的格式：2004-11-24 14:12:00
这样的话用max不好用吧？我试过Sewim的方法不行

 

S

sngirl

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #10

select max(date),act from table where name in(aa,bb) and act='2';

 

J

jimmy_che

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #11

我说的act=2是泛指，实际上很多数据，并不是等于2

 

Y

yyimen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #12

SELECT *
FROM TABLE1 as a INNER JOIN
(SELECT Name,MAX(cast(Date as Datetime)) as Date FROM TABLE1 GROUP Name,cast(Date as Datetime)) as b on a.Name=b.Name and cast(a.Date as Datetime)=b.Date

 

Y

yyimen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #13

access
SELECT *
FROM TABLE1 as a INNER JOIN
(SELECT Name,MAX(cdate(Date)) as Date FROM TABLE1 GROUP Name,cdate(Date) as b on a.Name=b.Name and cdate(Date)=b.Date

 

J

jenhon

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-28

• #14

来自：jimmy_che, 时间：2007-3-28 13:21:26, ID：3691352
有一点没说明白我这里的date是这样的格式：2004-11-24 14:12:00
这样的话用max不好用吧？我试过Sewim的方法不行

如果date带时间最好，我的句子应该就比较精确吧，试试看。