如何避免重复值 [英] How to avoid duplicate value

问题描述

嗨！

我使用此查询来获取位置：

hi!
I use this Query to fetch location:

SELECT top 6 L_ID, L_Name, 1 as OBCol FROM Locations where L_Name like 'ban%' UNION  SELECT Distinct
top 4 L_ID, L_Name, 2 as OBCol FROM Locations where L_Name like '%ban%'  order by OBCol

我的结果显示为：

And my result shown as:

ID                 Name             OBCol
3	           Bandarban	          1
118	           Bangladesh	          1
3	            Bandarban	          2
18       	    Gaibandha	          2
102	            Albania	              2
118	            Bangladesh	          2

这里我不想显示Bandarban或Bandarban等重复值。我该怎么办？它plz hel p me



我尝试了什么：



据我所知联盟删除duplicay，我已经使用了联盟，但它不能那样做。如果我删除它的工作，但我需要使用group by。

here i don't want to show duplicate value like Bandarban or Bandarban.how can i do it plz help me

What I have tried:

As i know that Union remove duplicay and i already use union but it can't do that.if I remove group by then its working but i need to use group by.

推荐答案

可能如上所述，对问题进行排序的最简单方法是在UNION中更改第二个查询的where子句，例如

Probably the simplest way to sort your problem as currently stated is to change the where clause on the 2nd query in the UNION e.g.

SELECT top 6 L_ID, L_Name, 1 as OBCol 
FROM Locations where L_Name like 'ban%'
UNION  
SELECT top 4 L_ID, L_Name, 2 as OBCol 
FROM Locations where L_Name like '%ban%'  and L_Name NOT LIKE 'ban%'
order by OBCol

或者你可以将UNION全部放在一起并使用CASE计算出值OBCol

Alternatively you could drop the UNION all together and work out the value OBCol using CASE

SELECT L_ID, L_Name, 
CASE WHEN L_Name like 'ban%' THEN 1 ELSE 2 END as OBCol 
FROM Locations where L_Name like '%ban%'
order by OBCol

这篇关于如何避免重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！