子查询返回的mysql多行

问题描述：

任何一个可以告诉你与我的日常去错了，这是我自己编写子查询返回的mysql多行

常规DDL：

DELIMITER $$ 

CREATE DEFINER=`root`@`%` PROCEDURE `uspEmployeeBankDataUpdate`(_EmpID int, 
       _PayeeName varchar(20), 
       _BankRoutingNumber varchar(9), 
       _BankTypeID varchar(5), 
       _AccountType varchar(2), 
       _BankAccount int, 
     _Amount DOUBLE, 
       _Comments varchar(50), 
       _Address1 varchar(30), 
       _Address2 varchar(30), 
       _ZipCode DECIMAL(9,0), 
       _City varchar(25), 
       _StateName VARCHAR(30), 
       _Country varchar(20), 
     _BankAccountType varchar(30), 
     _EndDate datetime) 
BEGIN 

declare p_ecount int; 

declare _startdate Date; 

set _startdate=(select date(startdate) from tblEmployeeBankData where 
      EmpId=_EmpId and 
      EndDate='9999-12-31'); 


    set p_ecount=(select count(1) from tblEmployeeBankData where 

    PayeeName=_PayeeName and 


    BankRoutingNumber=_BankRoutingNumber and 

BankTypeID=_BankTypeID and 
AccountType=_AccountType and 
BankAccount=_BankAccount and 
Amount=_Amount and 
Comments=_Comments and 
Address1=_Address1 and 
Address2=_Address2 and 
ZipCode=_ZipCode and 
City=_City and 
StateName=_StateName and 
Country=_Country and 
BankAccountType=_BankAccountType and 
EndDate='9999-12-31'); 

if p_ecount=0 and _startdate<curdate() then   
    begin   
    update tblEmployeeBankData set EndDate=_EndDate 
    where EmpID=_EmpID and EndDate="9999-12-31";  
    end; 
    end if; 

    END

这是通过

我的示例代码

m_oCmd.Parameters.AddWithValue("_EmpID", EmpID); 
      m_oCmd.Parameters.AddWithValue("_PayeeName", PayeeName); 
      m_oCmd.Parameters.AddWithValue("_BankTypeID", BankTypeID); 
      m_oCmd.Parameters.AddWithValue("_AccountType", AccountType); 
      m_oCmd.Parameters.AddWithValue("_BankRoutingNumber", BankRoutingNumber); 
      m_oCmd.Parameters.AddWithValue("_BankAccountType", BankAccountType); 
      m_oCmd.Parameters.AddWithValue("_BankAccount", BankAccount); 
      m_oCmd.Parameters.AddWithValue("_Amount", Amount); 
      m_oCmd.Parameters.AddWithValue("_Comments", Comments); 
      m_oCmd.Parameters.AddWithValue("_Address1", Address1); 
      m_oCmd.Parameters.AddWithValue("_Address2", Address2); 
      m_oCmd.Parameters.AddWithValue("_ZipCode", ZipCode); 
      m_oCmd.Parameters.AddWithValue("_City", City); 
      m_oCmd.Parameters.AddWithValue("_StateName", StateName); 
      m_oCmd.Parameters.AddWithValue("_Country", Country); 
      m_oCmd.Parameters.AddWithValue("_EndDate", EndDate);

该员工ID和结束日期是否有多行？ – 2011-02-19 03:27:26

您是否收到错误？或者输出不好？请描述问题症状。 – 2011-02-19 03:28:30

答

的错误是在这里

set _startdate=(select date(startdate) from tblEmployeeBankData where 
      EmpId=_EmpId and 
      EndDate='9999-12-31');

如果条件EmpId=_EmpId and EndDate='9999-12-31'产生多条记录，则失败。使用LIMIT和ORDER BY选择一个特定的记录（startdate），例如

set _startdate=(select date(startdate) from tblEmployeeBankData where 
      EmpId=_EmpId and 
      EndDate='9999-12-31' 
      ORDER BY startdate DESC 
      LIMIT 1);

子查询返回的mysql多行

相关推荐