mysql查询使用另一个查询的每一行 [英] mysql query uses every row of another query

问题描述

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

我想获得用户定义的期间(通过给我一个开始日期和结束日期)得出的总计列表.从开始日期到开始日期之间的每次总计应该相加，并在每一天添加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列)-之所以起作用，是因为您已经按日期对列进行了排序.

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

祝你好运.

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

I have searched a lot, but cannot find a helpful answer:

i want to have a list of totals from a period the user defines by giving me a start and end date. The totals should every time being from the start date to beginning with the start date and add every row 1 day. so the last row gives the totals from start to end date. example: - given period = start 2013-01-01 , end = 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
...

so i have a query who calculates all days:

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

and i have a query who calculates the totals per day

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

now combining these two to get my final result is hard.

basically what the final query should look like is:

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

any help? thx. this is a simplified example of my final query.

解决方案

Below is the sample. The idea is to obtain a initial data set ordered by date and having aggregate totals, implicit date range records. Then using the cursor you can pass through each row to get the final total column (amountCalc column in the sample) just by summarize the previous records - will work because you already have the columns ordered by date.

The procedure can have other input/ output parameters. Instead of getting info from table you can get data from one view, where the view can be already order by date asc. Is just a sample so can be customized as needed.

Good luck.

-- 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查询使用另一个查询的每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！