先上 sqlSELECT
k.resource_id, k.resource_type,
(k.resource_type%2) resOffset,
z.type_index, c.question_id resourceId,
k.resource_name resoutceName, l.erStatus erStatus,
COALESCE(ROUND(avg(j.is_recommend),0),0) isRecommend, if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 then 1
when ABS(d.question_state) =3 THEN 0.6 ELSE 0 END
)/
sum(case when ABS(d.question_state) in (1,2,3,4) then 1 ELSE 0 END)*100,2),0) gradeRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "男" then 1
when ABS(d.question_state) =3 and g.user_sex = "男" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "男" then 1 ELSE 0 END)*100,2),0) maleRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "女" then 1
when ABS(d.question_state) =3 and g.user_sex = "女" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "女" then 1 ELSE 0 END)*100,2),0) femaleRate FROM t_user_question c
JOIN (
select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus
) l on l.question_id = c.question_id
JOIN t_question_user_recommen j on j.question_id = l.question_id
JOIN t_user_homework h on c.homework_id = h.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on c.user_id = f.user_id AND h.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_id
JOIN t_question k on k.question_id = l.question_id
JOIN t_subject_question_type z on z.question_type_id = k.question_type_id and z.subject_id = k.subject_id
WHERE f.class_id = 1001
and c.homework_id = 2
and h.homework_id = 2
and h.state IN (2,3)
GROUP BY c.question_id
k.resource_id, k.resource_type,
(k.resource_type%2) resOffset,
z.type_index, c.question_id resourceId,
k.resource_name resoutceName, l.erStatus erStatus,
COALESCE(ROUND(avg(j.is_recommend),0),0) isRecommend, if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 then 1
when ABS(d.question_state) =3 THEN 0.6 ELSE 0 END
)/
sum(case when ABS(d.question_state) in (1,2,3,4) then 1 ELSE 0 END)*100,2),0) gradeRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "男" then 1
when ABS(d.question_state) =3 and g.user_sex = "男" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "男" then 1 ELSE 0 END)*100,2),0) maleRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "女" then 1
when ABS(d.question_state) =3 and g.user_sex = "女" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "女" then 1 ELSE 0 END)*100,2),0) femaleRate FROM t_user_question c
JOIN (
select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus
) l on l.question_id = c.question_id
JOIN t_question_user_recommen j on j.question_id = l.question_id
JOIN t_user_homework h on c.homework_id = h.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on c.user_id = f.user_id AND h.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_id
JOIN t_question k on k.question_id = l.question_id
JOIN t_subject_question_type z on z.question_type_id = k.question_type_id and z.subject_id = k.subject_id
WHERE f.class_id = 1001
and c.homework_id = 2
and h.homework_id = 2
and h.state IN (2,3)
GROUP BY c.question_id
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus生成表,在question_id上建立索引
这个上面是有索引的,为了sql清晰好看 我把它省略了 ,一下是那个派生表: select
c.question_id ,
ABS(d.question_state) erStatus
from t_class_homework a
JOIN t_user_homework b on a.homework_id = b.homework_id
JOIN t_user_question c on a.homework_id = c.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on f.class_id = a.class_id and f.user_id = c.user_id
WHERE f.class_id = 1001
and f.user_id = 10001
and b.state in (2,3)
and a.homework_id = 2
and b.user_id= f.user_id
GROUP BY c.question_id
,哪个是
select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus
生成的表?
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus这个是5楼产生的结果 ,我偷懒了 没说清楚
select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus他是5楼sql产生的结果