首页 > 解决方案 > 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 语句中,它会起作用,但不会显示所有数据。

标签: mysqlsqlstored-procedures

解决方案


我认为您不需要存储过程来执行此操作。只需使用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

dbfiddle 上的演示


推荐阅读