帮助 SQL 查询（列出字符串并在同一查询中进行计数）

发布于 2024-08-27 01:18:43 字数 558 浏览 5 评论 0原文

我正在为网页开发一种小型日志系统，并且我在执行多项操作时遇到一些困难。我尝试过做一些嵌套/子查询，但似乎无法做到正确。

我有两个表：

User = {userid: int, username}

Registered =  {userid: int, favoriteid: int}

我需要一个查询来列出所有用户 ID 和每个用户的用户名。另外，我还需要统计用户注册的favoriteid的总数。

未注册任何收藏夹的用户也必须列出，但收藏夹计数显示为零。

我希望我已经解释了我的请求，但否则请回信，以便我可以详细说明。

顺便说一句，我尝试过的查询如下所示：

SELECT user.userid, user.username FROM user,registered WHERE user.userid = registered.userid(SELECT COUNT(favoriteid) FROM registered)

然而，不幸的是，它并没有解决问题

亲切的问候梅斯蒂卡

原文

I’m working on a small kind of log system to a webpage, and I’m having some difficulties with a query I want to do multiple things. I have tried to do some nested / subqueries but can’t seem to get it right.

I’ve two tables:

User = {userid: int, username}

Registered =  {userid: int, favoriteid: int}

What I need is a query to list all the userid’s and the usernames of each user. In addition, I also need to count the total number of favoriteid’s the user is registered with.

A user who is not registered for any favorite must also be listed, but with the favorite count shown as zero.

I hope that I have explained my request probably but otherwise please write back so I can elaborate.

By the way, the query I’ve tried with look like this:

SELECT user.userid, user.username FROM user,registered WHERE user.userid = registered.userid(SELECT COUNT(favoriteid) FROM registered)

However, it doesn’t do the trick, unfortunately

Kind regards
Mestika

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

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

与他有关 2024-09-03 01:18:43

尝试这样的东西（未经测试）

Select u.userid, username, ISNULL(x.FavoriteCount, 0) as FavoriteCount
From User u 
Left Join
(Select userid, count(*) as FavoriteCount
From Registered
Group By userid) x
on u.userid = x.userid

顺便说一下，这是 SQL Server 语法。 MySql 语法非常相似，只需用 IFNULL 替换 ISNULL 函数即可。如果这是另一个 DBMS，那么它至少应该给您一个想法。

编辑：根据 OMG Ponies 的说法，可以使用 COALESCE 代替 ISNULL 来使其跨 SQL Server/MySQL/Oracle/等工作。我已经在 SQL Server 上验证了这一点。

有关 null 函数的详细信息，请参阅此处： http://www.w3schools.com/SQL/ sql_isnull.asp

Try something like this (untested)

Select u.userid, username, ISNULL(x.FavoriteCount, 0) as FavoriteCount
From User u 
Left Join
(Select userid, count(*) as FavoriteCount
From Registered
Group By userid) x
on u.userid = x.userid

By the way, this is SQL Server syntax. MySql syntax would be very similar, just swap out the ISNULL function with IFNULL. If this is another DBMS, then it should at least give you an idea.

Edit: According to OMG Ponies, COALESCE could be used instead of ISNULL to make it work across SQL Server/MySQL/Oracle/etc. I've verified this on SQL Server.

For more info on null functions, see here: http://www.w3schools.com/SQL/sql_isnull.asp

回复收藏 0 原文

黒涩兲箜 2024-09-03 01:18:43

Select userid, username
    , ( Select Count(*) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

如果 favoriteId 可以为 null 则使用

Select userid, username
    , ( Select Count(favoriteid) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

Select userid, username
    , ( Select Count(*) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

If favoriteId can be nullable then use

Select userid, username
    , ( Select Count(favoriteid) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

回复收藏 0 原文

不再让梦枯萎 2024-09-03 01:18:43

SELECT user.userid, user.username , count(favoriteid)
来自用户，已注册
WHERE user.userid = 注册的.userid
组由 user.userid, user.username
联盟
选择用户.用户ID，用户.用户名，0
来自用户
WHERE user.userid NOT EXISITS（从已注册中选择 1，其中 Registered.userid = user.userid）