Use of partition by

Students who have achieved more than 80 of all subjects

CREATE TABLE SC
(
Id int Identity,
Name nvarchar (20),
Kc_name nvarchar (20),
Kc_score int
)
--Question: Check the students in each subject score above 80 points: (subjects can be expanded more than Sanko)
INSERT into SC values (' John ', ' language ', 80)
INSERT into SC values (' Dick ', ' language ', 70)
INSERT into SC values (' loving ', ' language ', 75)
INSERT into SC values (' John ', ' math ', 85)
INSERT into SC values (' Dick ', ' math ', 88)
INSERT into SC values (' loving ', ' math ', 79)
INSERT into SC values (' John ', ' English ', 80)
INSERT into SC values (' Dick ', ' English ', 55)

Statement

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

Ideas:

After grouping all the students ' scores in the following order, check the final score (the total number of subjects) by more than 80.

Ii. New Two-table

Table 1

CREATE TABLE T_product
(
ID int identity,
Cid int,
PName nvarchar (200)
)

Populating data:

1 1 A product one
2 1 A product two
3 1 A product three
4 1 A Product four
5 1 A product five
6 2 B Product One
7 2 B product two
8 2 B product three
9 2 B product Four
2 B Product Five
One 3 C product
3 C Product Two
3 C Product Three
3 C Product Four
3 C Product Five
4 D Product One
4 D Product Two
4 D Product Three
4 D Product Four
4 D Product Five

Table 2

CREATE TABLE T_company
(
ID int identity,
CName nvarchar
)

Populating data:

1 Company A
2 Company B
3 Company C
4 Company D
5 Company E

Check the top two products for each company

The effect is as follows:

Id CName pname rownum
----------- -------------------------------------------------- -------------------------------------------------- --------------------
1 Company A a product 11
2 Company A a product 22
6 Company B Product 11
7 Company B Product 22
11 Company C C product 11
12 Company C C product 22
16 Company D D product 11
17 Company D D product 22
NULL Company e NULL NULL

Statement as follows:

Statement one:

Select Tp.id,t_company.cname,tp.pname,rownum from 
(
 select Row_number ()-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

Statement two:

Select Tp.id,t_company.cname,tp.pname,rownum from 
(
 select Row_number ()-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 are null

keywords: partition by, right join ... on< c13/> wrong sentences A:

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

Problem:

Think about why wrong sentences one of the E company's data is not queried.