这个超级长的sql，报了一个 Subquery returns more than 1 row的错，水平太次，看不出来是哪个地方，大神帮忙看一下
SELECT
tab5.ID,
tab5.LE_CODE,
tab5.LE_NAME,
tab5.US_ID,
ROUND(tab5.STUDYTIME / 60, 2) AS STUDYTIME,
tab5.STUDYPLAN,
tab5.EXAM_ID,
tab5.PASS_MARK,
tab5.MARK,
tab5.mainid,
tab6.REGNAME,
tab6. NAME,
tab6.TC_ID,
tab6.GROUPNAME,
tab6.LE_ID AS MAIN_LE_ID,
tab6.ID AS TUSER_ID
FROM
(
SELECT
tab2.TC_ID,
tab2.ID,
tab2.REGNAME,
tab2. NAME,
tab2.GROUPNAME,
tab2.GROUP_ID,
le.ID AS LE_ID
FROM
(
SELECT DISTINCT
tab1.TC_ID,
tab1.ID,
tab1.REGNAME,
tab1. NAME,
tab1.GROUPNAME,
tab1.GROUP_ID
FROM
(
SELECT
tcu.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tp. NAME AS GROUPNAME,
tp.ID AS GROUP_ID
FROM
trainingclass_user tcu,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tp
WHERE
tcu.TU_ID = tu.ID
AND tu. STATUS = 1
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tcu.TC_ID
AND tg.USER_ID = tu.ID
AND tp.ID = tg.GROUP_ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tp. NAME AS GROUPNAME,
tp.ID AS GROUP_ID
FROM
tc_array_user tau,
user_station us,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tp
WHERE
tau.TYPE = 1
AND tau.ENTITYID = us.ST_ID
AND us.US_ID = tu.ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.USER_ID = tu.ID
AND tp.ID = tg.GROUP_ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tgp. NAME AS GROUPNAME,
tgp.ID AS GROUP_ID
FROM
tc_array_user tau,
tuser_group tg,
tuser tu,
training_class tc,
tgroup tgp
WHERE
tau.TYPE = 2
AND tau.ENTITYID = tg.GROUP_ID
AND tg.USER_ID = tu.ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.GROUP_ID = tgp.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tgp. NAME AS GROUPNAME,
tgp.ID AS GROUP_ID
FROM
tc_array_user tau,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tgp
WHERE
tau.TYPE = 3
AND tau.ENTITYID = tu.business
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.USER_ID = tu.ID
AND tgp.ID = tg.GROUP_ID
) AS tab1
WHERE
tab1.ID NOT IN (
SELECT
tue.TU_ID
FROM
tc_user_except tue,
training_class tc
WHERE
tc.ID = tue.TC_ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tue.flag = 0
)
) tab2,
TGROUP_TRAININGCLASS tt,
tgroup tgp,
trainingclass_lesson tl,
lesson le
WHERE
tab2.TC_ID = tt.TC_ID
AND tgp.ID = tt.TG_ID
AND tgp.ID IN (
+'userid'+
)
AND tab2.TC_ID = tl.TC_ID
AND le.ID = tl.LE_ID
AND le. STATUS = 1
) tab6
LEFT JOIN (
SELECT
tab4.ID,
tab4.LE_CODE,
tab4.LE_NAME,
tab4.US_ID,
tab4.STUDYTIME,
tab4.STUDYPLAN,
tab4.EXAM_ID,
tab4.PASS_MARK,
IFNULL(
(
SELECT DISTINCT
em.MARK
FROM
exam_mark em,
exam_stu_paper esp
WHERE
em.STU_ID = tab4.US_ID
AND em.EXAM_ID = esp.EXAM_ID
AND esp.STU_ID = tab4.US_ID
AND em.EXAM_ID = tab4.EXAM_ID
),
0
) AS MARK,
IFNULL(
(
SELECT DISTINCT
il.mainid
FROM
integral il
WHERE
il.usid = tab4.US_ID
AND il.mainid = tab4.ID
) ,- 1
) AS mainid
FROM
(
SELECT DISTINCT
tab2.ID,
tab2.LE_CODE,
tab2.LE_NAME,
tab2.US_ID,
IFNULL(
(
SELECT DISTINCT
uss.STUDYTIME
FROM
userstudystatistics uss
WHERE
uss.LE_ID = tab2.ID
AND uss.US_ID = tab2.US_ID
),
0
) AS STUDYTIME,
IFNULL(
(
SELECT DISTINCT
uli.STUDYPLAN
FROM
usertlidstudyinfo uli
WHERE
uli.LE_ID = tab2.ID
AND uli.US_ID = tab2.US_ID
),
0
) AS STUDYPLAN,
IFNULL(tab3.exam_id, 0) AS EXAM_ID,
IFNULL(tab3.PASS_MARK, 0) AS PASS_MARK
FROM
(
SELECT DISTINCT
le.ID,
le.LE_CODE,
le.LE_NAME,
sl.US_ID
FROM
selectedlesson sl,
lesson le
WHERE
sl.LE_ID = le.ID
AND sl.TYPE = 0
AND le. STATUS = 1
UNION
SELECT
le.ID,
le.LE_CODE,
le.LE_NAME,
tab1.US_ID
FROM
(
SELECT
tcu.TC_ID,
tu.ID AS US_ID,
tu.REGNAME,
tu. NAME
FROM
trainingclass_user tcu,
tuser tu
WHERE
tcu.TU_ID = tu.ID
AND tu. STATUS = 1
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
user_station us,
tuser tu
WHERE
tau.TYPE = 1
AND tau.ENTITYID = us.ST_ID
AND us.US_ID = tu.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
tuser_group tg,
tuser tu
WHERE
tau.TYPE = 2
AND tau.ENTITYID = tg.GROUP_ID
AND tg.USER_ID = tu.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
tuser tu
WHERE
tau.TYPE = 3
AND tau.ENTITYID = tu.business
) tab1,
training_class tc,
trainingclass_lesson tl,
lesson le
WHERE
tab1.TC_ID = tc.ID
AND tl.TC_ID = tc.ID
AND le.ID = tl.LE_ID
AND tc. STATUS = 1
AND le. STATUS = 1
UNION
SELECT
sl.LESSION_ID AS ID,
le.LE_CODE,
le.LE_NAME,
tab1.US_ID
FROM
(
SELECT
us.ID,
us.US_ID,
us.ST_ID,
sn. NAME
FROM
user_station us,
station sn
WHERE
us.ST_ID = sn.ID
) tab1,
station_lesson sl,
lesson le
WHERE
sl.STATION_ID = tab1.ST_ID
AND sl.LESSION_ID = le.ID
AND le. STATUS = 1
) tab2
LEFT JOIN (
SELECT
ei.OUTER_ID,
ei.ID AS EXAM_ID,
ei.PASS_MARK
FROM
exam_info ei
WHERE
ei.OUTER_TYPE = 1
) tab3 ON tab2.ID = tab3.OUTER_ID
) tab4
) tab5 ON tab5.US_ID = tab6.ID
WHERE
tab6.LE_ID = tab5.ID
ORDER BY
tab6. NAME,
tab6.ID,
tab5.LE_CODE
------解决思路----------------------
IFNULL(
(
SELECT DISTINCT
uss.STUDYTIME
FROM
userstudystatistics uss
WHERE
uss.LE_ID = tab2.ID
AND uss.US_ID = tab2.US_ID
),
0
) AS STUDYTIME,
上面的语句可能查出来有多条记录，修改成下面这个应该就不会报错了，建议这个地方优化一下
IFNULL(
(
SELECT TOP 1 DISTINCT
uss.STUDYTIME
FROM
userstudystatistics uss
WHERE
uss.LE_ID = tab2.ID
AND uss.US_ID = tab2.US_ID
),
0
) AS STUDYTIME,
------解决思路----------------------
我改了一下你试试：
SELECT
tab5.ID,
tab5.LE_CODE,
tab5.LE_NAME,
tab5.US_ID,
ROUND(tab5.STUDYTIME / 60, 2) AS STUDYTIME,
tab5.STUDYPLAN,
tab5.EXAM_ID,
tab5.PASS_MARK,
tab5.MARK,
tab5.mainid,
tab6.REGNAME,
tab6. NAME,
tab6.TC_ID,
tab6.GROUPNAME,
tab6.LE_ID AS MAIN_LE_ID,
tab6.ID AS TUSER_ID
FROM
(
SELECT
tab2.TC_ID,
tab2.ID,
tab2.REGNAME,
tab2. NAME,
tab2.GROUPNAME,
tab2.GROUP_ID,
le.ID AS LE_ID
FROM
(
SELECT DISTINCT
tab1.TC_ID,
tab1.ID,
tab1.REGNAME,
tab1. NAME,
tab1.GROUPNAME,
tab1.GROUP_ID
FROM
(
SELECT
tcu.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tp. NAME AS GROUPNAME,
tp.ID AS GROUP_ID
FROM
trainingclass_user tcu,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tp
WHERE
tcu.TU_ID = tu.ID
AND tu. STATUS = 1
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tcu.TC_ID
AND tg.USER_ID = tu.ID
AND tp.ID = tg.GROUP_ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tp. NAME AS GROUPNAME,
tp.ID AS GROUP_ID
FROM
tc_array_user tau,
user_station us,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tp
WHERE
tau.TYPE = 1
AND tau.ENTITYID = us.ST_ID
AND us.US_ID = tu.ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.USER_ID = tu.ID
AND tp.ID = tg.GROUP_ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tgp. NAME AS GROUPNAME,
tgp.ID AS GROUP_ID
FROM
tc_array_user tau,
tuser_group tg,
tuser tu,
training_class tc,
tgroup tgp
WHERE
tau.TYPE = 2
AND tau.ENTITYID = tg.GROUP_ID
AND tg.USER_ID = tu.ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.GROUP_ID = tgp.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME,
tgp. NAME AS GROUPNAME,
tgp.ID AS GROUP_ID
FROM
tc_array_user tau,
tuser tu,
training_class tc,
tuser_group tg,
tgroup tgp
WHERE
tau.TYPE = 3
AND tau.ENTITYID = tu.business
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tc.ID = tau.TC_ID
AND tu. STATUS = 1
AND tg.USER_ID = tu.ID
AND tgp.ID = tg.GROUP_ID
) AS tab1
WHERE
tab1.ID NOT IN (
SELECT
tue.TU_ID
FROM
tc_user_except tue,
training_class tc
WHERE
tc.ID = tue.TC_ID
AND tc. STATUS = 1
AND 1 = 1
AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'
AND tue.flag = 0
)
) tab2,
TGROUP_TRAININGCLASS tt,
tgroup tgp,
trainingclass_lesson tl,
lesson le
WHERE
tab2.TC_ID = tt.TC_ID
AND tgp.ID = tt.TG_ID
AND tgp.ID IN (
+'userid'+
)
AND tab2.TC_ID = tl.TC_ID
AND le.ID = tl.LE_ID
AND le. STATUS = 1
) tab6
LEFT JOIN (
SELECT
tab4.ID,
tab4.LE_CODE,
tab4.LE_NAME,
tab4.US_ID,
tab4.STUDYTIME,
tab4.STUDYPLAN,
tab4.EXAM_ID,
tab4.PASS_MARK,
IFNULL(
(
SELECT top 1
em.MARK
FROM
exam_mark em,
exam_stu_paper esp
WHERE
em.STU_ID = tab4.US_ID
AND em.EXAM_ID = esp.EXAM_ID
AND esp.STU_ID = tab4.US_ID
AND em.EXAM_ID = tab4.EXAM_ID
group by em.MARK
),
0
) AS MARK,
IFNULL(
(
SELECT top 1
il.mainid
FROM
integral il
WHERE
il.usid = tab4.US_ID
AND il.mainid = tab4.ID
group by il.mainid
) ,- 1
) AS mainid
FROM
(
SELECT DISTINCT
tab2.ID,
tab2.LE_CODE,
tab2.LE_NAME,
tab2.US_ID,
IFNULL(
(
SELECT top 1
uss.STUDYTIME
FROM
userstudystatistics uss
WHERE
uss.LE_ID = tab2.ID
AND uss.US_ID = tab2.US_ID
group by uss.STUDYTIME
),
0
) AS STUDYTIME,
IFNULL(
(
SELECT top 1
uli.STUDYPLAN
FROM
usertlidstudyinfo uli
WHERE
uli.LE_ID = tab2.ID
AND uli.US_ID = tab2.US_ID
group by uli.STUDYPLAN
),
0
) AS STUDYPLAN,
IFNULL(tab3.exam_id, 0) AS EXAM_ID,
IFNULL(tab3.PASS_MARK, 0) AS PASS_MARK
FROM
(
SELECT DISTINCT
le.ID,
le.LE_CODE,
le.LE_NAME,
sl.US_ID
FROM
selectedlesson sl,
lesson le
WHERE
sl.LE_ID = le.ID
AND sl.TYPE = 0
AND le. STATUS = 1
UNION
SELECT
le.ID,
le.LE_CODE,
le.LE_NAME,
tab1.US_ID
FROM
(
SELECT
tcu.TC_ID,
tu.ID AS US_ID,
tu.REGNAME,
tu. NAME
FROM
trainingclass_user tcu,
tuser tu
WHERE
tcu.TU_ID = tu.ID
AND tu. STATUS = 1
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
user_station us,
tuser tu
WHERE
tau.TYPE = 1
AND tau.ENTITYID = us.ST_ID
AND us.US_ID = tu.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
tuser_group tg,
tuser tu
WHERE
tau.TYPE = 2
AND tau.ENTITYID = tg.GROUP_ID
AND tg.USER_ID = tu.ID
UNION
SELECT
tau.TC_ID,
tu.ID,
tu.REGNAME,
tu. NAME
FROM
tc_array_user tau,
tuser tu
WHERE
tau.TYPE = 3
AND tau.ENTITYID = tu.business
) tab1,
training_class tc,
trainingclass_lesson tl,
lesson le
WHERE
tab1.TC_ID = tc.ID
AND tl.TC_ID = tc.ID
AND le.ID = tl.LE_ID
AND tc. STATUS = 1
AND le. STATUS = 1
UNION
SELECT
sl.LESSION_ID AS ID,
le.LE_CODE,
le.LE_NAME,
tab1.US_ID
FROM
(
SELECT
us.ID,
us.US_ID,
us.ST_ID,
sn. NAME
FROM
user_station us,
station sn
WHERE
us.ST_ID = sn.ID
) tab1,
station_lesson sl,
lesson le
WHERE
sl.STATION_ID = tab1.ST_ID
AND sl.LESSION_ID = le.ID
AND le. STATUS = 1
) tab2
LEFT JOIN (
SELECT
ei.OUTER_ID,
ei.ID AS EXAM_ID,
ei.PASS_MARK
FROM
exam_info ei
WHERE
ei.OUTER_TYPE = 1
) tab3 ON tab2.ID = tab3.OUTER_ID
) tab4
) tab5 ON tab5.US_ID = tab6.ID
WHERE
tab6.LE_ID = tab5.ID
ORDER BY
tab6. NAME,
tab6.ID,
tab5.LE_CODE
------解决思路----------------------
根据你帖出来的语句的第 177, 192, 210, 222 行4个位置的子查询， 如果查询出来的纪录不唯一，就会导致该错误。
可按照楼上的， 在 SELECT 后添加 TOP 1 处理。