mysql查询使用另一个查询的每一行

小编典典

mysql查询使用另一个查询的每一行

sql

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

我想通过用户给我一个开始和结束日期来列出用户定义的时间段的总计。从开始日期到开始日期之间的每次总计应该相加，并在每一天添加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 
...

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

阅读 178

2021-04-28

共1个答案

小编典典

下面是示例。想法是获得按日期排序并具有总计，隐式日期范围记录的初始数据集。然后，使用光标，您可以通过总结前面的记录来遍历每一行以获取最终的总计列（示例中的amountCalc列）-之所以起作用，是因为您已经按日期对列进行了排序。

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

祝你好运。

-- 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

2021-04-28