奇怪的sql语句错误

[ontalk.club]

最近碰到一个奇怪的sql语句查询问题，简单说A表与B表关联，却没有数据查询出来，求大神帮忙看下
查询出LOW表的sql:
SELECT
t_p_exam_items.item_code,
t_p_exam_items.full_score,
t_p_exam_items.t_p_exam_items_id,
t_e_student_item_score.t_e_student_item_score_id,
AVG(
t_e_student_item_score.score
) AS lowavg
FROM
t_e_student_item_score
INNER JOIN t_p_exam_items ON t_p_exam_items.t_p_exam_items_id = t_e_student_item_score.t_p_exam_items_id
INNER JOIN (
SELECT
a.t_e_student_exam_id
FROM
(
SELECT
@rownum :=@rownum + 1 AS rownum,
t_r_student_score.t_e_student_exam_id,
t_r_student_score.score,
(
SELECT
count(1)
FROM
t_e_student_exam
WHERE
t_e_student_exam.t_p_exam_id = 231
AND t_e_student_exam.absent_mark = 0
) AS cnt
FROM
(
t_r_student_score,
(SELECT @rownum := 0) AS r
)
WHERE
t_r_student_score.t_p_exam_id = 231
ORDER BY
score
) a
WHERE
a.rownum <= a.cnt * 0.27
) low27 ON t_e_student_item_score.t_e_student_exam_id = low27.t_e_student_exam_id
GROUP BY
t_e_student_item_score.t_p_exam_items_id
查询出HIGH表的sql:
SELECT
t_p_exam_items.item_code,
t_p_exam_items.full_score,
t_p_exam_items.t_p_exam_items_id,
t_e_student_item_score.t_e_student_item_score_id,
AVG(
t_e_student_item_score.score
) AS highavg
FROM
t_e_student_item_score
INNER JOIN t_p_exam_items ON t_p_exam_items.t_p_exam_items_id = t_e_student_item_score.t_p_exam_items_id
INNER JOIN (
SELECT
a.t_e_student_exam_id
FROM
(
SELECT
@rownum :=@rownum + 1 AS rownum,
t_r_student_score.t_e_student_exam_id,
t_r_student_score.score,
(
SELECT
count(1)
FROM
t_e_student_exam
WHERE
t_e_student_exam.t_p_exam_id = 231
AND t_e_student_exam.absent_mark = 0
) AS cnt
FROM
(
t_r_student_score,
(SELECT @rownum := 0) AS r
)
WHERE
t_r_student_score.t_p_exam_id = 231
ORDER BY
score DESC
) a
WHERE
a.rownum <= a.cnt * 0.27
) high27 ON t_e_student_item_score.t_e_student_exam_id = high27.t_e_student_exam_id
GROUP BY
t_e_student_item_score.t_p_exam_items_id
WHERE
low.t_p_exam_items_id = high.t_p_exam_items_id
ORDER BY
high.t_p_exam_items_id
LOW表查出来的数据

HIGH表查出来的数据

两段sql用inner join关联，没有任何数据查出，求帮助

[二月十六]

关联字段没关联上？ 把inner join 改成left join试试

[ontalk.club]

两张表左关联：

[ontalk.club]

右关联：
内关联：
关联字段：t_p_exam_items_id

[ontalk.club]

引用 1 楼 sinat_28984567 的回复:

关联字段没关联上？ 把inner join 改成left join试试

试过了，不行，这两张表是自关联的，而且里边的语法有些看不懂

[二月十六]

把关联字段改成item_code试试

[ontalk.club]

引用 5 楼 sinat_28984567 的回复:

把关联字段改成item_code试试

也试过了，还是不行，我觉得问题不是在关联语法和条件那，而是在查询low或high表的sql那块

[二月十六]

引用 6 楼 qq_16207635 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] 把关联字段改成item_code试试

也试过了，还是不行，我觉得问题不是在关联语法和条件那，而是在查询low或high表的sql那块[/quote] 那把语句简化简化再试试

[IEEE_China]

把两部分分别写入到 临时表 #ta,#tb，再写两个临时表的join查询，检查是哪部分出错了