This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

问大家一个sql server得问题，能否帮我看看是否有更快更简洁的方法：

luoboyang(萝卜秧)

rows like this:
id name version(int)
typeid_1 typename1 version1
typeid_1 typename1_a version2
typeid_2 typename2 version1
typeid_2 typename2_a version2
typeid_2 typename2_b version3
typeid_3 typename3 version1
typeid_3 typename3_f version2

now, I want to find all the latest version for each row, like this:
typeid_1 typename1_a version2
typeid_2 typename2_b version3
typeid_3 typename3_f version2

除了用cursor，还有别的简洁办法没有？

谢谢！

(#3466753@0)
Last Updated: 2007-1-30
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 专业技术讨论 / 问大家一个sql server得问题，能否帮我看看是否有更快更简洁的方法： -luoboyang(萝卜秧); 2007-1-30 {592} (#3466753@0)
rows like this:
id name version(int)
typeid_1 typename1 version1
typeid_1 typename1_a version2
typeid_2 typename2 version1
typeid_2 typename2_a version2
typeid_2 typename2_b version3
typeid_3 typename3 version1
typeid_3 typename3_f version2

now, I want to find all the latest version for each row, like this:
typeid_1 typename1_a version2
typeid_2 typename2_b version3
typeid_3 typename3_f version2

除了用cursor，还有别的简洁办法没有？

谢谢！

sth like: select id, name, max(version) from your_table group by 1,2 order by 1,2 -sammy(21 days); 2007-1-30 (#3466771@0)

no, that won't work. you still get all the versions. -luoboyang(萝卜秧); 2007-1-30 (#3466778@0)

OK, I know what you want now...select id,max(version) from your_table group by 1order by 1 will work but won't show you the name. Let me zai see see... -sammy(21 days); 2007-1-30 (#3466788@0)

why cursor ? Max function and group by clause -hard20(hard20); 2007-1-30 (#3466785@0)

of course I tried this way first. :) #3466778@0 -luoboyang(萝卜秧); 2007-1-30 (#3466794@0)

select distinct a.id, a.name, a.version from tbl a join (select id, max(version) as version from tbl group by id) b on a.id=b.id and a.version = b.version -bdbs(不多不少); 2007-1-30 (#3466810@0)

:)) Thanks! this one seems work fine! 顺便讨论一下，这种表的设计会有很大的performance问题啊，每个query都要自连接一下。 -luoboyang(萝卜秧); 2007-1-30 (#3466833@0)

(#3466882) ：）不光performance的问题，如果name不一致（常有的事，改名了），输出结果会重复。比方ypeid_1 typename1 version2, typeid_1 typename1_a version2。建议名字分开建表。 -bdbs(不多不少); 2007-1-30 (#3466912@0)

这个表不是我设计的。：））它意图就是track所有的变化纪录，如果for id1, the name becomes name_a, 就会自动insert a new row, and the version will increase 1. 所以，同一个id，决不会有同样的version， -luoboyang(萝卜秧); 2007-1-30 {88} (#3466953@0)
这样设计是有目的，当一个订单需要变动，很多XRef table都需要找回当初订的时候的样子。。。。

如果id name unique，可以把id & name看作一个field，就不用join了。select cast(id as char(10)) + name, max(version) from tbl group by cast(id as char(10)) + name。咦？你老兄怎么也玩起这个来了？ -bdbs(不多不少); 2007-1-30 (#3467116@0)

你搞错了……把他的例子数据放进去试试？ -newkid(newkid); 2007-1-30 (#3467158@0)

是搞错了，因为LZ接我话接错了。本来是担心会发生一个ID对应多个Name的情况，LZ的回答是否定的，俺没仔细看贴，以为说Name和ID一一对应，不会有一个ID对应多个不同名字的情况。:-( -bdbs(不多不少); 2007-1-30 (#3467197@0)

select typeid,typename,version from table group by typeid, typename having version=max(version) -beware(十二月的蚂蚱); 2007-1-30 (#3466811@0)

no, I also tried this way. thanks. -luoboyang(萝卜秧); 2007-1-30 (#3466823@0)

maybe this: select * from table1 where version in (select max(version) from table1) -yangn(Raymond); 2007-1-30 (#3466841@0)

I don't think so. -sammy(21 days); 2007-1-30 (#3466845@0)
no, since 'select max(version) from table1' will only get one max version in the whole table, not max version for each id. -luoboyang(萝卜秧); 2007-1-30 (#3466850@0)

怪俺审题不清:) -yangn(Raymond); 2007-1-30 (#3466863@0)

最近准备了几件马甲？否则你这潜水能力也太强了。：） -luoboyang(萝卜秧); 2007-1-30 (#3466870@0)

呵呵。马甲有２件，很少穿啦，记性不好，记不住那么多password喽。 -yangn(Raymond); 2007-1-30 (#3466889@0)

这什么破表哇 -xanada(㊣流水); 2007-1-30 (#3466882@0)
how about this: select id,version from test1 td group by id,version having version=(select max(version) from test1 te where te.id=td.id) -yuanzidan(原子弹); 2007-1-30 (#3466919@0)
my answer -newkid(newkid); 2007-1-30 {305} (#3467019@0)
SELECT DISTINCT FIRST_VALUE(id) OVER (PARTITION BY id ORDER BY version DESC)
,FIRST_VALUE(name) OVER (PARTITION BY id ORDER BY version DESC)
,FIRST_VALUE(version) OVER (PARTITION BY id ORDER BY version DESC)
FROM my_versions;

这是ORACLE的函数，SQLSERVER应该有对应的。这样省去了表连接。
另一种方法(传统SQL) -newkid(newkid); 2007-1-30 {191} (#3467062@0)
SELECT id, SUBSTR(MAX(LPAD(version,10)||name),11),MAX(version)
FROM my_versions
GROUP BY id;

这里假设version是数值型，LPAD是为了让它们右对齐。||是ORACLE 的字符串连接符，大概相当于+号.

@Florida

问大家一个sql server得问题，能否帮我看看是否有更快更简洁的方法：

Replies, comments and Discussions:

More Topics