×

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

请教一个SQL问题

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
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 请教一个SQL问题
    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
    • 俺的水平,扔块砖头,看看能有玉不
      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)
      • 好像加一个排序的字段就可以了凑合用了,嘿嘿
    • ORACLE的写法
      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;
      • 正是我要的!多谢楼上两位!
    • 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.
    • 借贵帖问一下,最近工作中有很多database内容,Oracle, SQL, Access 都有。 希望了解一下有什么好书可以学习一下有关query的知识,需要深入的,比如上面提到的partition, row_number(), rank()等等。