在mysql中，我有下表'Log'：
id_user cod info
1 A random_info1
1 A random_info1
1 A random_info1
1 A random_info2
1 B random_info2
1 B random_info2
1 B random_info3
1 B random_info3
2 A random_info4
2 A random_info4
2 B random_info5
2 B random_info5
2 B random_info5
2 B random_info6
使用此查询：
SELECT
id_user,
SUM(cod = 'A') as A,
SUM(cod = 'B') as B
FROM
Log
GROUP BY
id_user
我得到每个'id_user'每个'鳕鱼'出现的数量：
id_user A B
1 4 4
2 2 4
现在，加上前面的结果，我需要为每个“鳕鱼”再次出现我们已经看到的“ info”中的值的次数，例如：
id_user A B A_repeated B_repeated
1 4 4 2 2
2 2 4 1 2
id_user ='1'的A_repeated为2，因为random_info1首次出现后出现了两次。
id_user ='1'的B_repeated为2，因为random_info2和random_info3第一次（每个）第一次出现。
要获得这些结果，我需要在查询中进行哪些更改？
干得好 ：）
SELECT
id_user,
SUM(cod = 'A') as A,
SUM(cod = 'B') as B,
SUM(cod = 'A' && is_repeating) AS A_repeating,
SUM(cod = 'B' && is_repeating) AS B_repeating
FROM
(
select
l.*
, @is_repeating := if(@prev_id_user = id_user && @prev_cod = cod && @prev_info = info, 1, 0) as is_repeating
, @prev_id_user := id_user
, @prev_cod := cod
, @prev_info := info
from
Log l
, (select @prev_id_user:=null, @prev_cod:=null, @prev_info:=null, @is_repeating:=0) var_init
order by id_user, cod, id
) Log
GROUP BY
id_user
不过，我需要添加，我添加了按顺序使用的auto_increment列（这很重要！）。就像我在上面的评论中已经说过的那样，除非您指定数据库表的顺序，否则没有任何顺序。没有order by子句，即使看起来像可以，您也不能依靠select的相同顺序。
更新：
看来我误会了你一点。这是一个更正的版本，您期望的A_repeating结果为3：
SELECT
id_user,
SUM(cod = 'A') as A,
SUM(cod = 'B') as B,
SUM(cod = 'A' && is_repeating) AS A_repeating,
SUM(cod = 'B' && is_repeating) AS B_repeating
FROM
(
select
l.*
, @is_repeating := if(@prev_id_user = id_user && @prev_cod = cod && @prev_info = info, 1, 0) as is_repeating
, @prev_id_user := id_user
, @prev_cod := cod
, @prev_info := info
from
Log l
, (select @prev_id_user:=null, @prev_cod:=null, @prev_info:=null, @is_repeating:=0) var_init
order by id_user, cod, info
) Log
GROUP BY
id_user
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句