mysql 一些问题

1、在存储过程中提示1172错误：Result consisted of more than one row

这个原因是：在过程中有赋给变量值时，符合条件的记有多条

eg:

declare  vName varchar(20);

select username into vName from sys_user where firstName like '%Lee%';

 

另附存储过程：

DELIMITER $$

 

USE `test`$$

DROP PROCEDURE IF EXISTS `ttt`$$

CREATE DEFINER=`root`@`%` PROCEDURE `ttt`()
BEGIN
DECLARE v_CarrierID INT;
DECLARE v_SCode VARCHAR(20);
DECLARE v_SSize VARCHAR(12);
DECLARE v_dQty INT;
DECLARE vStop SMALLINT DEFAULT 0;
DECLARE  cur_CarrierID CURSOR FOR
  SELECT sp.CarrierCorpID FROM shipping sp WHERE sp.Shipping_Status=1 GROUP BY sp.CarrierCorpID;
 
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET vStop=1;  
 
  OPEN cur_CarrierID;
  FETCH NEXT FROM cur_CarrierID INTO v_CarrierID;
  
   WHILE vStop<>1 DO  
     SELECT sd.SCode,sd.SSize,SUM(sd.Detail_Qty) AS dQty
      INTO v_SCode,v_SSize,v_dQty
      FROM shipping sp
     LEFT JOIN Shipping_Detail sd
     ON sd.Shipping_Code=sp.Shipping_Code
     WHERE sp.Shipping_Status=1 AND sp.CarrierCorpID=v_CarrierID and limit=1
     GROUP BY sd.SCode,sd.SSize;
        
     
     FETCH NEXT FROM cur_CarrierID   INTO v_CarrierID;
   END WHILE;
  
   CLOSE cur_CarrierID;
  
   END$$

DELIMITER ;

 

2、当2个表需要有多个字段关联时在mysql中可以用using 来关联

  eg: select a.*,b.* from a left join b on using (col1,col2,col3,col4)

这里的col1,col2,col3,col4需要在2个表中都存在。

转载于:https://www.cnblogs.com/jing_lee/archive/2010/09/19/1831105.html