Two fields A and B, no matter A=1, B=2, B=2, A=1 group by one article-CodePudding

As title, I do not know can use a query, it is ok to take out a, please tall person to give directions, thank you very much!

Such as

ID A, B
1 1 2
2 2 1
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
After the search, just a record
1 1 2

CodePudding user response:

Top 1??????

CodePudding user response:

 select * from top 1 yourtable

CodePudding user response:

 with t as (
Select 1 as id, 1 as a, as 2 b 
Union all 
Select 2, 2, 1 
) 
Select * from t a 
Cross the apply (
Select (
Select the ', '+ convert (varchar, val) 
The from t 
Unpivot (val for col in (a, b)) p 
Where id=Anderson d 
The order by val 
For XML path (' ') 
) as group_val 
B)

Don't know whether you have this idea, group_val same that all values are the same

CodePudding user response:

refer to the second floor laughing vent grief response:

 select * from top 1 yourtable

Maybe I didn't express clearly, I add, see you didn't see have to understand my problem, I change the statement, such as A="zhang", B="bill" the records, and A="bill", B="zhang SAN" records, this program is considered to be the same condition, so one can just pick it up, ignore other

ID A, B
Tom, dick and harry 1
Zhang SAN
2, dick, and harry3 Liu Jing week

According to my idea after the search, I want to conclude the results

ID A, B
Tom, dick and harry 1
3 Liu Jing week

CodePudding user response:

 with t as (
Select 4 as id, as a '1', '2' as b 
Union all select 5, '2', '1' 
Union all select 1, 'zhang', 'bill' 
Union all select 2, 'bill', 'zhang' 
Union all select 3, 'Liu Jing', 'week' 
) 
Select * from t. (
The select MIN (id) as id 
The from t a 
Cross the apply (
Select (
Select the ', '+ convert (varchar, val) 
The from t 
Unpivot (val for col in (a, b)) p 
Where id=Anderson d 
The order by val 
For XML path (' ') 
) as group_val 
B) 
Group by group_val 
) a 
Left the join t on Anderson, d=d t.i 
The order by t.i d

CodePudding user response:

 
The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
NAME_1 NVARCHAR (10), 
NAME_2 NVARCHAR (10)) 

INSERT INTO # T 
SELECT 'zhang', 'bill' UNION ALL 
SELECT 'bill', 'zhang' UNION ALL 
SELECT 'Liu Jing', 'week' 

SELECT MIN (ID) AS ID, NAME_1 NAME_2 
The FROM 
(SELECT ID, 
The CASE WHEN NAME_1 & lt; NAME_2 THEN NAME_1 ELSE NAME_2 END AS NAME_1, 
The CASE WHEN NAME_1 & gt; NAME_2 THEN NAME_1 ELSE NAME_2 END AS NAME_2 
The FROM # T) AS A 
GROUP BY NAME_1, NAME_2

CodePudding user response:

refer to fifth floor illiterate old gu response:

 with t as (
Select 4 as id, as a '1', '2' as b 
Union all select 5, '2', '1' 
Union all select 1, 'zhang', 'bill' 
Union all select 2, 'bill', 'zhang' 
Union all select 3, 'Liu Jing', 'week' 
) 
Select * from t. (
The select MIN (id) as id 
The from t a 
Cross the apply (
Select (
Select the ', '+ convert (varchar, val) 
The from t 
Unpivot (val for col in (a, b)) p 
Where id=Anderson d 
The order by val 
For XML path (' ') 
) as group_val 
B) 
Group by group_val 
) a 
Left the join t on Anderson, d=d t.i 
The order by t.i d

Thank you very much for the elder, need not understand every detail, as follow, is what I want results, to force yourself added paging, perfect query!