i********g
发帖数: 41 | 1 从网上看到这个关于select top N rows from each group的例子：
Let’s say I want to select the two cheapest fruits from each type. Here’s
a first try:
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+----------+-------+
给出的sol |
B*****g
发帖数: 34098 | 2 ？？？
f 是水果表
s
【在 i********g 的大作中提到】
: 从网上看到这个关于select top N rows from each group的例子：
: Let’s say I want to select the two cheapest fruits from each type. Here’s
: a first try:
: +--------+----------+-------+
: | type | variety | price |
: +--------+----------+-------+
: | apple | gala | 2.79 |
: | apple | fuji | 0.24 |
: | orange | valencia | 3.59 |
: | orange | navel | 9.36 |
|
i********g
发帖数: 41 | 3 我刚改了原文，hehe，不过还是看不明白那个程序
【在 B*****g 的大作中提到】
: ？？？
: f 是水果表
:
: s
|
B*****g
发帖数: 34098 | 4 google “Subqueries”
【在 i********g 的大作中提到】
: 我刚改了原文，hehe，不过还是看不明白那个程序
|
C**********r
发帖数: 75 | 5 这种回复跟放屁一样
【在 B*****g 的大作中提到】
: google “Subqueries”
|
B*****g
发帖数: 34098 | 6 不一样，俺这个还有点用
【在 C**********r 的大作中提到】
: 这种回复跟放屁一样
|
k*******s
发帖数: 134 | 7 where f.type = fruits.type and f.price < fruits.price
fruits.type 和 fruits.price 指向的外层的fruit表，内层的fruit表已经被alias了
。所以单独run里面的sql会报错。 |
x***e
发帖数: 2449 | 8 This is a very difficult subquery.
It is hard to understand it even after you have been dealing with SQL for
years.
You can start with join/outerjoin and union first.
And try to rewrite join queries to subqueires and vice versa.
After a while, this one won't be that difficult to understand.
s
【在 i********g 的大作中提到】
: 从网上看到这个关于select top N rows from each group的例子：
: Let’s say I want to select the two cheapest fruits from each type. Here’s
: a first try:
: +--------+----------+-------+
: | type | variety | price |
: +--------+----------+-------+
: | apple | gala | 2.79 |
: | apple | fuji | 0.24 |
: | orange | valencia | 3.59 |
: | orange | navel | 9.36 |
|
b*****e
发帖数: 364 | 9 You will get three cheapest fruits if you use<=2.
You can understand it in this way. "Select" read database row by row. For
every row of data, it will get the logic true/false result in the "where"
clause. If true then output.
Hope this is helpful.
s
【在 i********g 的大作中提到】
: 从网上看到这个关于select top N rows from each group的例子：
: Let’s say I want to select the two cheapest fruits from each type. Here’s
: a first try:
: +--------+----------+-------+
: | type | variety | price |
: +--------+----------+-------+
: | apple | gala | 2.79 |
: | apple | fuji | 0.24 |
: | orange | valencia | 3.59 |
: | orange | navel | 9.36 |
|
b*****e
发帖数: 364 | 10 Here is another example of such kind of subquary.
if object_id('##Table1')<>0 drop table ##Table1
Create table ##Table1 (
[Record Value] varchar(20), [Record Date] smalldatetime
)
go
insert into ##Table1 values ('Record 1','07/21/2003')
insert into ##Table1 values ('Record 2','07/22/2003')
insert into ##Table1 values ('Record 3','07/23/2003')
insert into ##Table1 values ('Record 4','07/24/2003')
insert into ##Table1 values ('Record 5','07/25/2003')
insert into ##Table1 values ('Record 6', |
