The original content of the table as shown in figure:

Now want to achieve:
If the first column of a line or two values are the same, and the third column of a line or two values are y, will the two lines, one line in the third column values instead of n, several other don't change, is below the results;


How do leaders use SQL implementation? Thank you very much!

CodePudding user response:

 CREATE TABLE # a (name VARCHAR (20), grade class VARCHAR (20), whether out of a VARCHAR (10)) 
INSERT INTO # a 
SELECT 'wang', '0101', 'y' UNION ALL 
SELECT 'xiao li', '0101', 'n' UNION ALL 
SELECT 'zhang', '0101', 'n' UNION ALL 
SELECT 'wang', '0201', 'y' UNION ALL 
SELECT 'xiao li', '0201', 'n' UNION ALL 
SELECT 'zhang', '0201', 'y' 


The UPDATE 
T1 
Whether the SET t1. [out]='n' 
The FROM (SELECT row_number () OVER (ORDER BY [name]) n * FROM # a) t1, 
(SELECT row_number () OVER (ORDER BY [name]) n * FROM # a) t2 
WHERE a t1. [name]=t2. [name] AND t1. Whether [out of]='y' AND t2. Whether [out of]='y' 
AND t1. N & gt; T2. N 

CodePudding user response:

The manual way upstairs, automatically add a loop

CodePudding user response:

refer to the second floor nucky_lee response:

manual way upstairs, automatically add a loop if

Upstairs is an automated way, need not add cycle;
But can be optimized, directly according to the "two columns" partition, n is not 1 Update directly is ok;
The optimized code is as follows:
With t1 as (
SELECT row_number () OVER (partition by name, is out of ORDER by [] grade class) n * FROM # a1
Whether the where full score='y'
)
Whether the UPDATE t1 SET t1. [out]='n' WHERE t1. N> 1