스토어드 프로시저를 사용하여 합산, 그룹화 및 다른 표에 삽입하기

스토어드 프로시저를 사용하여 합산, 그룹화 및 다른 표에 삽입하기

3개의 테이블을 작성했습니다.item,shop그리고.stock그리고 저장 프로시저라고 불리는inserting에 삽입됩니다.shop에서 소정의 아이템을 포함한 테이블item테이블

CREATE TABLE item(
i_id int(11) auto_increment,
i_name varchar(255) not null,
primary key(i_id));

---

CREATE TABLE shop(
s_id     int(11) auto_increment,
s_name   varchar(255) not null,
s_item   int(11) not null,
s_qty    int(11) not null,
primary  key(s_id),
foreign  key(s_item) references item(i_id)
);

---

CREATE TABLE stock(
item     int(11) not null,
total    int(11) not null
);

---

CREATE PROCEDURE inserting (
IN shop_name varchar(225),
IN shop_item int(11),
IN shop_qty int(11)
)

BEGIN

INSERT INTO shop(s_name, s_item, s_qty) 
VALUES
(shop_name, shop_item, shop_qty);

INSERT INTO STOCK(item, total) 
SELECT s_item, SUM(s_qty) FROM shop GROUP BY s_item
ON DUPLICATE KEY UPDATE
item = VALUES(item),
total = VALUES(total);

첫 번째 삽입은 작동하지만 두 번째 삽입은 다음 삽입에 입력됩니다.stock테이블이 추가 컬럼을 제공합니다. 예상하지 못했던 일이죠.

단일 결과를 얻기 위해 REPLACE INTO 및 ON DUPLICE KEY UPDATE를 사용해 보았지만 결과는 다음과 같습니다.

SELECT * FROM `stock`;
+------+-------+
| ITEM | TOTAL |
+------+-------+
|    1 |     5 |
|    1 |     9 |
+------+-------+

제가 달성하고자 하는 것은 ITEM 열을 그룹화하고 TOTAL을 하나의 행으로 합산하는 것입니다.

제가 여기서 뭘 잘못하고 있는 걸까요? 아니면 쿼리에서 누락되어 있는 걸까요?

감사합니다.

를 위해on duplicate key구문이 예상대로 작동하려면 대상 테이블에 고유 키 또는 기본 키 제약 조건이 있어야 합니다. 그러면 데이터베이스가 "예상대로" 행을 식별할 수 있습니다.에 대해서도 마찬가지입니다.REPLACE구문을 사용합니다.

하지만 당신의stock테이블에 기본 키가 없습니다.대신 다음 DDL을 고려하십시오.

CREATE TABLE stock(
    item     int(11) primary key,
    total    int(11) not null
);

참고: 열을 재할당할 필요가 없습니다.item에서on duplicate key이 조항은 애초에 갈등을 식별하기 위해 사용되는 조항이기 때문입니다.이것으로 충분합니다.

INSERT INTO STOCK(item, total) 
SELECT s_item, SUM(s_qty) FROM shop GROUP BY s_item
ON DUPLICATE KEY UPDATE total = VALUES(total);

이 작업을 한 번 실행하면 예상대로 작동합니다.그러나 다음 실행에서는 중복이 발생할 수 있습니다.ITEM@gmb가 말한 것 때문에.테이블에는 다음이 있어야 합니다.UNIQUE index또는PRIMARY KEY자세한 내용은 이쪽

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

언급URL : https://stackoverflow.com/questions/63182591/using-stored-procedure-to-sum-group-by-and-insert-to-another-table-with