partition by的用法

一、求解各科成绩均大于80的学生

create table SC
(
Id int identity ,
Name nvarchar(20) ,
KC_Name nvarchar(20),
KC_score int
)
--问题：查询各科成绩都在80分以上的学生：（科目可扩展 不止三科）
insert into SC values('张三','语文',80)
insert into SC values('李四','语文',70)
insert into SC values('钟情','语文',75)
insert into SC values('张三','数学',85)
insert into SC values('李四','数学',88)
insert into SC values('钟情','数学',79)
insert into SC values('张三','英语',80)
insert into SC values('李四','英语',55)

 

语句

 select distinct(sc.Name)
 from SC ,
  (select ROW_NUMBER() over (partition by Name order by SC.KC_score desc) as rownum,Name,KC_Name,KC_score
  from SC
  ) as temp
 where SC.Name=temp.Name and  SC.KC_score >70 and temp.rownum>=3
 

思路：

对每个学生的所有科成绩进行分组后，按照倒序排列，查询最后一个成绩(科目总数量)的大于80即可。

 

二、新建两表

表1

create table T_Product
(
id int  identity ,
Cid int,
PName nvarchar(200)
)

填充数据：

1           1           A产品一
2           1           A产品二
3           1           A产品三
4           1           A产品四
5           1           A产品五
6           2           B产品一
7           2           B产品二
8           2           B产品三
9           2           B产品四
10          2           B产品五
11          3           C产品一
12          3           C产品二
13          3           C产品三
14          3           C产品四
15          3           C产品五
16          4           D产品一
17          4           D产品二
18          4           D产品三
19          4           D产品四
20          4           D产品五

表2

create table T_Company
(
id int identity,
CName nvarchar(200)
)

 

填充数据：

1           公司A
2           公司B
3           公司C
4           公司D
5           公司E

 

查询每个公司的前两个产品

效果如下：

Id          CName                                              PName                                              rownum
----------- -------------------------------------------------- -------------------------------------------------- --------------------
1           公司A                                                A产品一                                               1
2           公司A                                                A产品二                                               2
6           公司B                                                B产品一                                               1
7           公司B                                                B产品二                                               2
11          公司C                                                C产品一                                               1
12          公司C                                                C产品二                                               2
16          公司D                                                D产品一                                               1
17          公司D                                                D产品二                                               2
NULL        公司E                                                NULL                                               NULL

 

 

语句如下：

语句一：

select Tp.Id,T_Company.CName,Tp.PName,rownum from 
(
 select ROW_NUMBER() over (partition by temp.CId order by temp.Id asc) as rownum,temp.Id as Id,temp.CId as Cid,temp.PName as PName
 from 
   (
     select T_Product.Id as Id,T_Product.CId as CId,T_Product.PName  as PName
     from T_Product
     group by T_Product.CId,T_Product.Id,T_Product.CId,T_Product.PName 
   )temp
)Tp

 right join 

 T_Company 

on Tp.Cid = T_Company.Id and  Tp.rownum <= 2

语句二：

select Tp.Id,T_Company.CName,Tp.PName,rownum from 
(
 select ROW_NUMBER() over (partition by temp.CId order by temp.Id asc) as rownum,temp.Id as Id,temp.CId as Cid,temp.PName as PName
 from 
 (
     select T_Product.Id as Id,T_Product.CId as CId,T_Product.PName  as PName
  from T_Product
  group by T_Product.CId,T_Product.Id,T_Product.CId,T_Product.PName 
   )temp
)Tp right join  T_Company on Tp.Cid = T_Company.Id
where Tp.rownum <= 2 or rownum is null

关键词：partition by， right join ...on

病句一：

select Tp.Id,T_Company.CName,Tp.PName,rownum from 
(
 select ROW_NUMBER() over (partition by temp.CId order by temp.Id asc) as rownum,temp.Id as Id,temp.CId as Cid,temp.PName as PName
 from 
 (
     select T_Product.Id as Id,T_Product.CId as CId,T_Product.PName  as PName
  from T_Product
  group by T_Product.CId,T_Product.Id,T_Product.CId,T_Product.PName 
   )temp
)Tp right join  T_Company on Tp.Cid = T_Company.Id
where Tp.rownum <= 2

问题：

思索为什么病句一的E公司的数据没有查询出？