Subquery returns more than 1 row问题的解决思路

今天遇到系统异常的问题。
子查询多了一行,众所周知独我不知这个子查询的查询结果是只能有一行。那么现在知道了我们就根据报错提示找到相应的mapper.xml文件的那个sql语句里纠错。那么我们开始找这条语句的子查询的问题首先:

SELECT '' AS bugID,A.`SUMMARY` AS `describe`,
	(SELECT CONCAT(last_name ,first_name) FROM cwd_user WHERE user_name = A.`ASSIGNEE`) AS liablePerson,
	(SELECT CONCAT(last_name , first_name) FROM cwd_user WHERE user_name = A.`REPORTER`)  AS testLiablePerson ,
	c.`pname` AS `status`,
	(SELECT email_address FROM cwd_user WHERE user_name = A.`REPORTER`)  AS emailAddressTest ,
	(SELECT email_address FROM cwd_user WHERE user_name = A.`ASSIGNEE`)  AS emailAddressDev
  	FROM `jiraissue` a, project b, issuestatus c
  	WHERE 
	a.`PROJECT` = b.`ID` AND a.`issuestatus` = c.`ID`
	AND a.issuenum = #{bugID,jdbcType=VARCHAR}
	AND b.`pkey` = #{projectName,jdbcType=VARCHAR}
看得出这里有四个子查询,哪个出了问题呢(CONCAT是把查出来的firstname和lastname连接)然后我们先把select的条件变成*查出来到底要啥。得到结果我们根据assignee和reporter的结果分别将子查询查一下发现根据报错的条件查发现是第二条有重复,怎么办呢,这四条都会出现这个问题,所以我们优化一下在子查询上加个limit条件只用子查询结果的第一条就可以了。龟龟就是这样,再见。
阅读更多
换一批

错误#1242 - Subquery returns more than 1 row问题

09-29

