Home > database >  Two fields A and B, no matter A=1, B=2, B=2, A=1 group by one article
Two fields A and B, no matter A=1, B=2, B=2, A=1 group by one article

Time:04-22

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!