开发者

mySQL recursive Count()

I have an issue regarding an recursive count which works well in SQL server, but I can not make this work in mySQL.

In SQL Server I used to use:

SELECT
@Param= not_unique_id,
(Select Count(not unique id) FROM TABLE0 WHERE not_unique_id=@Param)
FROM Table0 WHERE .....

this will give me:

1 2

1 2

2 1

3 3

3 3

3 3

this will give me the number of id's per current row id as parameter

In mySQL i do the same:

SELECT
Param=not_unique_id,

(returns 开发者_如何学JAVANULL, SET is not allowed here)

(Select Count(not unique id) FROM TABLE0 WHERE not_unique_id=Param)
FROM Table0 WHERE .....

this will give me:

NULL NULL

NULL NULL

NULL NULL

NULL NULL

NULL NULL

NULL NULL

Alternatively, I tried with: In mySQL i do the same:

SELECT
not_unique_id,
(Select Count(not unique id) FROM TABLE0 WHERE not_unique_id=not_unique_id)
FROM Table0 WHERE .....

this will give me:

1 3

1 3

2 3

3 3

3 3

3 3

this will give me the unique count of the whole record

any idea how to do this in mySQL?


For assigning a value in mysql don't forget :=

SELECT @Param:=not_unique_id,
(
 SELECT Count(not_unique_id)
 FROM TABLE0
 WHERE not_unique_id=@Param
)
FROM Table0 WHERE .....

or

SELECT not_unique_id,
(
 SELECT Count(not_unique_id)
 FROM TABLE0
 WHERE not_unique_id=a.not_unique_id
)
FROM Table0 a WHERE .....
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