SELECT DISTINCT
DCode, DName, WorkID, WorkName, Manager, WorkState, ProjectNo, ProjectName,
ProjectManager, PrjState
FROM (SELECT a.DCode, a.DName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM TeamWorkPartTree a, TeamWorkManage b, ProjectManage c
WHERE a.WorkID = b.WorkID AND b.TaskNo = c.ProjectNo AND
c.TaskType <> 'M' AND a.TCode LIKE 'F%'
UNION
SELECT a.TCode, a.TName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM XtdetamsTeam_title a, TeamWorkManage b, ProjectManage c,
TeamWorkResult d
WHERE a.FileID = d .FileID AND d .WorkID = b.WorkID AND
b.TaskNo = c.ProjectNo AND c.TaskType <> 'M' AND a.TCode LIKE 'F%')
MYTAB
ORDER BY DCode结构如上所示,问题是集合查询里,UNION后的select语句中,a表里字段为Tcode,Tname,这样,在这个集合查询的结果上进行查询的时候,就会显示列名Tcode无效.请教高手,如何能将两个字段不同的表组成一个集合查询,然后在这个查询结果上再进行查询.万分感谢!!
DCode, DName, WorkID, WorkName, Manager, WorkState, ProjectNo, ProjectName,
ProjectManager, PrjState
FROM (SELECT a.DCode, a.DName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM TeamWorkPartTree a, TeamWorkManage b, ProjectManage c
WHERE a.WorkID = b.WorkID AND b.TaskNo = c.ProjectNo AND
c.TaskType <> 'M' AND a.TCode LIKE 'F%'
UNION
SELECT a.TCode, a.TName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM XtdetamsTeam_title a, TeamWorkManage b, ProjectManage c,
TeamWorkResult d
WHERE a.FileID = d .FileID AND d .WorkID = b.WorkID AND
b.TaskNo = c.ProjectNo AND c.TaskType <> 'M' AND a.TCode LIKE 'F%')
MYTAB
ORDER BY DCode结构如上所示,问题是集合查询里,UNION后的select语句中,a表里字段为Tcode,Tname,这样,在这个集合查询的结果上进行查询的时候,就会显示列名Tcode无效.请教高手,如何能将两个字段不同的表组成一个集合查询,然后在这个查询结果上再进行查询.万分感谢!!
SELECT a.TCode, a.TName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM XtdetamsTeam_title a, TeamWorkManage b, ProjectManage c,
TeamWorkResult d
WHERE a.FileID = d .FileID AND d .WorkID = b.WorkID AND
b.TaskNo = c.ProjectNo AND c.TaskType <> 'M' AND a.TCode LIKE 'F%'
--------------------
可以执行
DCode, DName, WorkID, WorkName, Manager, WorkState, ProjectNo, ProjectName,
ProjectManager, PrjState
FROM (SELECT a.DCode, a.DName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM TeamWorkPartTree a, TeamWorkManage b, ProjectManage c
WHERE a.WorkID = b.WorkID AND b.TaskNo = c.ProjectNo AND
c.TaskType <> 'M' AND a.TCode LIKE 'F%'
UNION
SELECT Dcode=a.TCode, Dname=a.TName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM XtdetamsTeam_title a, TeamWorkManage b, ProjectManage c,
TeamWorkResult d
WHERE a.FileID = d .FileID AND d .WorkID = b.WorkID AND
b.TaskNo = c.ProjectNo AND c.TaskType <> 'M' AND a.TCode LIKE 'F%')
MYTAB
ORDER BY DCode
MYTAB.DCode, MYTAB.DName, MYTAB.WorkID, MYTAB.WorkName, MYTAB.Manager, MYTAB.WorkState, MYTAB.ProjectNo, MYTAB.ProjectName,
MYTAB.ProjectManager, MYTAB.PrjState
FROM (SELECT a.DCode, a.DName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM TeamWorkPartTree a, TeamWorkManage b, ProjectManage c
WHERE a.WorkID = b.WorkID AND b.TaskNo = c.ProjectNo AND
c.TaskType <> 'M' AND a.TCode LIKE 'F%'
UNION
SELECT Dcode=a.TCode, Dname=a.TName, b.WorkID, b.WorkName, b.Manager, b.WorkState,
c.ProjectNo, c.ProjectName, c.ProjectManager, c.WorkState AS PrjState
FROM XtdetamsTeam_title a, TeamWorkManage b, ProjectManage c,
TeamWorkResult d
WHERE a.FileID = d .FileID AND d .WorkID = b.WorkID AND
b.TaskNo = c.ProjectNo AND c.TaskType <> 'M' AND a.TCode LIKE 'F%')
MYTAB
ORDER BY MYTAB.DCode