If have a class schedule, there are two columns, respectively is: course ID, only. Course name, can be a moniker, if the first lesson is Chinese, is the mathematics in the second quarter, third quarter is Chinese, then how can I know the first lesson is Chinese, the next time the language in which section (that is, the third quarter), and know it of course ID,

CodePudding user response:

 
- to establish a temporary table 
IF OBJECT_ID (' TEMPDB for.. # Tab1 ') IS NOT NULL 
DROP TABLE # Tab1 
Select * # into Tab1 from (insert test data 
 -The select course ID=1, name='language' 
Union all 
The select course ID=2, name='mathematics' 
Union all 
The select course ID=3, name='language' 
) as Tab1 

- query containing test data of the temporary table 
- select * from # Tab1 

Select 
* 
, row=ROW_NUMBER () over (partition by course name order by course id) - using window function for the new column row assignment, the new column of the row values for repeated course name 'current frequency, the so-called' current 'id sorting based on course 
The from # Tab1 
Order by course ID - can add don't add, without the result sequence will be in accordance with the window function collation, and will maintain the original table order for you to see what window function effect 

- and, finally, we come to the next Chinese classes in which section (ID) course, how to query the 
Select 
* 
The from (
Select 
* 
, a row=ROW_NUMBER () over (partition by course name order by course id) 
The from # Tab1 
) as A 
Where A course name='language' and A.r ow=2 

CodePudding user response:

 
The CREATE TABLE # A VARCHAR (id int, course name (20)) 
INSERT INTO # A 
SELECT 1, 'Chinese' UNION ALL 
SELECT 2, 'mathematics' UNION ALL 
SELECT 3, 'English' UNION ALL 
SELECT 4, 'Chinese' UNION ALL 
SELECT 5, 'physical' UNION ALL 
SELECT 10, 'Chinese' 
Select * from # A 
Declare 
@ course varchar (20)='language', 
Which day @ int=3 
Select the 'first' + cast (a.x xx as varchar (5)) + 'class', a. course name from 
(
Select *, ROW_NUMBER () over (partition by course name order by id) xx, ROW_NUMBER () over (order by id) XXX from # A 
) where a a. of course name=@ and a.x which section x=@ 

Don't know whether to this effect

CodePudding user response:

 CREATE TABLE # A VARCHAR (id int, course name (20)) 
INSERT INTO # A 
SELECT 1, 'Chinese' UNION ALL 
SELECT 2, 'mathematics' UNION ALL 
SELECT 3, 'English' UNION ALL 
SELECT 4, 'Chinese' UNION ALL 
SELECT 5, 'physical' UNION ALL 
SELECT 10, 'Chinese' 
- # 2 data 

Query - 
-- known in front of the Chinese, with id 1, for the next Chinese class id 
SELECT the TOP 1 * 
The from # A WHERE clause [course name]='language' AND id> 1 
The ORDER BY id 
/* 
Id course name 
4 Chinese 
*/