mysql 用户变量赋值与 count(*) [英] mysql user variable assignement with count(*)

问题描述

我尝试使用用户变量进行查询，但我不明白为什么，我无法更新查询中的变量.我想总结每天的分组结果来做一个图表.真的很简单.

I try to do query with a user variable, but I don't understand why, i can't update the variable in the query. I want to sum the grouped result for each day to do a chart. That really simple.

SET @total := 0;
SELECT 
    "total Register",
    li.registerDate,
    @total := COUNT(*) + @total as registerNumber,
    @total 
    FROM calendar c3
    INNER JOIN (
            SELECT 
            c2.ide_calendar as ide_calendar,
            c2.name,
            DATE_FORMAT(i.date_creation, "%Y-%m-%d") as registerDate
            FROM calendrar c2
            LEFT JOIN inscription i ON i.ide_calendrar = c2.id
    ) li ON li.ide_calendrar = c3.id
    WHERE c3.id = 9291
    GROUP BY registerDate

这真的很奇怪.我正在查看 mysql 文档，但没有任何效果.

That really strange. I looking mysql documentation, and nothing works.

我想统计一些每天的注册总数.

EDIT : I want for some statistics to have for each day the total number of register.

Day 1 : 2 register then total = 2
Day 2 : 1 register then total = 3.

我以前用过，没有问题.

I used it by the past, and had no problem.

查询的实际数据结果示例.

EDIT 2 : Exemple of the actual data result about the query.

total Inscription   |   2017-07-10  |   2
total Inscription   |   2020-04-20  |   2
total Inscription   |   2020-04-21  |   3
total Inscription   |   2020-06-17  |   4
total Inscription   |   2020-06-18  |   2

我想要的是:

total Inscription   |   2017-07-10  |   2
total Inscription   |   2020-04-20  |   4
total Inscription   |   2020-04-21  |   7
total Inscription   |   2020-06-17  |   11
total Inscription   |   2020-06-18  |   13

这就是我使用变量的原因.在查询中使用更简单.但这一次，我不明白.

That's why I used variables. It's more simple to use on query. But this time, i don't understand.

非常感谢.

推荐答案

正如@JagaSrik 所说，需要更多有关您的数据和表格的信息才能给出正确答案.但是，如果您需要像这样更改表格:

As @JagaSrik said, there is a need to more information about your data and tables to give right answer. However, if you need to change a table like this:

name                |     date      |  id
-----------------------------------------
total Inscription   |   2017-07-10  |   2
total Inscription   |   2020-04-20  |   2
total Inscription   |   2020-04-21  |   3
total Inscription   |   2020-06-17  |   4
total Inscription   |   2020-06-18  |   2

像这样:

     date      |  total
-----------------------------------------
   2017-07-10  |   2
   2020-04-20  |   4
   2020-04-21  |   7
   2020-06-17  |   11
   2020-06-18  |   13

您可以使用CURSOR.

对于这个例子:

DELIMITER $$
CREATE PROCEDURE `countIds`()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE total INTEGER DEFAULT 0;
    DECLARE theCount INTEGER DEFAULT 0;
    DECLARE theDate varchar(100) DEFAULT "";    

        DEClARE curCount 
        CURSOR FOR 
            SELECT `id`,`date` FROM table1;

        DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;
        
    OPEN curCount;

    getCount: LOOP
        FETCH curCount INTO theCcount,theDate;
        IF finished = 1 THEN 
            LEAVE getCount;
        END IF;
        SET total = total + theCount;
        select theDate, total;
    END LOOP getCount;
    CLOSE curCount;       

END$$
DELIMITER ;

尽管如此，CURSOR 是完成工作的一种方式，并且可能有更有效的方法.

Nevertheless CURSOR is a way to do your work and there may be more effective methods.

这篇关于mysql 用户变量赋值与 count(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！