查询中的 SQL 计数查询

发布于 2024-10-17 23:55:34 字数 243 浏览 5 评论 0原文

我有两个正在使用的表，一个依赖于另一个。我想获取独立表的信息并统计每行数据被依赖表使用的次数。在我当前的查询中，当我知道实际上总共有 38 条记录时，我只得到 1 条记录。我做错了什么？

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid

原文

I have two tables that I'm working with One dependent on the other. And I would like to get the information of the independent tables and count the number of times each row of data was used by the dependent table. With My current query I only get a record count of 1 when I know there in fact 38 records total. What am I doing wrong?

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid

分享到QQ

分享到微博

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

发布评论

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

习惯那些不曾习惯的习惯 2024-10-24 23:55:34

我怀疑你想要做的是添加一个

GROUP BY b.rid

假设你的问题是“每个配方有多少种啤酒”。

另外，当配方没有酿造时，您可能还想使用 LEFT JOIN 来计数 0 行：

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r LEFT JOIN brews b
ON b.rid = r.rid
WHERE r.uid = '#cookie.id#'
GROUP BY b.rid

I suspect what you want to do is add a

GROUP BY b.rid

assuming your question is "How many brews are there for each recipe."

Also you might want to use a LEFT JOIN to also count 0 rows when there is no brew to a recipe:

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r LEFT JOIN brews b
ON b.rid = r.rid
WHERE r.uid = '#cookie.id#'
GROUP BY b.rid

回复收藏 0 原文

℡Ms空城旧梦 2024-10-24 23:55:34

select r.rid, r.name, count(b.bid) as brewtot
from recipes r inner join brews b on r.rid = b.rid 
where r.uid = '#cookie.id#' group by r.rid, r.name

那应该有效

select r.rid, r.name, count(b.bid) as brewtot
from recipes r inner join brews b on r.rid = b.rid 
where r.uid = '#cookie.id#' group by r.rid, r.name

That should work

回复收藏 0 原文

半城柳色半声笛 2024-10-24 23:55:34

在查询末尾添加“GROUP BY r.rid”。

回复收藏 0 原文

安稳善良 2024-10-24 23:55:34

你必须在rid上进行group by。

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid 
group by r.rid

您也想查看所有酿造记录吗？

You have to do group by on rid.

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid 
group by r.rid

Do you want to also see all brews record too?.

回复收藏 0 原文

演多会厌 2024-10-24 23:55:34

我很惊讶这个查询甚至在没有分组依据的情况下也能工作？尝试：

SELECT  r.rid
,       r.name
,       COUNT(b.bid) AS brewtot 
FROM    recipes r
JOIN    brews b
ON      b.rid = r.rid
WHERE   r.uid = '#cookie.id#'
GROUP BY
        r.rid
,       r.name

在使用我的测试设置的 SQL Server 上，这会返回正确的计数。

I'm surprised the query even works without a group by? Try:

SELECT  r.rid
,       r.name
,       COUNT(b.bid) AS brewtot 
FROM    recipes r
JOIN    brews b
ON      b.rid = r.rid
WHERE   r.uid = '#cookie.id#'
GROUP BY
        r.rid
,       r.name

On SQL Server with my test setup, this returns the correct count.

回复收藏 0 原文

~没有更多了~