个人中心
gpt4 book ai didi

mysql - 如何在选择或表格的每一行中执行过程

转载 作者：行者123 更新时间：2023-11-30 22:32:17 24 4
gpt4 key购买 nike

<分区>

我正在尝试分析 NBA 球队的赛程，我需要在这个查询的每一行上运行一个过程 ...

SELECT
    t.teamname AS team,
    opp.teamname AS opponent, 
    cat.cityname AS at,
    COUNT( * ) AS GamesInCity,
    ( COUNT( * )/ @daysinseason ) AS Pct
FROM schedules s
    INNER JOIN teams t   ON s.teamid     = t.teamid
    INNER JOIN teams opp ON s.oppteamid  = opp.teamid
    INNER JOIN teams at  ON s.hometeamid = at.teamid
    INNER JOIN city c    ON t.cityid     = c.cityid
    INNER JOIN city copp ON opp.cityid   = copp.cityid
    INNER JOIN city cat  ON at.cityid    = cat.cityid
WHERE
    t.teamname = @team
GROUP BY
    cat.cityname

或者将查询输出为最终如下所示的 tmp 表:

enter image description here

我知道游标并不理想，那么解决这个问题的最佳方法是什么，或者我该怎么做？是这样的吗？

SELECT
    t.teamname AS team,
    opp.teamname AS opponent, 
    cat.cityname AS at,
    COUNT( * ) AS GamesInCity,
    ( COUNT( * )/ @daysinseason ) AS Pct, 
    --
    CALL name_of_proc(IN @var, IN (COUNT( * )/ @daysinseason), OUT @out)
    --
FROM
    schedules s
    INNER JOIN teams t   ON s.teamid     = t.teamid
    INNER JOIN teams opp ON s.oppteamid  = opp.teamid
    INNER JOIN teams at  ON s.hometeamid = at.teamid
    INNER JOIN city c    ON t.cityid     = c.cityid
    INNER JOIN city copp ON opp.cityid   = copp.cityid
    INNER JOIN city cat  ON at.cityid    = cat.cityid
WHERE
    t.teamname = @team
GROUP BY
    cat.cityname

注意:我必须在我调用的过程中使用 pct 列。

谢谢。

更新:程序是:

SET @team = 'Atlanta Hawks';
SET @daysinseason = 199;
SET @salary = 10000000;
SET @country = 'US';
SET @state = 'NY';
SET @filer= 'SINGLE';

CALL calc_state_tax(@salary, @state, @filer, @staxesDue);

CREATE PROCEDURE `calc_state_tax`(IN `pSalary` DECIMAL(12,2), IN `pStateCode` VARCHAR(2), IN `pFiler` VARCHAR(6), OUT `taxesDue` DECIMAL(9,2)
    )
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN
    DECLARE taxRate DECIMAL(9,4);
    DECLARE taxExempt DECIMAL(7,2);
    DECLARE deduction DECIMAL(7,2);

    SELECT
        s1.taxrate,
        s1.exemption,
        s1.stddeduction
        INTO taxRate, taxExempt, deduction
    FROM statetax s1
    WHERE s1.statecode = pStateCode  
        AND s1.filer = pFiler 
        AND s1.bracket = (
        SELECT MAX( s2.bracket) 
        FROM statetax s2
        WHERE s1.statecode = s2.statecode
        AND s1.filer = s2.filer
        AND s2.bracket < pSalary);

    SET taxesDue = (((pSalary - deduction) * taxRate));

    END

24 4 0
文章推荐： c# - html选择列表用c#添加值
文章推荐： c# - 比较两个文件夹 - 模板与实际
文章推荐： c# - 多线程丛林中的全局异常处理 - 故障安全/重启
文章推荐： php - json.stringify 转换为数组 php 不插入 mysql
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com