查询从SQL中删除重复项(Query to remove the duplicates from SQL)

查询从SQL中删除重复项(Query to remove the duplicates from SQL)

我在桌子上叫做距离。 它有4列。 id，start_from，end_to和distance 。

我有一些重复的记录。 在这个意义上重复记录，

start_from | end_to | distance Chennai Bangalore 350 Bangalore Chennai 350 Chennai Hyderabad 500 Hyderabad Chennai 510

在上表中， 钦奈到班加罗尔和班加罗尔与钦奈都有相同的距离 。 所以我需要查询在select上删除该记录。

我想要一个喜欢的外出

start_from | end_to | distance Chennai Bangalore 350 Chennai Hyderabad 500 Hyderabad Chennai 510

I have on table called distance. It has 4 columns. id, start_from, end_to and distance.

I have some duplicate records. Duplicate records in the sense,

start_from | end_to | distance Chennai Bangalore 350 Bangalore Chennai 350 Chennai Hyderabad 500 Hyderabad Chennai 510

In above table, chennai to bangalore and bangalore to chennai both have same distance. So I need query to remove that record on select.

I want a out put like

start_from | end_to | distance Chennai Bangalore 350 Chennai Hyderabad 500 Hyderabad Chennai 510

最满意答案

如果Chennai to Bangalore或Bangalore to Chennai没有什么不同，你可以试试这个：

select max(`start_from`) as `start_from`, min(`end_to`) as `end_to`, `distance` from yourtable group by case when `start_from` > `end_to` then `end_to` else `start_from` end, case when `start_from` > `end_to` then `start_from` else `end_to` end, `distance`

这是rextester中的演示 。

即使Chennai to Hyderabad也是350也有作品演示 。

如果你想让Bangalore to Chennai留Bangalore to Chennai ，你可以改变max和min ：

select min(`start_from`) as `start_from`, max(`end_to`) as `end_to`, `distance` from yourtable group by case when `start_from` > `end_to` then `end_to` else `start_from` end, case when `start_from` > `end_to` then `start_from` else `end_to` end, `distance`

也是一个演示 。

并且大多数数据库兼容的case when 。

If there is no different between Chennai to Bangalore or Bangalore to Chennai, you can try this:

select max(`start_from`) as `start_from`, min(`end_to`) as `end_to`, `distance` from yourtable group by case when `start_from` > `end_to` then `end_to` else `start_from` end, case when `start_from` > `end_to` then `start_from` else `end_to` end, `distance`

Here is a demo in rextester.

Even if Chennai to Hyderabad is 350 also works demo.

And if you want Bangalore to Chennai to be remained, you can just change the place of max and min:

select min(`start_from`) as `start_from`, max(`end_to`) as `end_to`, `distance` from yourtable group by case when `start_from` > `end_to` then `end_to` else `start_from` end, case when `start_from` > `end_to` then `start_from` else `end_to` end, `distance`

also a demo.

And case when will be compatible to most databases.