SQL 从sql查询的结果中删除重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20243882/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
remove duplicate records from sql query's result
提问by Pratham4950
SELECT
Distinct loc.name,
loc.latitude,
loc.longitude,
loc.imageurl,
c.userid,
c.locationid,
c.time,
(SELECT Count(*) FROM tbl_likecheckin WHERE checkinid = c.id) AS TOTALCheckin,
(SELECT Count(*) FROM tbl_likecheckin WHERE userid = 57 AND checkinid = c.id) As Checkinflag,
CONVERT(DECIMAL(16,2),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))
AS
Distance
FROM tblcheckin AS C
INNER JOIN tbluser AS u
ON c.userid = u.userid
INNER JOIN tblgetlocation AS loc
ON c.locationid = loc.venueid
WHERE c.flag = 'C'
and CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)
ORDER BY c.time DESC
- Here is the output of my above query, i have applied distinctbut till the data are duplicated because of imageurlcolumn
- so how can i remove duplicated record from result no matter which duplicated row is removed from result.
- I just want a single record.I need imageurl also.
- 这是我上面查询的输出，我已经应用了不同但直到数据因为imageurl列而重复
- 那么无论从结果中删除哪个重复行，我如何从结果中删除重复记录。
- 我只想要一个记录。我也需要 imageurl。
回答by Sahil Sareen
See this : http://www.w3schools.com/sql/sql_distinct.asp
看到这个：http: //www.w3schools.com/sql/sql_distinct.asp
Ur using DISTINCTover all the columns that's why.
If the "imageurl" column is creating trouble for you take it out of the DISTINCT keyword's scope.
Ur在所有列上使用DISTINCT这就是原因。
如果“imageurl”列给您带来麻烦，请将其从 DISTINCT 关键字的范围中删除。
This could be useful : SQL - Select distinct but return all columns?
这可能很有用：SQL - 选择不同但返回所有列？
Example here : http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_distinct
Run this query:
此处示例：http: //www.w3schools.com/sql/trysql.asp?
filename =trysql_select_distinct 运行此查询：
SELECT DISTINCT Country,City FROM Customers;
从客户中选择不同的国家、城市；
and this one:
和这个：
SELECT Country,City FROM Customers Group By Country;
SELECT Country,City FROM Customers Group By Country;
回答by JsonStatham
Difficult to answer this without having the data to play with, something along these lines. The left join should mean that the original CTE result set stays the same and hopefully will just grab the first imageurl match it finds keeping the number of records the same.
如果没有数据可玩，就很难回答这个问题。左连接应该意味着原始 CTE 结果集保持不变，并希望只抓取它找到的第一个 imageurl 匹配，保持记录数相同。
with cte as (
SELECT
Distinct loc.name,
loc.latitude,
loc.longitude,
c.userid,
c.locationid,
c.time,
(SELECT Count(*) FROM tbl_likecheckin WHERE checkinid = c.id) AS TOTALCheckin,
(SELECT Count(*) FROM tbl_likecheckin WHERE userid = 57 AND checkinid = c.id) As Checkinflag,
CONVERT(DECIMAL(16,2),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))
AS
Distance
FROM tblcheckin AS C
INNER JOIN tbluser AS u
ON c.userid = u.userid
INNER JOIN tblgetlocation AS loc
ON c.locationid = loc.venueid
WHERE c.flag = 'C'
and CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)
)
select cte.name, cte.latitude, cte.longitude, cte.userid, cte.locationid, cte.[time], cte.Distance, imagetable.imageurl
from cte
left outer join (SELECT
Distinct
loc.name,
loc.imageurl,
FROM tblcheckin AS C
INNER JOIN tbluser AS u
ON c.userid = u.userid
INNER JOIN tblgetlocation AS loc
ON c.locationid = loc.venueid
WHERE c.flag = 'C'
and CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)) imagetable
on cte.name = imagetable.name
