mysql - SQL 过程 - 多值
问题描述
我有这个程序:
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE `id_var` varchar(255);
DECLARE `cur1` CURSOR FOR
SELECT `id` FROM `clients`
WHERE `status` = 'Active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS `tblquota_nc`;
CREATE TABLE IF NOT EXISTS `tblquota_nc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`email` varchar(255),
`pack_id` int(11) NOT NULL,
`pack_name` varchar(255) NOT NULL,
`quota` int(11) NULL,
PRIMARY KEY (`id`)
);
OPEN cur1;
read_loop: LOOP
FETCH NEXT
FROM cur1
INTO id_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
FROM `clients`
INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
WHERE clients.status = 'Active'
AND tblhosting.domainstatus = 'Active'
AND clients.id = id_var;
IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
BEGIN
END;
ELSE
BEGIN
if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
end if;
END;
END IF;
END LOOP;
CLOSE cur1;
END
似乎我有一个错误,因为该SELECT
语句返回了几个值。我想用这些结果做另一个循环来插入新表。我想根据这些信息制作一个新表。
表客户:
id | email | status
----------------------------
1 | user1@mail.com | Active
2 | user2@mail.com | Inactive
3 | user3@mail.com | Active
表tblhosting
id | userid | packageid | domainstatus
------------------------------------------------
1 | 1 | 2 | Active
2 | 2 | 3 | Active
3 | 3 | 1 | Active
表tblproducts
id | name
-----------
1 | Basic
2 | Silver
3 | Gold
我期望结果如下:
id | user_id | email | pack_id | pack_name | quota
-----------------------------------------------------------
1 | 1 | user1@mail.com | 2 | Silver | 100
2 | 2 | user2@mail.com | 3 | Gold | 1000
3 | 3 | user3@mail.com | 1 | Basic | 10
如果我将 max 放在 case 语句中,它会起作用,但不会显示所有数据。
解决方案
我认为您不需要存储过程来执行此操作。只需使用CREATE TABLE ... SELECT
语法:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
输出SELECT * FROM tblquota_nc
:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
推荐阅读
- python - 同时运行多个函数(线程)
- flutter - 如何在 Flutter 的 Futures 中使用 Futures?
- r - 如何使用 r 中的 wordcloud2 包将 wordcloud 保存为 .png?
- visual-studio - 我可以从现有 C 代码创建 Visual Studio 2019 项目吗?
- excel - 如何以 12:07 而不是 12:7 格式显示时间?
- python - Txt 到 csv 格式的行和列 [python]
- git - 为什么 git repack 在“filter-repo”中失败?
- android - 片段变化后失去焦点(ANDROID TV)
- php - 如何在准备好的语句中执行多个查询?
- javascript - XMLHttpRequest 返回未定义