获取mySQL存储过程将导致另一个存储过程 [英] Getting mySQL stored procedure results in another stored procedure

问题描述

我需要一个存储过程的示例，该示例使用另外两个存储过程(甚至只是一个)的结果，其中结果用作组合存储过程中的表.我可能会从那里拿走.

I need an example of a stored procedure that uses the results of two another stored procedures (or even just one) where the results are used as tables in the combining stored procedure. I could likely take it from there.

网络上的示例(多个站点)和php.net上的详细信息太简单了.

examples on web (multiple sites) and details at php.net are too simplistic.

基本问题可以在这里看到:

The basic issue can be seen here:

SELECT ttd.person_id, weight, min(test_date)
FROM (
  CALL get_team_member_body_stats(1) AS tbs,   
  CALL get_team_member_first_last_test_date(1) AS ttd
  WHERE tbs.person_id = ttd.person_id
  AND (tbs.test_date = ttd.first_test OR tbs.test_date = ttd.last_test)
  GROUP BY ttd.person_id;
END

感谢您的帮助.昨天和今天，在网上搜索和实验中，我已经奋战了好几个小时.

Thanks for any help. I've beat my head against this for several hours yesterday and today in online searching and experimenting.

详细信息:

存储过程get_team_member_body_stats(IN team_id INT)处理5个表(人，团队，人_队，body_stats，person_body_stats)并产生:

stored procedure get_team_member_body_stats(IN team_id INT) works over 5 tables (person, team, person_team, body_stats, person_body_stats) and produces:

person_id | body_stats_id | weight | test_date |   
  2         2               200      2011-01-01  
  4         3               250      2011-01-01  
  1         5               145      2011-03-01 
  2         6               210.4    2011-03-01 
  5         7               290      2011-03-01 
  1         8               140      2011-04-01 
  1         9               135      2011-05-01 
  4         11              245      2011-05-01 

表2

存储过程get_team_member_first_last_test_date(IN team_id INT)在相同的表上工作并产生:

Table 2

stored procedure get_team_member_first_last_test_date(IN team_id INT) works over the same tables and produces:

person_id | first_test | last_test

1           2011-03-01   2011-05-01
2           2011-01-01   2011-03-01
4           2011-01-01   2011-05-01
5           2011-03-01   2011-03-01

目标是加入这些并产生:

Goal is to join these and produce:

person_id | first_weight | last_weight

1           145            135
2           200            210.4 
4           250            245
5           290            290

谢谢

推荐答案

它似乎出现了(根据这个社区，因为我找不到使用其他存储过程的结果集作为新选择表中的表的存储过程的任何示例) mysql要么不支持，要么很难做到我希望做的事.

It appears (per this community and because I couldn't find any examples of stored procedures using result sets from other stored procedures as tables in new select tables) that mysql either doesn't support this or that it is ridiculously hard to do what I hoped to do.

因此，我没有调用存储过程，而是将查询复制到了此最终过程中.

So rather than calling stored procedures I copied the queries into this final procedure.

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_team_stats_change`(
        IN team_id INT
    )
    BEGIN
    SELECT SUM(start_weight) AS start_weight, 
        SUM(start_body_fat) AS start_body_fat, 
        SUM(current_weight) AS current_weight,
        SUM(current_body_fat) AS current_body_fat,
        SUM(weight_change) AS weight_change, 
        SUM(body_fat_change) AS body_fat_change FROM (
    SELECT ls.person_id, 
        fs.weight AS start_weight, fs.body_fat AS start_body_fat, 
        ls.weight AS current_weight, ls.body_fat AS current_body_fat,
        fs.weight - ls.weight AS weight_change, fs.body_fat - ls.body_fat AS body_fat_change
    FROM
    (SELECT ttd.person_id, bs.weight, bs.body_fat, bs.test_date
    FROM body_stats AS bs
    JOIN
        ((SELECT pbs.person_id,  min(bs.test_date)  AS first_test, max(bs.test_date) AS last_test   
        FROM body_stats AS bs,
            person_body_stats AS pbs,
            team_member AS tm,
            team AS t
        WHERE t.team_id = team_id
        AND tm.team_id = t.team_id
        AND tm.person_id = pbs.person_id
        AND pbs.body_stats_id = bs.body_stats_id
        AND tm.start_date  bs.test_date
        AND bs.test_date >= t.starting_date
        GROUP BY person_id) AS ttd,
        person_body_stats AS pbs)
    ON bs.test_date = ttd.first_test 
        AND pbs.person_id = ttd.person_id 
        AND pbs.body_stats_id = bs.body_stats_id) AS fs,

    (SELECT ttd.person_id, bs.weight, bs.body_fat, bs.test_date
    FROM body_stats AS bs
    JOIN
        ((SELECT pbs.person_id,  min(bs.test_date)  AS first_test, max(bs.test_date) AS last_test   
        FROM body_stats AS bs,
            person_body_stats AS pbs,
            team_member AS tm,
            team AS t
        WHERE t.team_id = team_id
        AND tm.team_id = t.team_id
        AND tm.person_id = pbs.person_id
        AND pbs.body_stats_id = bs.body_stats_id
        AND tm.start_date  bs.test_date
        AND bs.test_date >= t.starting_date
        GROUP BY person_id) AS ttd,
        person_body_stats AS pbs)
    ON bs.test_date = ttd.last_test 
        AND pbs.person_id = ttd.person_id 
        AND pbs.body_stats_id = bs.body_stats_id) AS ls
        WHERE ls.person_id = fs.person_id
       ) AS delta;
    END

这篇关于获取mySQL存储过程将导致另一个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！