获取 select...group by mysql 语句的行数

发布于 2024-09-06 13:50:26 字数 341 浏览 4 评论 0原文

我有一个产品表，其中包含大约 2000 种服装产品，每个产品都有一个 grpIdent 字段和一个 productGroup 字段。

当我运行以下查询时：

select count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

根据 SQL Yog，我得到 99 行的结果集，其中包含与每个组的行数相关的不同值。但我想返回数字 99 给出返回的行数。

有人知道我如何才能实现这一目标吗？

原文

I have a table of products which contains some 2000 clothing products, each product has a grpIdent field and a productGroup field.

when I run the following query:

select count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

I get a resultset of 99 rows, according to SQL Yog, containing different values pertaining to the number of rows for each group. BUT I want to return the number 99 giving the number of rows returned.

Does anybody have an idea on how I can achieve this please?

分享到QQ

分享到微博

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

发布评论

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

烈酒灼喉 2024-09-13 13:50:26

SELECT COUNT(*) FROM ([Your Query])

将返回查询返回的行数。

SELECT COUNT(*) FROM ([Your Query])

Will return the number of rows from your query returns.

回复收藏 0 原文

难得心□动 2024-09-13 13:50:26

select **@@Rowcount** as myResultRowsCount
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

select **@@Rowcount** as myResultRowsCount
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

回复收藏 0 原文

左耳近心 2024-09-13 13:50:26

select productGroup, grpIdent, count(*) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent;

将返回 grpIdent 不同值的计数。

第 1 点。您可以嵌套表格并选择 count(*) 来计算行数。

第 2 点。下面是值基数的首选查询。

select count (distinct grpIdent) 
from tblclothingitems ci 
where productGroup='hel';

select productGroup, grpIdent, count(*) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent;

will return counts for different values of grpIdent.

Point 1. You could nest the table and select count(*) to count the rows.

Point 2. This below is the preferred query for the cardinality of the values.

select count (distinct grpIdent) 
from tblclothingitems ci 
where productGroup='hel';

回复收藏 0 原文

别把无礼当个性 2024-09-13 13:50:26

如果您想要单个值，则必须选择一个变量。
像这样：

DECLARE @Count INT;
select @Count = count(1) from tblclothingitems ci where productGroup='hel' group by productGroup, grpIdent
RETURN @Count

编辑：
看来我问错了你的问题。如果我现在明白了，你可以使用：
从 tblclothingitems 中选择 COUNT（不同的产品组）

If you want a single value, you have to select into a variable.
Like this:

DECLARE @Count INT;
select @Count = count(1) from tblclothingitems ci where productGroup='hel' group by productGroup, grpIdent
RETURN @Count

EDIT:
Seems i got your question wrong. If i understand it right now, you could use:
SELECT COUNT(DISTINCT productgroup) FROM tblclothingitems

回复收藏 0 原文

野鹿林 2024-09-13 13:50:26

使用 MySQL 的内置函数“SQL_CALC_FOUND_ROWS”，其工作原理如下，使用上面的示例查询；

select SQL_CALC_FOUND_ROWS count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

然后发出第二条MySQL命令；

SELECT FOUND_ROWS();

并在您的软件中使用该结果 - 您可以在许多领域使用它，例如更新数据，或者使用“LIMIT”子句来限制返回到应用程序的行数。

SQL_CALC_FOUND_ROWS 告诉 MySQL 计算行数，忽略“LIMIT”子句，这允许您执行类似的操作。

SELECT SQL_CALC_FOUND_ROWS * 
FROM `myTable` 
WHERE `title` LIKE "%blah%" 
LIMIT 100,10;

从第100行开始提取10行，然后；

SELECT FOUND_ROWS();

这将告诉您整个表的 title 字段中有多少行带有“blah”，然后您可以将此信息显示为“Showing 10 rows from 12345 Total”

Use the built in function of MySQL "SQL_CALC_FOUND_ROWS" which works as follows, using your example query above;

select SQL_CALC_FOUND_ROWS count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

Then issue a second MySQL command;

SELECT FOUND_ROWS();

And use that result within your software - you can use this in many areas, for example updating data, or where you are using a "LIMIT" clause to restrict the number of rows returned to your app.

The SQL_CALC_FOUND_ROWS tells MySQL to calculate the number of rows ignoring the "LIMIT" clause, which allows you to do something like.

SELECT SQL_CALC_FOUND_ROWS * 
FROM `myTable` 
WHERE `title` LIKE "%blah%" 
LIMIT 100,10;

To extract 10 rows starting from the 100th row, and then;

SELECT FOUND_ROWS();

Which will tell you how many rows there are with "blah" in the title field in the whole table, you can then present this info as "Showing 10 rows from 12345 total"

回复收藏 0 原文

~没有更多了~