SQL grouping plus column plus self-numbering self-numbering qualification

Description

(1) The date is displayed in the form of: convert (varchar (7), field name, 120),

(2) Add a column

(3) Self-numbering:

Row_number () Over (Order by field name Desc) as RowID

Row_number () Over (partition by field 1 order By field 2) as RowID

(4) Self-numbering restrictions (not directly in the Where condition)

To illustrate:

Desired effect: monthly statistics of the top 5 jobs (based on the number of pieces)

Initial SQL statement:

Select SUM (sum_truenum) Number of pieces, Pickuser_name,convert (varchar (7), picktime,120) time
From V_outin_maintop
where brand_id=66 and Classreport_name= ' out of the library ' and Len (pickuser_name) >0
and picktime>= ' 2014-9-1 ' and picktime< ' 2014-12-1 '
GROUP BY Pickuser_name,convert (varchar (7), picktime,120)

：

First step: Add a column

SQL statements:

Select jobs = ' picking ', sum (sum_truenum), Pickuser_name pick, convert (varchar (7), picktime,120) time
From V_outin_maintop
where brand_id=66 and Classreport_name= ' out of the library ' and Len (pickuser_name) >0
and picktime>= ' 2014-9-1 ' and picktime< ' 2014-12-1 '
GROUP BY Pickuser_name,convert (varchar (7), picktime,120)

：

Step Two: Add self-numbering (sort by month, group by person)

SQL statements:

Select jobs = ' picking ',row_number () over (partition by convert (varchar (7), picktime,120) Order by sum (SUM_TRUENUM) desc) As RowID,
SUM (sum_truenum) Number of pieces, pickuser_name, convert (varchar (7), picktime,120) time
From V_outin_maintop
where brand_id=66 and Classreport_name= ' out of the library ' and Len (pickuser_name) >0
and picktime>= ' 2014-9-1 ' and picktime< ' 2014-12-1 '
GROUP BY Pickuser_name,convert (varchar (7), picktime,120)

：

Step three: Self-numbering (only the first few)

SQL statements:

With Rowjianhuo
As
(
Select jobs = ' picking ', row_number () over (partition by convert (varchar (7), picktime,120) Order by sum (SUM_TRUENUM) desc) as RowID,
SUM (sum_truenum) Number of pieces, pickuser_name, convert (varchar (7), picktime,120) time
From V_outin_maintop
where brand_id=66 and Classreport_name= ' out of the library ' and Len (pickuser_name) >0
and picktime>= ' 2014-9-1 ' and picktime< ' 2014-12-1 '
GROUP BY Pickuser_name,convert (varchar (7), picktime,120)
)
SELECT * from Rowjianhuo where rowid<6

You can also write this:

SELECT * FROM
(
Select jobs = ' picking ', row_number () over (partition by convert (varchar (7), picktime,120) Order by sum (SUM_TRUENUM) desc) as RowID,
SUM (sum_truenum) Number of pieces, pickuser_name, convert (varchar (7), picktime,120) time
From V_outin_maintop
where brand_id=66 and Classreport_name= ' out of the library ' and Len (pickuser_name) >0
and picktime>= ' 2014-9-1 ' and picktime< ' 2014-12-1 '
GROUP BY Pickuser_name,convert (varchar (7), picktime,120)
) A
where rowid<6

: (Data is a little, so the data does not change)

Note:

Two tables, the same structure, connected to a table: with

SELECT * FROM table 1

UNION ALL

SELECT * FROM Table 2

SQL grouping plus column plus self-numbering self-numbering qualification