Here's my SELECT
query:
这是我选择查询:
SELECT col1, col3, col5, col8
FROM Table1
In my SELECT
query, I want to perform a COUNT(*) exclusively for the current row.
在SELECT查询中,我希望为当前行执行一个COUNT(*)。
I want something like this, but have no idea how I can get this:
我想要这样的东西,但不知道我怎么能得到这个:
SELECT col1, col3,
(
SELECT COUNT(*)
FROM table1 WHERE col3 = col3 value for current row
),
col5, col8
FROM Table1
What is the correct way to perform a COUNT(*) for the current row of a SELECT
query resultset?
对于选择查询结果集的当前行执行计数(*)的正确方法是什么?
3 个解决方案
#1
2
try this:
试试这个:
set @num := 0;
SELECT @num := @num+1 , col1, col3, col5, col8 FROM Table1
or other way:
或其他方式:
SET @num := ( SELECT COUNT( * ) FROM Table1) ;
SELECT @num := @num -1 , col1, col3, col5, col8
FROM Table1
#2
1
SELECT col1, col3,
(
SELECT COUNT(*)
FROM table1 WHERE id = A.Id
) Count,
col5, col8
FROM Table1 A
#3
1
Found it.
找到了。
If I write my query like this:
如果我这样写我的查询:
SELECT col1, col3,
COUNT(*) AS count,
col5, col8
FROM Table1
then I get COUNT of all the items in Table1 for all rows.
然后计算表1中所有行的所有项。
I actually wanted COUNT of all items for selective rows.
实际上,我想要为选择行计算所有项的数量。
Like I may have col3 value appearing more than once. I actually want the count of all items for each col3.
就像我可能有col3值不止一次出现一样。实际上,我想要每个col3的所有项的计数。
So, GROUP BY
clause is the solution.
所以,GROUP BY子句是解。
SELECT col1, col3,
COUNT(*) AS Count,
col5, col8
FROM Table1
GROUP BY col3
Sorry, I think I wasn't able to explain my problem properly, so it confused a lot of people.. A very poor question. My bad.
对不起,我想我不能正确地解释我的问题,所以它使很多人困惑。一个很差的问题。我的坏。
#1
2
try this:
试试这个:
set @num := 0;
SELECT @num := @num+1 , col1, col3, col5, col8 FROM Table1
or other way:
或其他方式:
SET @num := ( SELECT COUNT( * ) FROM Table1) ;
SELECT @num := @num -1 , col1, col3, col5, col8
FROM Table1
#2
1
SELECT col1, col3,
(
SELECT COUNT(*)
FROM table1 WHERE id = A.Id
) Count,
col5, col8
FROM Table1 A
#3
1
Found it.
找到了。
If I write my query like this:
如果我这样写我的查询:
SELECT col1, col3,
COUNT(*) AS count,
col5, col8
FROM Table1
then I get COUNT of all the items in Table1 for all rows.
然后计算表1中所有行的所有项。
I actually wanted COUNT of all items for selective rows.
实际上,我想要为选择行计算所有项的数量。
Like I may have col3 value appearing more than once. I actually want the count of all items for each col3.
就像我可能有col3值不止一次出现一样。实际上,我想要每个col3的所有项的计数。
So, GROUP BY
clause is the solution.
所以,GROUP BY子句是解。
SELECT col1, col3,
COUNT(*) AS Count,
col5, col8
FROM Table1
GROUP BY col3
Sorry, I think I wasn't able to explain my problem properly, so it confused a lot of people.. A very poor question. My bad.
对不起,我想我不能正确地解释我的问题,所以它使很多人困惑。一个很差的问题。我的坏。