问题描述
Promble： - 表1有FromCity ToCity从海得拉巴到班加罗尔的距离为400公里，距离班加罗尔到海德拉巴的距离为400公里，距离海德拉巴到班戈拉的距离为400，而班加罗尔到海得拉巴的距离为400，
消除Row3为Warangal到Hyderabad没有重复记录。
消除Row4为没有重复记录不适用于Vijawada到Hyderabad。
表1
ID FromCity ToCity距离
1海得拉巴班加罗尔400
2班加罗尔海德拉巴400
3 Warangal Hyderabad 150
4 Vijawada Hyderabad 400
预期结果： - 只需要一个记录或者Row2（没有重复记录）如下
第1行
ID FromCity ToCity距离
1海德拉巴班加罗尔400
（OR）
第2行
ID FromCity ToCity距离
2班加罗尔海得拉巴400
我尝试过：
我有连接FromCity + ToCity +距离作为ResultSet1，ToCity + FromCity +距离作为结果集2
ResultSet1 ID
HyderabadBangalore400 1
ResultSet2 ID
海德拉巴班加罗尔400 2
现在我不知道如何更进一步？请提出一些关于问题的想法来解决它
Promble:- Table1 is having FromCity ToCity is having Distance of 400 Km from Hyderabad to Bangalore,400 Km from Bangalore to Hyderabad,Should get only Single Record As Distance from Hyderabad to Banglore is 400 and Bangalore to Hyderabad is 400,
Eliminate the Row3 as No Duplicate record is not there for Warangal to Hyderabad.
Eliminate the Row4 as No Duplicate record is not there for Vijawada to Hyderabad.
Table1
ID FromCity ToCity Distance
1 Hyderabad Bangalore 400
2 Bangalore Hyderabad 400
3 Warangal Hyderabad 150
4 Vijawada Hyderabad 400
Expected Result :-Need to get only one Record Either Row1 or Row2 (No Duplicate Record) as Below
Row1
ID FromCity ToCity Distance
1 Hyderabad Bangalore 400
(OR)
Row2
ID FromCity ToCity Distance
2 Bangalore Hyderabad 400
What I have tried:
I have Concatenation FromCity + ToCity + Distance As ResultSet1, and ToCity + FromCity +Distance As Result Set 2
ResultSet1 ID
HyderabadBangalore400 1
ResultSet2 ID
HyderabadBangalore400 2
Now I am not geting Idea how to go further?Kindly give Some ideas on the Problem to Slove it
推荐答案
select fromcity, tocity, min(distance) distance
from
(
select case when fromcity > tocity then tocity else fromcity end fromcity,
case when fromcity > tocity then fromcity else tocity end tocity, distance
from table1
) a
group by fromcity, tocity
/*
the min(distance) would not be even needed if you are sure that there is no inconsistency in the table ie. distance between bangalore and hyderabad is same in both records
*/
declare @city1 nvarchar(125) = 'Hyderabad'
declare @city2 nvarchar(125) = 'Bangalore'
SELECT DISTINCT @city1, @city2, Distance
FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
OR
(FromCity = @city2 AND ToCity = @city1)
如果您需要与两个城市匹配的Id列表，则生成一个CSV列表，例如
If you need a list of the Id's that matched the two cities then generate a CSV list of them e.g.
SELECT DISTINCT @city1 AS CITY1, @city2 AS CITY2, Distance,
IDS = STUFF((SELECT ', ' + CAST(id as nvarchar) FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
OR (FromCity = @city2 AND ToCity = @city1)
FOR XML PATH('')),1,2,'')
FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
OR
(FromCity = @city2 AND ToCity = @city1)
或者你可以通过作弊使用Min和Max来获取不同列中的Id（假设你不会插入重复的条目到表格上）：
Alternatively you can get the Id in separate columns by "cheating" with Min and Max (this assumes you will not be inserting duplicate entries onto the table):
SELECT DISTINCT @city1 AS CITY1, @city2 AS CITY2, Distance, MIN(Id), MAX(Id)
FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
OR
(FromCity = @city2 AND ToCity = @city1)
GROUP BY Distance
OP澄清了预期的结果。最后一个查询将得到你需要的，只需选择 MIN（Id）或 MAX（Id）。
OP has clarified the expected results. The last query will get what you need, just choose either MIN(Id) or MAX(Id).
这篇关于表1从城市到达班加罗尔有400公里的距离，从班加罗尔到海德拉巴的距离为400公里，应该只得到单一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！