SQL查询不同关键字问题
在执行联接查询时,我得到重复的数据而不是“不同的”关键字。我对谷歌进行了更多的调暗,但一无所获。表名称是“Event”,它保存事件列表,另一个表是 UserEvents,它为用户分配通风口。事件可以分配给多个用户。我使用的是 SQL Server 2005。
Events
eid | ename
-----------------
e1 | Test event1
e2 | test ecent2
UserEvents
id| uid | eventId
-----------------
1 | u1 | e1
2 | u1 | e2
3 | u2 | e1
查询:
select distinct
Events.eid, Events.ename,UserVents.uid
from
Events
inner join
UserEvents on
UserEvents.eventID=Events.eid
输出:
eid | ename | uid
-------------------
e1 | Test event1 | u1
e2 | Test event2 | u2
e1 | test event1 | u1
问题:
这里,事件是重复的,而不是不同的关键字。它不应重复事件“e1”。 请帮助我。我如何更改查询?这是 SQL Server 2005 中的问题吗? 亲切的帮助
While executing join query I am getting duplicate data instead of the "distinct" keyword. I dim much more google and gained nothing. The table names are "Event" which holds list of events and the other table is UserEvents which assgins vents to users. On event can be assigned to multiple users. I am using SQL server 2005.
Events
eid | ename
-----------------
e1 | Test event1
e2 | test ecent2
UserEvents
id| uid | eventId
-----------------
1 | u1 | e1
2 | u1 | e2
3 | u2 | e1
Query:
select distinct
Events.eid, Events.ename,UserVents.uid
from
Events
inner join
UserEvents on
UserEvents.eventID=Events.eid
Output:
eid | ename | uid
-------------------
e1 | Test event1 | u1
e2 | Test event2 | u2
e1 | test event1 | u1
Issue:
Here, Event is repeating instead of the distinct key word. It should not repeat the event 'e1".
Kindly help me. How I change the query? Is this an issue in SQL server 2005??
kinly help
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(4)
请澄清你的问题,你想看到什么结果。您的第一个查询返回了不同的结果,您可以在此处的 SE 数据浏览器上检查它
https://data.stackexchange.com/stackoverflow/qt/115261
来自 MSDN:独特
指定结果集中只能出现唯一的行。
它不应该重复事件'e1
如何更改查询?
这可能就是您正在寻找的:
select E.eid,
E.ename,
E.uid
from (
select Events.eid,
Events.ename,
UserEvents.uid,
row_number() over(partition by eid
order by UserEvents.uid) as rn
from Events
inner join UserEvents
on UserEvents.eventId=Events.eid
) as E
where E.rn = 1
结果:
eid ename uid
---- ----------- ----
e1 Test event1 u1
e2 test ecent2 u1
在 SE 数据上尝试:https:// data.stackexchange.com/stackoverflow/q/115257/
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
DISTINCT
适用于整个列列表。整行需要相同才能被消除。您在问题中输入的结果不完整,因为您错过了
uid
列。返回
没有任何行是相同的。
DISTINCT
applies to the whole column list. The whole row needs to be the same to be eliminated.The results you have put in your question are not complete as you have missed out the
uid
column.Returns
None of the rows are the same.