我搜索了很多，但找不到有用的答案:

我想获得用户通过给我开始和结束日期定义的时间段的总计列表。总计应该每次都是从开始日期到开始日期，并且每行添加 1 天。所以最后一行给出了从开始到结束日期的总数。 示例:- 给定期间 = 开始 2013-01-01 ，结束 = 2013-01-31

total day 1 = 100
total day 2 = 0 (not listed in my totalsperday query, but should have a row in my final query)
total day 3 = 140
total day 4 = 20
...

final table should look like:
end day 1: 100
end day 2: 100
end day 3: 240
end day 4: 260
...

所以我有一个计算所有天数的查询:

SELECT '2013-01-01' as startdate, w.endDate
FROM
(
    SELECT date('2013-01-01' + INTERVAL u.i*100 + v.i*10 + w.i DAY) AS endDate
    FROM sysints AS u
    JOIN sysints AS v
    JOIN sysints AS w
    WHERE ( u.i*100 + v.i*10 + w.i ) <= 
    (
        SELECT DATEDIFF( '2013-01-31','2013-01-01') as ddff
    )
) w
ORDER BY w.endDate ASC

我有一个查询谁计算每天的总数

SELECT p.selldate, SUM(p.price) as totalPerDay
FROM products p
WHERE   '2013-01-01' >= p.selldate <= '2013-01-31'
GROUP BY p.selldate
ORDER BY p.selldate ASC

现在很难将这两者结合起来得到我的最终结果。

基本上最终的查询应该是这样的:

- make the sum of sumperday from day 1 to day 1
- make the sum of sumperday from day 1 to day 2
- make the sum of sumperday from day 1 to day 3 
...

有什么帮助吗？ 谢谢。 这是我最终查询的简化示例。

最佳答案

下面是示例。这个想法是获得一个按日期排序的初始数据集，并具有总计、隐式日期范围记录。然后使用游标，您可以通过每一行来获取最终的总计列(示例中的 amountCalc 列)，只需汇总以前的记录 - 这将起作用，因为您已经有了按日期排序的列。

该过程可以有其他输入/输出参数。您可以从一个 View 中获取数据，而不是从表中获取信息，该 View 可以已经按日期升序排序。只是一个示例，因此可以根据需要进行定制。

祝你好运。

-- drop table `Balance`;
CREATE TABLE `Balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `account` varchar(30) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL, 
  PRIMARY KEY (`id`)
);

INSERT INTO `Balance` (`date`, `account`, `amount`) VALUES 
('2013-01-02', 'T355176', 8700), 
('2013-01-03', 'T355176', 8900), 
('2013-01-04', 'T355215', 33308), 
('2013-01-03', 'T355215', 116581), 
('2013-01-06', 'T812022', 275000), 
('2013-01-02', 'T812063', 136500), 
('2013-01-05', 'T812063', 11682), 
('2013-01-06', 'T812064', 615100), 
('2013-01-03', 'T812064', 25000), 
('2013-01-02', 'T812085', 82500);


SELECT * FROM Balance WHERE date >= '2013-01-01' AND date <= '2013-01-06' ORDER BY date ASC;
CALL sp_getTotals('2013-01-01', '2013-01-06');


-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_getTotals`(IN startDate DATE, IN endDate DATE)
BEGIN


DECLARE dt DATE;
DECLARE amt DECIMAL(10,2);
DECLARE amtCalcPart DECIMAL(10,2);
DECLARE done INT DEFAULT 0;

DECLARE dtStart DATE;
DECLARE dtEnd DATE;

DECLARE cur1 CURSOR FOR SELECT date, amount FROM `TempMB`;
DECLARE cur2 CURSOR FOR SELECT startDate, endDate;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


DROP TEMPORARY TABLE IF EXISTS `TempMB`;
CREATE TEMPORARY TABLE IF NOT EXISTS `TempMB` (
`id` int(11) NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `amount` DECIMAL(10,2) NULL DEFAULT 0.00,
  `amountCalc` DECIMAL(10,2) NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
);


SET dtStart = DATE(startDate);
SET dtEnd = DATE(endDate);

WHILE dtStart <= dtEnd DO
    INSERT INTO `TempMB` (`date`) SELECT dtStart;
    SET dtStart = DATE_ADD(dtStart, INTERVAL 1 DAY);
END WHILE;


SELECT * FROM TempMB;

-- Fill temp table with info needed
UPDATE `TempMB` t 
INNER JOIN 
(
    SELECT date, SUM(amount) AS amount
    FROM Balance 
    WHERE 
        date >= startDate AND date <= endDate 
    GROUP BY date
    ORDER BY date ASC
) b ON b.date = t.date 
SET 
    t.amount = b.amount;
/*INSERT INTO `TempMB` (`date`, `amount`)
SELECT date, SUM(amount) AS amount
FROM Balance 
WHERE 
    date >= startDate AND date <= endDate 
GROUP BY date
ORDER BY date ASC;
*/


SET amtCalcPart = 0.00;
-- Initialise cursor
OPEN cur1;
-- USE BEGIN-END handler for cursor-control within own BEGIN-END block
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
-- Loop cursor throu temp records
LOOP 
    -- Get next value
    FETCH cur1 INTO dt, amt;

    -- Calculate amountCalc
    SET amtCalcPart = (SELECT SUM(amount) as amt FROM `TempMB` WHERE Date <= dt);
    UPDATE `TempMB` SET amountCalc = amtCalcPart WHERE date = dt;

END LOOP;
END;

-- Release cursor
CLOSE cur1;

SELECT * FROM TempMB;

END

关于mysql查询使用另一个查询的每一行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14679237/