sql—在mysql中使用其他表列和填充一行的数据

bqucvtff 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(365)

我有三张table
员工：

+----+--------+----------+--------+-----------------+-----+
 | id | E_name | super_id | salary | bdate           | Dno | 
 +----+--------+----------+--------+-----------------+-----+
 | 1  | John   | 3        | 100000 | Fri Jan 01 1960 | 1   | 
 | 2  | Mary   | 3        | 100000 | Tue Dec 01 1964 | 3   | 
 | 3  | Bob    | null     | 100000 | Thu Feb 07 1974 | 3   | 
 | 4  | Tom    | 1        | 100000 | Tue Jan 17 1978 | 2   | 
 | 5  | Bill   | null     | 100000 | Thu Jan 17 1985 | 1   | 
 +----+--------+----------+--------+-----------------+-----+

部门：

+-----+--------------+
 | Dno | dname        | 
 +-----+--------------+
 | 1   | Payroll      | 
 | 2   | Tech support | 
 | 3   | Research     | 
 +-----+--------------+

部门：

+-----+-------------+
 | dno | totalsalary | 
 +-----+-------------+
 | 1   | 0           | 
 | 2   | 0           | 
 | 3   | 0           | 
 +-----+-------------+

deptsal表包含按部门划分的工资与dno之和。作为引用部门表的外键。。
我得用mysql写两个程序
我必须编写一个更新部门总工资的过程，该部门的dno在deptsal表中的过程参数中传递
我试过了

DELIMITER $$
CREATE PROCEDURE `updateSalary` (IN dno INT)
BEGIN
    UPDATE deptsal
    SET totalsalary = (
                    SELECT SUM(employee.salary) 
                    FROM employee 
                    WHERE employee.dno = @dno 
                    GROUP BY employee.dno
                )
    where deptsal.dno = dno;
END$$
DELIMITER ;

但这似乎不管用
我还需要编写一个程序来更新整个deptsal表
下面是我的create table查询

CREATE TABLE department (
    Dno INT(6) PRIMARY KEY,
    dname VARCHAR(25)
);

CREATE TABLE employee (
    id INT(6) PRIMARY KEY,
    E_name VARCHAR(25),
    super_id INT(6), 
    salary INT(10),
    bdate DATE,
    Dno INT(6)  
);
ALTER TABLE employee
ADD FOREIGN KEY (Dno) REFERENCES department(Dno);

CREATE TABLE deptsal (
    dno INT(6) PRIMARY KEY,
    totalsalary INT(10)
);
ALTER TABLE deptsal
ADD FOREIGN KEY (Dno) REFERENCES department(Dno);

sql mysql Database

来源：https://stackoverflow.com/questions/61986735/fill-data-of-one-row-using-other-table-column-sum-in-mysql

1条答案

按热度按时间

ws51t4hk1#

@dno和dno是两个独立的符号

DELIMITER $$ 
CREATE PROCEDURE `updateSalary` (IN dno INT)
BEGIN
    UPDATE deptsal
    SET totalsalary = (
                    SELECT SUM(employee.salary) 
                    FROM employee 
                    WHERE employee.dno = dno 
                    GROUP BY employee.dno
                )
    where deptsal.dno = dno;
END$$
DELIMITER ;

END$$
DELIMITER ;

若要全部更新，可以修改过程，若要在dno=-1时全部更新
或者写第二个程序

DELIMITER $$ 
CREATE PROCEDURE `updateSalaryAll` ()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE _dno INTEGER DEFAULT -1;

    -- declare cursor for department number
    DEClARE curdno 
        CURSOR FOR 
            SELECT dno FROM deptsal;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

    OPEN curdno;
    getdno: LOOP
        FETCH curdno INTO _dno;
        IF finished = 1 THEN 
            LEAVE getdno;
        END IF;
        UPDATE deptsal
        SET totalsalary = (
                        SELECT SUM(employee.salary) 
                        FROM employee 
                        WHERE employee.dno = _dno 
                        GROUP BY employee.dno
                    )
        where deptsal.dno = _dno;
    END LOOP getdno;
    CLOSE curdno;        
END$$
DELIMITER ;

赞(0）回复(0）举报 2021-07-26

我来回答

sql—在mysql中使用其他表列和填充一行的数据

1条答案

相关问题

热门标签

最新问答