我们现在的项目要做一个报表,用的是mySQL数据库,记录也不多,总共才2万多条,要求的报表关联很多,我写了个sql,竟然直接爆机了,特别郁闷!还请高手们不吝赐教啊!
我的QQ:494466199 Msn:[email protected] eMail: [email protected]
绿色的是我的最初的SQL,红色的是我修改后的 SQL ,pcpPlms0713.sql是我的数据库文件SELECT T1.USER_ID,T1.USER_NAME,T1.LONG_NAME,
T2.LEA_START_TIME,T2.REAL_START_TIME,T2.REAL_END_TIME, CASE T2.LEARN_STATUS
WHEN '1' THEN '已开始但未结束'
WHEN '2' THEN '已结束'
ELSE '未开始'
END AS LEARN_STATUS, CASE T2.LEARN_STATUS
WHEN '1' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
WHEN '2' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
ELSE '0'
END AS SUSTAIN_TIME, CASE T2.CREATE_STATUS
WHEN '1' THEN '必修'
WHEN '0' THEN '选修'
ELSE ''
END AS CREATE_STATUS, T3.LESSON_ID,T3.LESSON_NAME,T3.TYPE_NAME AS LESSON_TYPE_NAME,
T4.GROUP_NAME,T4.GROUP_ID
FROM R_PERSON_USER T
LEFT JOIN T_USER T1
ON T1.USER_ID = T.USER_ID
LEFT JOIN T_PERSON_LEARN T2
ON T.PERSON_LEARN_ID = T2.PERSON_LEARN_ID
LEFT JOIN (
SELECT TTT.*,TTT1.TYPE_NAME FROM
(
SELECT TT1.PERSON_LEARN_ID,TT2.*
FROM R_PERSON_LESSON TT1
LEFT JOIN T_LESSON TT2
ON TT1.LESSON_ID = TT2.LESSON_ID
) TTT
LEFT JOIN T_LESSON_TYPE TTT1
ON TTT.LESSON_TYPE_ID = TTT1.LESSON_TYPE_ID
) T3
ON T.PERSON_LEARN_ID = T3.PERSON_LEARN_ID
LEFT JOIN (
SELECT DISTINCT TT3.USER_ID ,TT4.*
FROM R_USER_GROUP TT3
LEFT JOIN T_GROUP TT4
ON TT3.GROUP_ID = TT4.GROUP_ID
)T4
ON T1.USER_ID = T4.USER_IDUNIONSELECT T5.USER_ID,T5.USER_NAME,T5.LONG_NAME,
NULL AS LEA_START_TIME,NULL AS REAL_START_TIME,NULL AS REAL_END_TIME, NULL AS LEARN_STATUS, NULL AS SUSTAIN_TIME, NULL AS CREATE_STATUS, NULL AS LESSON_ID,NULL AS LESSON_NAME,NULL AS LESSON_TYPE_NAME, NULL AS GROUP_NAME,NULL AS GROUP_IDFROM T_USER T5
WHERE USER_ID NOT IN (SELECT USER_ID FROM R_PERSON_USER)
我修改后的SQL : SELECT
T1.USER_ID,T1.USER_NAME,T1.LONG_NAME,
T2.LEA_START_TIME,T2.REAL_START_TIME,T2.REAL_END_TIME,
CASE T2.LEARN_STATUS
WHEN '1' THEN '已开始'
WHEN '2' THEN '已结束'
ELSE '未开始'
END AS LEARN_STATUS,
CASE T2.LEARN_STATUS
WHEN '1' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
WHEN '2' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
ELSE '0'
END AS SUSTAIN_TIME,
CASE T2.CREATE_STATUS
WHEN '1' THEN '必修'
WHEN '0' THEN '选修'
ELSE ''
END AS CREATE_STATUS,
T3.LESSON_ID,T3.LESSON_NAME,T3.TYPE_NAME AS LESSON_TYPE_NAME,
T4.GROUP_NAME,T4.GROUP_ID
FROM R_PERSON_USER T,
(
SELECT TTT.*,TTT1.TYPE_NAME FROM
(
SELECT TT1.PERSON_LEARN_ID,TT2.*
FROM R_PERSON_LESSON TT1,T_LESSON TT2
WHERE TT1.LESSON_ID = TT2.LESSON_ID
) TTT,T_LESSON_TYPE TTT1
WHERE TTT.LESSON_TYPE_ID = TTT1.LESSON_TYPE_ID
) T3,
T_USER T1,
T_PERSON_LEARN T2,
(
SELECT DISTINCT TT3.USER_ID ,TT4.*
FROM R_USER_GROUP TT3,T_GROUP TT4
WHERE TT3.GROUP_ID = TT4.GROUP_ID
)T4
WHERE 1=1
AND T.PERSON_LEARN_ID = T3.PERSON_LEARN_ID
AND T1.USER_ID = T.USER_ID
AND T.PERSON_LEARN_ID = T2.PERSON_LEARN_ID
AND T1.USER_ID = T4.USER_IDLIMIT 43000,1000
我的QQ:494466199 Msn:[email protected] eMail: [email protected]
绿色的是我的最初的SQL,红色的是我修改后的 SQL ,pcpPlms0713.sql是我的数据库文件SELECT T1.USER_ID,T1.USER_NAME,T1.LONG_NAME,
T2.LEA_START_TIME,T2.REAL_START_TIME,T2.REAL_END_TIME, CASE T2.LEARN_STATUS
WHEN '1' THEN '已开始但未结束'
WHEN '2' THEN '已结束'
ELSE '未开始'
END AS LEARN_STATUS, CASE T2.LEARN_STATUS
WHEN '1' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
WHEN '2' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
ELSE '0'
END AS SUSTAIN_TIME, CASE T2.CREATE_STATUS
WHEN '1' THEN '必修'
WHEN '0' THEN '选修'
ELSE ''
END AS CREATE_STATUS, T3.LESSON_ID,T3.LESSON_NAME,T3.TYPE_NAME AS LESSON_TYPE_NAME,
T4.GROUP_NAME,T4.GROUP_ID
FROM R_PERSON_USER T
LEFT JOIN T_USER T1
ON T1.USER_ID = T.USER_ID
LEFT JOIN T_PERSON_LEARN T2
ON T.PERSON_LEARN_ID = T2.PERSON_LEARN_ID
LEFT JOIN (
SELECT TTT.*,TTT1.TYPE_NAME FROM
(
SELECT TT1.PERSON_LEARN_ID,TT2.*
FROM R_PERSON_LESSON TT1
LEFT JOIN T_LESSON TT2
ON TT1.LESSON_ID = TT2.LESSON_ID
) TTT
LEFT JOIN T_LESSON_TYPE TTT1
ON TTT.LESSON_TYPE_ID = TTT1.LESSON_TYPE_ID
) T3
ON T.PERSON_LEARN_ID = T3.PERSON_LEARN_ID
LEFT JOIN (
SELECT DISTINCT TT3.USER_ID ,TT4.*
FROM R_USER_GROUP TT3
LEFT JOIN T_GROUP TT4
ON TT3.GROUP_ID = TT4.GROUP_ID
)T4
ON T1.USER_ID = T4.USER_IDUNIONSELECT T5.USER_ID,T5.USER_NAME,T5.LONG_NAME,
NULL AS LEA_START_TIME,NULL AS REAL_START_TIME,NULL AS REAL_END_TIME, NULL AS LEARN_STATUS, NULL AS SUSTAIN_TIME, NULL AS CREATE_STATUS, NULL AS LESSON_ID,NULL AS LESSON_NAME,NULL AS LESSON_TYPE_NAME, NULL AS GROUP_NAME,NULL AS GROUP_IDFROM T_USER T5
WHERE USER_ID NOT IN (SELECT USER_ID FROM R_PERSON_USER)
我修改后的SQL : SELECT
T1.USER_ID,T1.USER_NAME,T1.LONG_NAME,
T2.LEA_START_TIME,T2.REAL_START_TIME,T2.REAL_END_TIME,
CASE T2.LEARN_STATUS
WHEN '1' THEN '已开始'
WHEN '2' THEN '已结束'
ELSE '未开始'
END AS LEARN_STATUS,
CASE T2.LEARN_STATUS
WHEN '1' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
WHEN '2' THEN SUBSTRING(TIMEDIFF(REAL_END_TIME, REAL_START_TIME),1,5)
ELSE '0'
END AS SUSTAIN_TIME,
CASE T2.CREATE_STATUS
WHEN '1' THEN '必修'
WHEN '0' THEN '选修'
ELSE ''
END AS CREATE_STATUS,
T3.LESSON_ID,T3.LESSON_NAME,T3.TYPE_NAME AS LESSON_TYPE_NAME,
T4.GROUP_NAME,T4.GROUP_ID
FROM R_PERSON_USER T,
(
SELECT TTT.*,TTT1.TYPE_NAME FROM
(
SELECT TT1.PERSON_LEARN_ID,TT2.*
FROM R_PERSON_LESSON TT1,T_LESSON TT2
WHERE TT1.LESSON_ID = TT2.LESSON_ID
) TTT,T_LESSON_TYPE TTT1
WHERE TTT.LESSON_TYPE_ID = TTT1.LESSON_TYPE_ID
) T3,
T_USER T1,
T_PERSON_LEARN T2,
(
SELECT DISTINCT TT3.USER_ID ,TT4.*
FROM R_USER_GROUP TT3,T_GROUP TT4
WHERE TT3.GROUP_ID = TT4.GROUP_ID
)T4
WHERE 1=1
AND T.PERSON_LEARN_ID = T3.PERSON_LEARN_ID
AND T1.USER_ID = T.USER_ID
AND T.PERSON_LEARN_ID = T2.PERSON_LEARN_ID
AND T1.USER_ID = T4.USER_IDLIMIT 43000,1000
解决方案 »
- 紧急求救:SP2-1503:无法初始化oracle调用界面
- 特别急特别,过来看看
- 求Oracle 10g Web开发书籍!
- 请问有Oracle公司的编程规范吗?
- 为什么我的oracle8i安装后启动DBAStudio总是说没有监听啊?
- 在oracle中,怎样得到数据库中所有的存储过程代码?谢谢
- 这个GROUP BY怎么分?
- 请大家帮忙(关于sql和oracle不同的)
- 一个触发器的问题,请高手赐教!!!!!!!
- oracle 12c rac public ip建立连接抛出“The Network Adapter could not establish the conn”
- oracle数据库导入,导出的问题。
- Oracle查询时比较两个字段的大小,并返回整数或负数
譬如
SELECT P.*,U.* FROM T_PERSON_LEARN P
LEFT JOIN R_PERSON_USER RPU
ON P.PERSON_ID = RPU.PERSON_ID
LEFT JOIN USER U
ON RPU.USER_ID = U.USER_ID