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 3this 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 NULLAlternatively, 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 3this 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 .....
精彩评论