how SQL in the same table judging the value of the same column selected corresponding data ? (don't cycle method)
1. If the trains are listed at the same time have A car, car, B data will display only A car, don't need to display data
B car2. If A car or B trains, only A car will display A car, only B car show B

CodePudding user response:

 
The CREATE TABLE # T 
(
Trains VARCHAR (20), 
Regional VARCHAR (20) 
) 

INSERT INTO # T VALUES (' A ', 'Beijing') 
INSERT INTO # T VALUES (' B ', 'Beijing') 
INSERT INTO # T VALUES (' A ', 'Shanghai') 
INSERT INTO # T VALUES (' B ', 'Shanghai') 
INSERT INTO # T VALUES (' C ', 'Shanghai') 
INSERT INTO # T VALUES (' A ', 'guangzhou) 
INSERT INTO # T VALUES (' B ', 'shenzhen') 
INSERT INTO # T VALUES (' C ', 'nanjing') 
INSERT INTO # T VALUES (' A ', 'chengdu) 

DECLARE @ XX VARCHAR (20)='B' - the priority to choose which trains 
SELECT 
(CASE WHEN 
(SELECT COUNT (*) FROM # T B WHERE b. area=a. region AND b. trains=@ XX) & gt; 0 
THEN 
(@ XX) 
The ELSE 
(SELECT TOP 1 B. Trains FROM # T B WHERE b. area=a. region) 
END 
), 
A. the FROM # T A GROUP BY a. region 

DROP TABLE # T 

A total of only two trains

CodePudding user response:

Didn't see, for example, what was the outcome of the present do you want

CodePudding user response:

SELECT *
FROM the TABLE A
WHERE NOT the EXISTS (SELECT 1 FROM the TABLE WHERE area=a. area AND trains & lt; A. journey)