如何选择列和计数(*)?在SQL

I have tables like this:

我有这样的桌子:

User (idUser, nameUser, etc)

用户(idus、nameUser等)

Chat (idChat, txtChat)

聊天(idChat txtChat)

Members(idUser, idChat)

成员(idus idChat)

I have to select all the columns in User and in how many group chats (with more than 2 members) is said user and in how many regular chats (two members).

我必须选择User中的所有列，以及有多少组聊天(超过2个成员)被称为User，以及有多少常规聊天(两个成员)。

My first idea was to make two or more selects and use a union but it tourns out it doesn't quite work like that.

我的第一个想法是做两个或更多的选择和使用一个联合，但它是巡回，它不是像那样工作。

I tried something like this

我试过类似的方法。

    select *
    from User
    where idUser in (select idUser
                     from Members)

I tried getting the users that were in chats but I really don't know how to count aswell

我试着找那些聊天的用户，但是我真的不知道怎么计算

or something like that, I don't really know where to put count (*) I know how to count the number of rows a select gets me but I don't know how to get it as another column

或者类似的，我不知道把count(*)放到哪里我知道如何计算一个select得到的行数但是我不知道如何把它作为另一列

1 个解决方案

#1

Something like this should do things for you (with GROUP BY):

像这样的事情应该为你做些什么(与团体BY):

SELECT  u.idUser,
        u.nameUser,
        COUNT(DISTINCT m.idChat) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser
GROUP BY u.idUser, u.nameUser

Or with PARTITION BY

或与分区

SELECT DISTINCT 
        u.idUser,
        u.nameUser,
        COUNT(m.idChat) OVER (PARTITION BY nameUser) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser

#1