急问一个关于T-SQL的问题，谢谢 - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

急问一个关于T-SQL的问题，谢谢

急问一个关于T-SQL的问题，谢谢# Database - 数据库

B*a2008-11-24 08:11

1 楼

new to SQL, please help...thanks!
2 tables join, A left join B
A has id column
B has id and date columns
in table B, each id has multiple rows of different dates
I need to get the MAX date for each id
and link back to table A
how to do this in T-SQL...?
thanks a lot...

B*g2008-11-24 08:11

2 楼

group by

【在 B****a 的大作中提到】

: new to SQL, please help...thanks!
: 2 tables join, A left join B
: A has id column
: B has id and date columns
: in table B, each id has multiple rows of different dates
: I need to get the MAX date for each id
: and link back to table A
: how to do this in T-SQL...?
: thanks a lot...

B*a2008-11-24 08:11

3 楼

but I need to select a lot more fields from other tables as well, how to
just select and group by ids from table B and get a new field of MAX date?

【在 B*****g 的大作中提到】

: group by

B*g2008-11-24 08:11

4 楼

why do you want to have group by on table B instead of on table A?

【在 B****a 的大作中提到】

: but I need to select a lot more fields from other tables as well, how to
: just select and group by ids from table B and get a new field of MAX date?

B*a2008-11-24 08:11

5 楼

coz only table B has the date variable I need for each id...

【在 B*****g 的大作中提到】

: why do you want to have group by on table B instead of on table A?

B*g2008-11-24 08:11

6 楼

group by is after join.
below is oracle sql, should be ok for sql server.
SELECT a.id, MAX (b.date)
FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
GROUP BY a.id

【在 B****a 的大作中提到】

: coz only table B has the date variable I need for each id...

B*a2008-11-24 08:11

7 楼

谢谢。。。
我试了，但是不work
我一共有三个tables
就是说，A left join B on id
A left join C on seq (another field)
我要B里面的date的MAX
和A里的id，还有C里的一些其他的fields
我晕了。。。

【在 B*****g 的大作中提到】

: group by is after join.
: below is oracle sql, should be ok for sql server.
: SELECT a.id, MAX (b.date)
: FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
: GROUP BY a.id

B*g2008-11-24 08:11

8 楼

You did not mention table C before.
SELECT d.ID, d.maxdate, c.*
FROM (SELECT a.id, a.seqno, MAX (b.date) maxdate
FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
GROUP BY a.id, a.seqno) d LEFT OUTER JOIN tab_c c ON d.
seqno = c.seqno

【在 B****a 的大作中提到】

: 谢谢。。。
: 我试了，但是不work
: 我一共有三个tables
: 就是说，A left join B on id
: A left join C on seq (another field)
: 我要B里面的date的MAX
: 和A里的id，还有C里的一些其他的fields
: 我晕了。。。

B*a2008-11-24 08:11

9 楼

thanks a lot! but I am a little confused here...the d table in select
statement should be a?

【在 B*****g 的大作中提到】

: You did not mention table C before.
: SELECT d.ID, d.maxdate, c.*
: FROM (SELECT a.id, a.seqno, MAX (b.date) maxdate
: FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
: GROUP BY a.id, a.seqno) d LEFT OUTER JOIN tab_c c ON d.
: seqno = c.seqno

B*g2008-11-24 08:11

10 楼

d is the join of a and b

【在 B****a 的大作中提到】

: thanks a lot! but I am a little confused here...the d table in select
: statement should be a?

B*a2008-11-24 08:11

11 楼

I C
thanks a lot! you are so nice! :)

【在 B*****g 的大作中提到】

: d is the join of a and b

c*e2008-11-24 08:11

12 楼

you can do a qualify here
select a.whatever ,
b.whatever
from table_a a
left outer join table_b b
on a.id = b.id
qualify row_number() over (partition by a.id order by b.dt desc) = 1

【在 B****a 的大作中提到】

: but I need to select a lot more fields from other tables as well, how to
: just select and group by ids from table B and get a new field of MAX date?

m*22008-11-24 08:11

13 楼

我用self join,
SELECT a.*,b.*, c.* (--whatever field you want)
FROM tbla a,tblb b1,tblc c
where a.id = b1.id
and a.seqno = c.seqno
and b1.date =
(SELECT MAX (b2.date) from tblb b2
where b2.id = b1.id
group by b2.id)

【在 B****a 的大作中提到】

c*e2008-11-24 08:11

14 楼

如果table b里面有不止一个max(date)的话,就会出现多个结果

【在 m**********2 的大作中提到】

: 我用self join,
: SELECT a.*,b.*, c.* (--whatever field you want)
: FROM tbla a,tblb b1,tblc c
: where a.id = b1.id
: and a.seqno = c.seqno
: and b1.date =
: (SELECT MAX (b2.date) from tblb b2
: where b2.id = b1.id
: group by b2.id)

B*g2008-11-24 08:11

15 楼

1. 虽然人家要left join
2. 虽然你的performance更好
但是，原题说。。。
in table B, each id has multiple rows of different dates

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

: 如果table b里面有不止一个max(date)的话,就会出现多个结果

c*e2008-11-24 08:11

16 楼

multiple rows of differnt dates我很怀疑LZ具体想说什么

【在 B*****g 的大作中提到】

: 1. 虽然人家要left join
: 2. 虽然你的performance更好
: 但是，原题说。。。
: in table B, each id has multiple rows of different dates