×

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

@Florida

请教一个SQL问题

uzitea(kiss the rain)
select C.companyName, o.orderId from customers c inner join orders o
on c.customerID = o.customerID order by companyName

This is the result of the above query

Alfreds Futterkiste 10835
Alfreds Futterkiste 10952
Alfreds Futterkiste 11011
Ana Trujillo Emparedados y helados 10926
Ana Trujillo Emparedados y helados 10759
------------------------------------------------------------------------------------------------------------
Q: How to get the following result?

Alfreds Futterkiste 10835
null 10952
null 11011
Ana Trujillo Emparedados y helados 10926
null 10759
(#4886645@0)
Last Updated: 2008-12-1
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 请教一个SQL问题 -uzitea(kiss the rain); 2008-12-1 {594} (#4886645@0)
    select C.companyName, o.orderId from customers c inner join orders o
    on c.customerID = o.customerID order by companyName

    This is the result of the above query

    Alfreds Futterkiste 10835
    Alfreds Futterkiste 10952
    Alfreds Futterkiste 11011
    Ana Trujillo Emparedados y helados 10926
    Ana Trujillo Emparedados y helados 10759
    ------------------------------------------------------------------------------------------------------------
    Q: How to get the following result?

    Alfreds Futterkiste 10835
    null 10952
    null 11011
    Ana Trujillo Emparedados y helados 10926
    null 10759
    • 俺的水平，扔块砖头，看看能有玉不 -c1xwy(洪兴罩俺去战斗); 2008-12-1 {281} (#4886709@0)
      select main.name as cname, id
      from result main
      where main.id=(select top 1 id from result sub where main.name=sub.name order by sub.id)
      union
      select null as cname, id
      from result main
      where main.id<>(select top 1 id from result sub where main.name=sun.name order by sub.id)
      • 好像加一个排序的字段就可以了凑合用了，嘿嘿 -c1xwy(洪兴罩俺去战斗); 2008-12-1 (#4886740@0)
    • ORACLE的写法 -newkid(newkid); 2008-12-1 {294} (#4886786@0)
      SELECT (CASE WHEN ROW_NUMBER() OVER (PARTITION BY C.companyName ORDER BY o.orderId)=1 THEN C.companyName
      ELSE NULL
      END) AS display_name
      , o.orderId
      from customers c inner join orders o
      on c.customerID = o.customerID
      order by companyName, o.orderID;
      • 正是我要的！多谢楼上两位！ -uzitea(kiss the rain); 2008-12-1 (#4886973@0)
    • In SQL 2005, either row_number() or rank() can be used as newkid query. However, in SQL 2000 without partition it’s hard to do it. -deep_blue(BLUE); 2008-12-2 (#4887668@0)
    • 借贵帖问一下，最近工作中有很多database内容，Oracle, SQL, Access 都有。 希望了解一下有什么好书可以学习一下有关query的知识，需要深入的，比如上面提到的partition, row_number(), rank()等等。 -letschat(letschat); 2008-12-3 (#4891807@0)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论