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

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

请教一个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)

@Las Vegas

请教一个SQL问题

Replies, comments and Discussions:

More Topics