SQL查询不同关键字问题

发布于 12-10 12:02 字数 814 浏览 0 评论 0原文

在执行联接查询时,我得到重复的数据而不是“不同的”关键字。我对谷歌进行了更多的调暗,但一无所获。表名称是“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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

新雨望断虹2024-12-17 12:02:44

DISTINCT 适用于整个列列表。整行需要相同才能被消除。

您在问题中输入的结果不完整,因为您错过了 uid 列。

WITH Events(eid,ename) AS(
SELECT 'e1' ,'Test event1' UNION ALL
SELECT 'e2' ,'test ecent2')

,UserEvents(id,uid,eventId) AS
(
SELECT 1,'u1' , 'e1' UNION ALL
SELECT 2,'u1' , 'e2' UNION ALL
SELECT 3,'u2' , 'e1'
)
select distinct 
        Events.eid, Events.ename,UserEvents.uid  
        from
        Events
        inner join
        UserEvents on
        UserEvents.eventId=Events.eid

返回

eid  ename       uid
---- ----------- ----
e1   Test event1 u1
e1   Test event1 u2
e2   test ecent2 u1

没有任何行是相同的。

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.

WITH Events(eid,ename) AS(
SELECT 'e1' ,'Test event1' UNION ALL
SELECT 'e2' ,'test ecent2')

,UserEvents(id,uid,eventId) AS
(
SELECT 1,'u1' , 'e1' UNION ALL
SELECT 2,'u1' , 'e2' UNION ALL
SELECT 3,'u2' , 'e1'
)
select distinct 
        Events.eid, Events.ename,UserEvents.uid  
        from
        Events
        inner join
        UserEvents on
        UserEvents.eventId=Events.eid

Returns

eid  ename       uid
---- ----------- ----
e1   Test event1 u1
e1   Test event1 u2
e2   test ecent2 u1

None of the rows are the same.

一抹苦笑2024-12-17 12:02:44

请澄清你的问题,你想看到什么结果。您的第一个查询返回了不同的结果,您可以在此处的 SE 数据浏览器上检查它
https://data.stackexchange.com/stackoverflow/qt/115261
来自 MSDN:独特
指定结果集中只能出现唯一的行。

Please, clarify your question, what result do you want to see. Your first query returned distincted result, you can check it on SE data explorer here
https://data.stackexchange.com/stackoverflow/qt/115261
From MSDN: DISTINCT
Specifies that only unique rows can appear in the result set.

病毒体2024-12-17 12:02:44

如果 unique 关键字不起作用,请尝试添加 group by 子句。
您的新查询应如下所示:

select  
            Events.eid, Events.ename,UserVents.uid  
            from
            Events
            inner join
            UserEvents on
            UserEvents.eventID=Events.eid
group by Events.eid, Events.ename,UserVents.uid

if the distinct keyword is not working try adding a group by clause.
Your new query should look like:

select  
            Events.eid, Events.ename,UserVents.uid  
            from
            Events
            inner join
            UserEvents on
            UserEvents.eventID=Events.eid
group by Events.eid, Events.ename,UserVents.uid
-小熊_2024-12-17 12:02:44

它不应该重复事件'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/

It should not repeat the event 'e1

How I change the query?

This might be what you are looking for:

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 

Result:

eid  ename       uid
---- ----------- ----
e1   Test event1 u1
e2   test ecent2 u1    

Try it on SE Data: https://data.stackexchange.com/stackoverflow/q/115257/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文