情况说明:
我有两台同样的数据库服务器,一台为测试服务器,一台为运行服务器,操作系统和数据库系统都一样;
我有一个查询语句,在这两台服务器上都能正确运行,但是只要在这个查询语句最外层加个Select * from () aaa,其中一台可以正常运行,另外一台则会宕机;
都能正常运行的Sql语句:
SELECT G.OrganizationName AS 项目队,G.PROCESSNAME AS 项目类别,G.PROJECTNAME AS 项目名称,G.*,H.ACTIONNUM,J.FINISHNUM FROM (select A.*,B.OrganizationName,E.PROCESSID,E.PROCESSNAME,'' as 项目进度,'查看' as 操作 from (select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ01 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ15 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ201 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ202 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ203 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ204 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ205 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ23 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ27 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ28 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ78 where ORGANIZATIONID ='OGN60625') A LEFT JOIN ASREG001 B ON A.OrganizationID=B.OrganizationID LEFT JOIN ( SELECT C.PROCASEID,D.PROCESSID,D.PROCESSNAME FROM PDWFC01 C,PSWFD01 D WHERE C.PROCESSID=D.PROCESSID) E ON A.PROJECTID=E.PROCASEID
INNER JOIN PDWFC02 F ON A.PROJECTID=F.PROCASEID AND F.CURRENTSTATUS='P02') G LEFT JOIN (SELECT T.PROCESSID,COUNT(T.PROCESSID) AS ACTIONNUM FROM PSWFD07 T WHERE T.ACTIONTYPE NOT IN ('ED','AG','RG','SA') GROUP BY T.PROCESSID) H ON G.PROCESSID=H.PROCESSID LEFT JOIN (SELECT t.procaseid, COUNT(T.procaseid) as FINISHNUM FROM PDWFC04 T inner join PSWFD07 H on T.Actionid=H.ACTIONID AND H.ACTIONTYPE NOT IN ('ED','AG','RG','SA') WHERE T.CURRENTSTATUS='A05' group by t.procaseid) J ON G.PROJECTID=J.PROCASEID会导致其中一台服务器宕机的Sql语句
select * from (
SELECT G.OrganizationName AS 项目队,G.PROCESSNAME AS 项目类别,G.PROJECTNAME AS 项目名称,G.*,H.ACTIONNUM,J.FINISHNUM FROM (select A.*,B.OrganizationName,E.PROCESSID,E.PROCESSNAME,'' as 项目进度,'查看' as 操作 from (select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ01 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ15 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ201 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ202 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ203 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ204 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ205 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ23 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ27 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ28 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ78 where ORGANIZATIONID ='OGN60625') A LEFT JOIN ASREG001 B ON A.OrganizationID=B.OrganizationID LEFT JOIN ( SELECT C.PROCASEID,D.PROCESSID,D.PROCESSNAME FROM PDWFC01 C,PSWFD01 D WHERE C.PROCESSID=D.PROCESSID) E ON A.PROJECTID=E.PROCASEID
INNER JOIN PDWFC02 F ON A.PROJECTID=F.PROCASEID AND F.CURRENTSTATUS='P02') G LEFT JOIN (SELECT T.PROCESSID,COUNT(T.PROCESSID) AS ACTIONNUM FROM PSWFD07 T WHERE T.ACTIONTYPE NOT IN ('ED','AG','RG','SA') GROUP BY T.PROCESSID) H ON G.PROCESSID=H.PROCESSID LEFT JOIN (SELECT t.procaseid, COUNT(T.procaseid) as FINISHNUM FROM PDWFC04 T inner join PSWFD07 H on T.Actionid=H.ACTIONID AND H.ACTIONTYPE NOT IN ('ED','AG','RG','SA') WHERE T.CURRENTSTATUS='A05' group by t.procaseid) J ON G.PROJECTID=J.PROCASEID
) aaa
我有两台同样的数据库服务器,一台为测试服务器,一台为运行服务器,操作系统和数据库系统都一样;
我有一个查询语句,在这两台服务器上都能正确运行,但是只要在这个查询语句最外层加个Select * from () aaa,其中一台可以正常运行,另外一台则会宕机;
都能正常运行的Sql语句:
SELECT G.OrganizationName AS 项目队,G.PROCESSNAME AS 项目类别,G.PROJECTNAME AS 项目名称,G.*,H.ACTIONNUM,J.FINISHNUM FROM (select A.*,B.OrganizationName,E.PROCESSID,E.PROCESSNAME,'' as 项目进度,'查看' as 操作 from (select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ01 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ15 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ201 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ202 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ203 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ204 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ205 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ23 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ27 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ28 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ78 where ORGANIZATIONID ='OGN60625') A LEFT JOIN ASREG001 B ON A.OrganizationID=B.OrganizationID LEFT JOIN ( SELECT C.PROCASEID,D.PROCESSID,D.PROCESSNAME FROM PDWFC01 C,PSWFD01 D WHERE C.PROCESSID=D.PROCESSID) E ON A.PROJECTID=E.PROCASEID
INNER JOIN PDWFC02 F ON A.PROJECTID=F.PROCASEID AND F.CURRENTSTATUS='P02') G LEFT JOIN (SELECT T.PROCESSID,COUNT(T.PROCESSID) AS ACTIONNUM FROM PSWFD07 T WHERE T.ACTIONTYPE NOT IN ('ED','AG','RG','SA') GROUP BY T.PROCESSID) H ON G.PROCESSID=H.PROCESSID LEFT JOIN (SELECT t.procaseid, COUNT(T.procaseid) as FINISHNUM FROM PDWFC04 T inner join PSWFD07 H on T.Actionid=H.ACTIONID AND H.ACTIONTYPE NOT IN ('ED','AG','RG','SA') WHERE T.CURRENTSTATUS='A05' group by t.procaseid) J ON G.PROJECTID=J.PROCASEID会导致其中一台服务器宕机的Sql语句
select * from (
SELECT G.OrganizationName AS 项目队,G.PROCESSNAME AS 项目类别,G.PROJECTNAME AS 项目名称,G.*,H.ACTIONNUM,J.FINISHNUM FROM (select A.*,B.OrganizationName,E.PROCESSID,E.PROCESSNAME,'' as 项目进度,'查看' as 操作 from (select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ01 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ15 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ201 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ202 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ203 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ204 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ205 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ23 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ27 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ28 where ORGANIZATIONID ='OGN60625' union select PROJECTID,ORGANIZATIONID,PROJECTNAME from ADPRJ78 where ORGANIZATIONID ='OGN60625') A LEFT JOIN ASREG001 B ON A.OrganizationID=B.OrganizationID LEFT JOIN ( SELECT C.PROCASEID,D.PROCESSID,D.PROCESSNAME FROM PDWFC01 C,PSWFD01 D WHERE C.PROCESSID=D.PROCESSID) E ON A.PROJECTID=E.PROCASEID
INNER JOIN PDWFC02 F ON A.PROJECTID=F.PROCASEID AND F.CURRENTSTATUS='P02') G LEFT JOIN (SELECT T.PROCESSID,COUNT(T.PROCESSID) AS ACTIONNUM FROM PSWFD07 T WHERE T.ACTIONTYPE NOT IN ('ED','AG','RG','SA') GROUP BY T.PROCESSID) H ON G.PROCESSID=H.PROCESSID LEFT JOIN (SELECT t.procaseid, COUNT(T.procaseid) as FINISHNUM FROM PDWFC04 T inner join PSWFD07 H on T.Actionid=H.ACTIONID AND H.ACTIONTYPE NOT IN ('ED','AG','RG','SA') WHERE T.CURRENTSTATUS='A05' group by t.procaseid) J ON G.PROJECTID=J.PROCASEID
) aaa
正常的SELECT g.organizationname AS 项目队, g.processname AS 项目类别,
g.projectname AS 项目名称, g.*, h.actionnum, j.finishnum
FROM (SELECT a.*, b.organizationname, e.processid, e.processname,
'' AS 项目进度, '查看' AS 操作
FROM (SELECT projectid, organizationid, projectname
FROM adprj01
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj15
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj201
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj202
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj203
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj204
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj205
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj23
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj27
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj28
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj78
WHERE organizationid = 'OGN60625') a
LEFT JOIN
asreg001 b ON a.organizationid = b.organizationid
LEFT JOIN
(SELECT c.procaseid, d.processid, d.processname
FROM pdwfc01 c, pswfd01 d
WHERE c.processid = d.processid) e ON a.projectid =
e.procaseid
INNER JOIN pdwfc02 f
ON a.projectid = f.procaseid AND f.currentstatus = 'P02'
) g
LEFT JOIN
(SELECT t.processid, COUNT (t.processid) AS actionnum
FROM pswfd07 t
WHERE t.actiontype NOT IN ('ED', 'AG', 'RG', 'SA')
GROUP BY t.processid) h ON g.processid = h.processid
LEFT JOIN
(SELECT t.procaseid, COUNT (t.procaseid) AS finishnum
FROM pdwfc04 t INNER JOIN pswfd07 h
ON t.actionid = h.actionid
AND h.actiontype NOT IN ('ED', 'AG', 'RG', 'SA')
WHERE t.currentstatus = 'A05'
GROUP BY t.procaseid) j ON g.projectid = j.procaseid出问题的/* Formatted on 2008/05/20 17:06 (Formatter Plus v4.8.8) */
SELECT *
FROM (SELECT g.organizationname AS 项目队, g.processname AS 项目类别,
g.projectname AS 项目名称, g.*, h.actionnum, j.finishnum
FROM (SELECT a.*, b.organizationname, e.processid, e.processname,
'' AS 项目进度, '查看' AS 操作
FROM (SELECT projectid, organizationid, projectname
FROM adprj01
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj15
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj201
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj202
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj203
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj204
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj205
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj23
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj27
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj28
WHERE organizationid = 'OGN60625'
UNION
SELECT projectid, organizationid, projectname
FROM adprj78
WHERE organizationid = 'OGN60625') a
LEFT JOIN
asreg001 b ON a.organizationid = b.organizationid
LEFT JOIN
(SELECT c.procaseid, d.processid, d.processname
FROM pdwfc01 c, pswfd01 d
WHERE c.processid = d.processid) e
ON a.projectid = e.procaseid
INNER JOIN pdwfc02 f
ON a.projectid = f.procaseid
AND f.currentstatus = 'P02'
) g
LEFT JOIN
(SELECT t.processid, COUNT (t.processid) AS actionnum
FROM pswfd07 t
WHERE t.actiontype NOT IN ('ED', 'AG', 'RG', 'SA')
GROUP BY t.processid) h ON g.processid = h.processid
LEFT JOIN
(SELECT t.procaseid, COUNT (t.procaseid) AS finishnum
FROM pdwfc04 t INNER JOIN pswfd07 h
ON t.actionid = h.actionid
AND h.actiontype NOT IN ('ED', 'AG', 'RG', 'SA')
WHERE t.currentstatus = 'A05'
GROUP BY t.procaseid) j ON g.projectid = j.procaseid
) aaa
估计和你的oracle配置有关,不能正确解析出中文字段名
感觉越来越奇怪了
hebo2005 能继续帮我嘛?