问题如下:
company
company_id company_name
1 A
2 B
3 Cproduct
product_id company_id product_bame
1 1 aaa
2 2 bbbb
3 1 cccc
4 1 ddd
5 3 eee
6 2 yyyy
7 2 ooooo
8 3 ppp
9 3 wwww
10 1 rrrr
11 2 ttt
12 2 uuu想要结果:
result:
No. compay_name product_name
1 A aaa
2 A ccc
3 B bbb
4 B rrr
5 C eee
6 C ppp就是按照product_name 进行排序后,取出每个公司的前两个商品数据进行显示,想一次性进行检索出来
,这个sql这么写?
company
company_id company_name
1 A
2 B
3 Cproduct
product_id company_id product_bame
1 1 aaa
2 2 bbbb
3 1 cccc
4 1 ddd
5 3 eee
6 2 yyyy
7 2 ooooo
8 3 ppp
9 3 wwww
10 1 rrrr
11 2 ttt
12 2 uuu想要结果:
result:
No. compay_name product_name
1 A aaa
2 A ccc
3 B bbb
4 B rrr
5 C eee
6 C ppp就是按照product_name 进行排序后,取出每个公司的前两个商品数据进行显示,想一次性进行检索出来
,这个sql这么写?
2 from company a
3 join product b
4 on a.company_id=b.company_id
5 where (select count(*) from product where company_id=b.company_id and product_name<b.product_name)<2
6 order by company_name,product_name;COMPANY_NA PRODUCT_NA
---------- ----------
a aaa
a cccc
b bbbb
b ooooo --为什么你的结果是rrrr?
c eee
c ppp已选择6行。
我照着你给的sql,写了一个,
select DISTINCT h.max_temp,d.station_name,h.stano,d.hist_id from history.history_day h join dictionary.dict_station_support d on h.stano=d.hist_id where (select count(*) from history.history_day t where t.stano=h.stano and t.max_temp>h.max_temp)<5 ORDER BY h.max_temp DESC这样没错,
但是:
1>我的数据量非常大,检索会非常慢。
2>如果我检索的信息
select DISTINCT h.max_temp,d.station_name,h.vdate,h.stano,d.hist_id from history.history_day h join dictionary.dict_station_support d on h.stano=d.hist_id where (select count(*) from history.history_day t where t.stano=h.stano and t.max_temp>h.max_temp)<5 ORDER BY h.max_temp DESC
多一项,那么DISTINCT 就不好使了,重复数据会很多。
from (select a.compay_name,b.product_name,
row_number() over(partition by company_id order by product_bame) rn
from company a,product b
where a.company_id=b.company_id) t
where t.rn<=2