这个超级长的sql,报了一个 Subquery returns more than 1 row的错,水平太次,看不出来是哪个地方,大神帮忙看一下rnrn[code=sql]rnSELECTrn tab5.ID,rn tab5.LE_CODE,rn tab5.LE_NAME,rn tab5.US_ID,rn ROUND(tab5.STUDYTIME / 60, 2) AS STUDYTIME,rn tab5.STUDYPLAN,rn tab5.EXAM_ID,rn tab5.PASS_MARK,rn tab5.MARK,rn tab5.mainid,rn tab6.REGNAME,rn tab6. NAME,rn tab6.TC_ID,rn tab6.GROUPNAME,rn tab6.LE_ID AS MAIN_LE_ID,rn tab6.ID AS TUSER_IDrnFROMrn (rn SELECTrn tab2.TC_ID,rn tab2.ID,rn tab2.REGNAME,rn tab2. NAME,rn tab2.GROUPNAME,rn tab2.GROUP_ID,rn le.ID AS LE_IDrn FROMrn (rn SELECT DISTINCTrn tab1.TC_ID,rn tab1.ID,rn tab1.REGNAME,rn tab1. NAME,rn tab1.GROUPNAME,rn tab1.GROUP_IDrn FROMrn (rn SELECTrn tcu.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tp. NAME AS GROUPNAME,rn tp.ID AS GROUP_IDrn FROMrn trainingclass_user tcu,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tprn WHERErn tcu.TU_ID = tu.IDrn AND tu. STATUS = 1rn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tcu.TC_IDrn AND tg.USER_ID = tu.IDrn AND tp.ID = tg.GROUP_IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tp. NAME AS GROUPNAME,rn tp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn user_station us,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tprn WHERErn tau.TYPE = 1rn AND tau.ENTITYID = us.ST_IDrn AND us.US_ID = tu.IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.USER_ID = tu.IDrn AND tp.ID = tg.GROUP_IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tgp. NAME AS GROUPNAME,rn tgp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn tuser_group tg,rn tuser tu,rn training_class tc,rn tgroup tgprn WHERErn tau.TYPE = 2rn AND tau.ENTITYID = tg.GROUP_IDrn AND tg.USER_ID = tu.IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.GROUP_ID = tgp.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tgp. NAME AS GROUPNAME,rn tgp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tgprn WHERErn tau.TYPE = 3rn AND tau.ENTITYID = tu.businessrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.USER_ID = tu.IDrn AND tgp.ID = tg.GROUP_IDrn ) AS tab1rn WHERErn tab1.ID NOT IN (rn SELECTrn tue.TU_IDrn FROMrn tc_user_except tue,rn training_class tcrn WHERErn tc.ID = tue.TC_IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tue.flag = 0rn )rn ) tab2,rn TGROUP_TRAININGCLASS tt,rn tgroup tgp,rn trainingclass_lesson tl,rn lesson lern WHERErn tab2.TC_ID = tt.TC_IDrn AND tgp.ID = tt.TG_IDrn AND tgp.ID IN (rn +'userid'+rn )rn AND tab2.TC_ID = tl.TC_IDrn AND le.ID = tl.LE_IDrn AND le. STATUS = 1rn ) tab6rnLEFT JOIN (rn SELECTrn tab4.ID,rn tab4.LE_CODE,rn tab4.LE_NAME,rn tab4.US_ID,rn tab4.STUDYTIME,rn tab4.STUDYPLAN,rn tab4.EXAM_ID,rn tab4.PASS_MARK,rn IFNULL(rn (rn SELECT DISTINCTrn em.MARKrn FROMrn exam_mark em,rn exam_stu_paper esprn WHERErn em.STU_ID = tab4.US_IDrn AND em.EXAM_ID = esp.EXAM_IDrn AND esp.STU_ID = tab4.US_IDrn AND em.EXAM_ID = tab4.EXAM_IDrn ),rn 0rn ) AS MARK,rn IFNULL(rn (rn SELECT DISTINCTrn il.mainidrn FROMrn integral ilrn WHERErn il.usid = tab4.US_IDrn AND il.mainid = tab4.IDrn ) ,- 1rn ) AS mainidrn FROMrn (rn SELECT DISTINCTrn tab2.ID,rn tab2.LE_CODE,rn tab2.LE_NAME,rn tab2.US_ID,rn IFNULL(rn (rn SELECT DISTINCTrn uss.STUDYTIMErn FROMrn userstudystatistics ussrn WHERErn uss.LE_ID = tab2.IDrn AND uss.US_ID = tab2.US_IDrn ),rn 0rn ) AS STUDYTIME,rn IFNULL(rn (rn SELECT DISTINCTrn uli.STUDYPLANrn FROMrn usertlidstudyinfo ulirn WHERErn uli.LE_ID = tab2.IDrn AND uli.US_ID = tab2.US_IDrn ),rn 0rn ) AS STUDYPLAN,rn IFNULL(tab3.exam_id, 0) AS EXAM_ID,rn IFNULL(tab3.PASS_MARK, 0) AS PASS_MARKrn FROMrn (rn SELECT DISTINCTrn le.ID,rn le.LE_CODE,rn le.LE_NAME,rn sl.US_IDrn FROMrn selectedlesson sl,rn lesson lern WHERErn sl.LE_ID = le.IDrn AND sl.TYPE = 0rn AND le. STATUS = 1rn UNIONrn SELECTrn le.ID,rn le.LE_CODE,rn le.LE_NAME,rn tab1.US_IDrn FROMrn (rn SELECTrn tcu.TC_ID,rn tu.ID AS US_ID,rn tu.REGNAME,rn tu. NAMErn FROMrn trainingclass_user tcu,rn tuser turn WHERErn tcu.TU_ID = tu.IDrn AND tu. STATUS = 1rn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn user_station us,rn tuser turn WHERErn tau.TYPE = 1rn AND tau.ENTITYID = us.ST_IDrn AND us.US_ID = tu.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn tuser_group tg,rn tuser turn WHERErn tau.TYPE = 2rn AND tau.ENTITYID = tg.GROUP_IDrn AND tg.USER_ID = tu.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn tuser turn WHERErn tau.TYPE = 3rn AND tau.ENTITYID = tu.businessrn ) tab1,rn training_class tc,rn trainingclass_lesson tl,rn lesson lern WHERErn tab1.TC_ID = tc.IDrn AND tl.TC_ID = tc.IDrn AND le.ID = tl.LE_IDrn AND tc. STATUS = 1rn AND le. STATUS = 1rn UNIONrn SELECTrn sl.LESSION_ID AS ID,rn le.LE_CODE,rn le.LE_NAME,rn tab1.US_IDrn FROMrn (rn SELECTrn us.ID,rn us.US_ID,rn us.ST_ID,rn sn. NAMErn FROMrn user_station us,rn station snrn WHERErn us.ST_ID = sn.IDrn ) tab1,rn station_lesson sl,rn lesson lern WHERErn sl.STATION_ID = tab1.ST_IDrn AND sl.LESSION_ID = le.IDrn AND le. STATUS = 1rn ) tab2rn LEFT JOIN (rn SELECTrn ei.OUTER_ID,rn ei.ID AS EXAM_ID,rn ei.PASS_MARKrn FROMrn exam_info eirn WHERErn ei.OUTER_TYPE = 1rn ) tab3 ON tab2.ID = tab3.OUTER_IDrn ) tab4rn) tab5 ON tab5.US_ID = tab6.IDrnWHERErn tab6.LE_ID = tab5.IDrnORDER BYrn tab6. NAME,rn tab6.ID,rn tab5.LE_CODErnrn[/code]rnrnrn

