×

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

no, that won't work. you still get all the versions.

Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 问大家一个sql server得问题,能否帮我看看是否有更快更简洁的方法:
    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
      • no, that won't work. you still get all the versions.
        • 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...
    • why cursor ? Max function and group by clause
      • of course I tried this way first. :) #3466778@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
      • :)) Thanks! this one seems work fine! 顺便讨论一下,这种表的设计会有很大的performance问题啊,每个query都要自连接一下。
        • (#3466882) :) 不光performance的问题,如果name不一致(常有的事,改名了),输出结果会重复。比方ypeid_1 typename1 version2, typeid_1 typename1_a version2。建议名字分开建表。
          • 这个表不是我设计的。:))它意图就是track所有的变化纪录,如果for id1, the name becomes name_a, 就会自动insert a new row, and the version will increase 1. 所以,同一个id,决不会有同样的version,
            这样设计是有目的,当一个订单需要变动,很多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。咦?你老兄怎么也玩起这个来了?
              • 你搞错了……把他的例子数据放进去试试?
                • 是搞错了,因为LZ接我话接错了。本来是担心会发生一个ID对应多个Name的情况,LZ的回答是否定的,俺没仔细看贴,以为说Name和ID一一对应,不会有一个ID对应多个不同名字的情况。:-(
    • select typeid,typename,version from table group by typeid, typename having version=max(version)
      • no, I also tried this way. thanks.
        • maybe this: select * from table1 where version in (select max(version) from table1)
          • I don't think so.
          • no, since 'select max(version) from table1' will only get one max version in the whole table, not max version for each id.
            • 怪俺审题不清:)
              • 最近准备了几件马甲?否则你这潜水能力也太强了。:)
                • 呵呵。马甲有2件,很少穿啦,记性不好,记不住那么多password喽。
    • 这什么破表哇
    • 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)
    • my answer
      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)
      SELECT id, SUBSTR(MAX(LPAD(version,10)||name),11),MAX(version)
      FROM my_versions
      GROUP BY id;

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