选择内连接表id vb

Question

这是我第一次用的

SELECT classroom .cls_id, 
   classroom.cls_name, 
   u1.users_id as users_id1,
   u2.users_id as users_id2
FROM   classroom 
   INNER JOIN clsown 
           ON clsown.cls_id = classroom .cls_id 
   INNER JOIN users AS u1 
           ON clsown.users_id = u1.users_id 
   INNER JOIN users AS u2
           ON clsown.users_id = u2.users_id 

我得到这个:

cls_id  cls_name  users_id1  users_id2
1 -------- room1 ------- 1 --------- 1
1 -------- room1 ------- 2 --------- 2
2 -------- room2 ------- 3 --------- 3
2 -------- room2 ------- 4 --------- 4

i want to see

cls_id  cls_name  users_id1  users_id2
1 -------- room1 ------- 1 --------- 2
2 -------- room2 ------- 3 --------- 4

我不知道如何做内连接。我做错了什么?

谢谢你的回答

Answer 1

您在这里注意到的查询实际上需要一个枢轴，因为您希望获取cls_id和cls_name的唯一值，然后将用户按列排序。

我在2009年写了这篇文章，展示了如何做这样的事情。它应该有帮助

Answer 2

由于您使用的是SQL Server，因此有几种方法可以透视数据。

您可以使用CASE表达式的聚合函数:

select cls_id,
    cls_name,
    MAX(case when rn = 1 then users_id end) user_id1,
    MAX(case when rn = 2 then users_id end) user_id2
from
(
    SELECT cr.cls_id, 
        cr.cls_name, 
        u1.users_id,
        ROW_NUMBER() over(partition by cr.cls_id order by u1.users_id) rn
    FROM classroom cr
    INNER JOIN clsown co
        ON co.cls_id = cr.cls_id 
    INNER JOIN users AS u1 
        ON co.users_id = u1.users_id 
) d
group by cls_id, cls_name;

或者您可以使用PIVOT函数:

select cls_id,
    cls_name,
    user_id1, 
    user_id2
from
(
    SELECT cr .cls_id, 
        cr.cls_name, 
        u1.users_id,
        'user_id'+cast(ROW_NUMBER() over(partition by cr.cls_id order by u1.users_id) as varchar2)) col
    FROM classroom cr
    INNER JOIN clsown co
        ON co.cls_id = cr.cls_id 
    INNER JOIN users AS u1 
        ON co.users_id = u1.users_id 
) d
pivot
(
    max(users_id)
    for col in (user_id1, user_id2)
) piv;

Answer 3

我假设您使用Mysql作为RDBMS，如果是这样，您可以使用GROUP_CONCAT()根据您的需要显示records，但以不同的形式。这个查询将填充所有额外的coloumns你想要的，到一个单独的cell由separator分隔。

SELECT classroom .cls_id, 
       classroom.cls_name, 
       GROUP_CONCAT(u1.users_id ORDER BY u1.users_id SEPARATOR ', ') Users 
FROM   classroom 
       INNER JOIN clsown 
               ON clsown.cls_id = classroom .cls_id 
       INNER JOIN users AS u1 
               ON clsown.users_id = u1.users_id
       Group by classroom .cls_id

上面的查询将返回如下记录，

cls_id  cls_name    users
  1      room1       1,2
  2      room2       3,4 

EDIT: Sql server