出现错误:Subquery returns more than 1 row

05-11

这是一个进销存的出入库项目部分,现在问题不知道出在哪里,请各位大虾帮帮忙,帮忙看看rnrn```rn java.sql.SQLException: Subquery returns more than 1 rowrn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)rn at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)rn at com.sxt.gmms.dao.instorage.InStorageDao.addInStorageAndItem(InStorageDao.java:40)rn at com.sxt.gmms.frame.instorage.instorage.InStorageFrame.actionPerformed(InStorageFrame.java:233)rn at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)rn at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)rn at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)rn at javax.swing.DefaultButtonModel.setPressed(Unknown Source)rn at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)rn at java.awt.Component.processMouseEvent(Unknown Source)rn at javax.swing.JComponent.processMouseEvent(Unknown Source)rn at java.awt.Component.processEvent(Unknown Source)rn at java.awt.Container.processEvent(Unknown Source)rn at java.awt.Component.dispatchEventImpl(Unknown Source)rn at java.awt.Container.dispatchEventImpl(Unknown Source)rn at java.awt.Component.dispatchEvent(Unknown Source)rn at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)rn at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)rn at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)rn at java.awt.Container.dispatchEventImpl(Unknown Source)rn at java.awt.Window.dispatchEventImpl(Unknown Source)rn at java.awt.Component.dispatchEvent(Unknown Source)rn at java.awt.EventQueue.dispatchEventImpl(Unknown Source)rn at java.awt.EventQueue.access$200(Unknown Source)rn at java.awt.EventQueue$3.run(Unknown Source)rn at java.awt.EventQueue$3.run(Unknown Source)rn at java.security.AccessController.doPrivileged(Native Method)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.awt.EventQueue$4.run(Unknown Source)rn at java.awt.EventQueue$4.run(Unknown Source)rn at java.security.AccessController.doPrivileged(Native Method)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.awt.EventQueue.dispatchEvent(Unknown Source)rn at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)rn at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)rn at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)rn at java.awt.EventDispatchThread.pumpEvents(Unknown Source)rn at java.awt.EventDispatchThread.pumpEvents(Unknown Source)rn at java.awt.EventDispatchThread.run(Unknown Source)rnrnrn这是项目的源代码rn/**rn * 商品入库的DAO层rn * rn * @author Administratorrn * rn */rnpublic class InStorageDao rnrn public void addInStorageAndItem(InStorage inStorage,rn List itemList) rn Connection con = null;rn PreparedStatement stat = null;rn ResultSet rs = null;rn try rn con = DBUtil.getConn();rn // 设置自动提交为falsern con.setAutoCommit(false);rn // 先保存入库单rn String inSql = "insert into iss_in(" + "in_code," + "in_date,"rn + "in_status," + "emp_id)" + " values(?,?,?,("rn + "select emp_id from iss_employee "rn + "where emp_name=?)) ";rn stat = con.prepareStatement(inSql, new String[] "in_id" );rn stat.setString(1, inStorage.getInCode());rn stat.setDate(2, new java.sql.Date(inStorage.getInDate().getTime()));rn stat.setInt(3, inStorage.getInStatus());rn stat.setString(4, inStorage.getEmp().getEmpName());rn stat.executeUpdate();//出错了???rnrn // 取得返回的入库单idrn int inId = 0;rn rs = stat.getGeneratedKeys();rn if (rs.next()) rn inId = rs.getInt(1);rn rn // 再保存入库明细rn String itemSql = "insert into iss_in_item(" + "in_item_price,"rn + "in_item_qty," + "in_item_status," + "goods_id,"rn + "in_id)" + "values(?,?,?,"rn + "(select goods_id from iss_goods where goods_code = ?),"rn + "?)";rn stat = con.prepareStatement(itemSql);rn for (InStorageItem inStorageItem : itemList) rn stat.setFloat(1, inStorageItem.getInItemPrice());rn stat.setInt(2, inStorageItem.getInItemQty());rn stat.setInt(3, 1);rn stat.setString(4, inStorageItem.getGoods().getGoodsCode());rn stat.setInt(5, inId);rn stat.executeUpdate();rn rn con.commit();rn catch (Exception e) rn e.printStackTrace();rn try rn con.rollback();rn catch (SQLException e1) rn e1.printStackTrace();rn rn finally rn DBUtil.closeConn(con, stat, rs);rn rn rnrnrn```rnrnrn 这是数据库部分,用的是MySQL数据库,请各位大虾帮帮忙!rn![图片说明](https://img-ask.csdn.net/upload/201605/11/1462948190_737156.png)rnrn![图片说明](https://img-ask.csdn.net/upload/201605/11/1462948205_833102.png)

没有更多推荐了,返回首页