pls help me in this Sql query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

pls help me in this Sql query

pls help me in this Sql query# Database - 数据库

h*i2003-07-19 07:07

1 楼

hi, DB experts. here i have an problem to create this query:
i have a table:
T1 {fieldID, fieldvisitTime, fieldUserID, fieldIP}
now i want:
get the last visit time and its corresponding IP for each userID.
I think it's possible to make this query in one SQL sentence. but how?
thanks in advance!!!!!!!!!!!!

b*e2003-07-19 07:07

2 楼

try to use the aggregrate function such as Max()
and then GROUP BY userID.

【在 h***i 的大作中提到】

: hi, DB experts. here i have an problem to create this query:
: i have a table:
: T1 {fieldID, fieldvisitTime, fieldUserID, fieldIP}
: now i want:
: get the last visit time and its corresponding IP for each userID.
: I think it's possible to make this query in one SQL sentence. but how?
: thanks in advance!!!!!!!!!!!!

h*i2003-07-19 07:07

3 楼

ok. now i am using this query:
select fieldUserID, max(fieldvisitTime) as lastTime, fieldIP from T1 group by
fieldUserID
but the problem is, it will not return the "corresponding fieldIP". simply
it just pick the first IP for each userID group.
:(
so how?
thx

【在 b*e 的大作中提到】

: try to use the aggregrate function such as Max()
: and then GROUP BY userID.

m*c2003-07-19 07:07

4 楼

Should you group by all fields instead of just fieldUserID:
... GROUP BY fieldUserID, lastTime, fieldID
by

【在 h***i 的大作中提到】

: ok. now i am using this query:
: select fieldUserID, max(fieldvisitTime) as lastTime, fieldIP from T1 group by
: fieldUserID
: but the problem is, it will not return the "corresponding fieldIP". simply
: it just pick the first IP for each userID group.
: :(
: so how?
: thx

n*n2003-07-19 07:07

5 楼

试试这个:
select t.fieldUserID, t.fieldvisitTime as lastTime, t.fieldIP
from T1 t, (select fieldUserID, max(fieldvisitTime) as visitTime
from T1 group by fieldUserID) m
where t.fieldUserID = m.fieldUserID
and t.fieldvisitTime = m.visitTime

by

【在 h***i 的大作中提到】

a*c2003-07-19 07:07

6 楼

Or this one -
Select fieldUserID, fieldvisittime, fieldIP
from T1 a
where fieldvisittime = (select MAX(fieldvisittime)
from T1 b
where b.fieldUserID = a.fieldUserID)

group
simply

【在 n***n 的大作中提到】

: 试试这个:
: select t.fieldUserID, t.fieldvisitTime as lastTime, t.fieldIP
: from T1 t, (select fieldUserID, max(fieldvisitTime) as visitTime
: from T1 group by fieldUserID) m
: where t.fieldUserID = m.fieldUserID
: and t.fieldvisitTime = m.visitTime
:
: by

h*i2003-07-19 07:07

7 楼

thanks for all of you!
i have never tried to use the "compound" sentence. :)
i'll try and let you know latter.

group
simply

【在 n***n 的大作中提到】