Mysql查询计数器x/y

Question

是否可以创建一个返回x/y记录数量的查询？

例如：我有一张这样的桌子

ID | id_user | id_event  
23 | 3       | 1 
24 | 3       | 1 
25 | 3       | 1 
26 | 4       | 2
27 | 4       | 2

我将返回类似如下的内容：

事件

id_user 3 -> **1/3**
id_user 3 -> **2/3**
id_user 3 -> **3/3**
id_user 4 -> **1/2**
id_user 4 -> **2/2**

欢迎提出任何建议！

Answer 1

尝尝这个

SET @id_event  := 0;
SELECT CONCAT('id_user ', id_user ,'->','**', (@id_event  := @id_event  + 1)  ,'/', id_user ,** ) from table

Answer 2

这可能是this question的副本。

SELECT CONCAT('id_user ',id_user,' -> **',rank,'/',group_total,'**') FROM (
    SELECT id, 
        group_total,
        CASE id_user 
            WHEN @id_user THEN
                CASE id_event 
                    WHEN @id_event THEN @rowno := @rowno + 1 
                    ELSE @rowno := 1
                END
            ELSE @rowno :=1
        END AS rank,
        @id_user := id_user AS id_user,
        @id_event := id_event AS id_event
    FROM event_table
    JOIN (SELECT id_user, id_event, COUNT(*) group_total FROM event_table GROUP BY id_user, id_event) t USING (id_user, id_event)
    JOIN (SELECT @rowno := 0, @id_user := 0, @id_event := 0) r
    ORDER BY id_user, id_event
) c;

Answer 3

假设您想要这样的输出：

id_user < id_user > ** serial number of event related to this user / total events related to this user **

您可以通过以下查询来实现这样的结果：

SELECT 
CONCAT('id_user ',UE.id_user,' -> **',IF(@userID = UE.id_user, @eventNumber := @eventNumber + 1, @eventNumber := 1),'/',t.totalEvents,'**') AS output,
@userID := UE.id_user
FROM (SELECT @userID := -1, @eventNumber := 1) var,user_events UE 
INNER JOIN 
(
    SELECT 
        id_user,
        COUNT(id_event) totalEvents
    FROM user_events
    GROUP BY id_user
) AS t
ON UE.id_user = t.id_user
ORDER BY UE.id_user;

更多信息：

这个特殊的小提琴只返回所需的输出列，而第一个小提琴包含一个额外的列