问一个别名做查询条件的语句应该怎么写,麻烦高手帮忙解答,在线等!!!

Question

[code=SQL]SELECT PROJECTNAME

Answer 1

(Select   STAGE_BEG_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   a.PROJECT_STATE   and   PROJECTID   =   a.PROJECTID)begdate   ,
(Select   STAGE_END_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   5   and   PROJECTID   =   a.PROJECTID)enddate--为什么两个字查询的条件不同？一个STAGE_SERIAL   =   a.PROJECT_STATE，一个STAGE_SERIAL   =   5

Answer 2

看一下语句的结果是不是你想要的
select   b.PROJECTNAME as PROJECTID,
c.STAGE_NAME as PROJECT_STATE,
d.EMPLOYEENAME as EMPLOYEEID   ,   
c.STAGE_BEG_DATE as begdate   ,
c.STAGE_END_DATE as enddate,
a.PROJECTID   
from   PROJECT_BASE a   
left join SPPROJECTINFOTABLE b on b.PROJECTID   =   a.PROJECTID
left join PROJECT_STAGE c on c.STAGE_SERIAL   =   a.PROJECT_STATE   and   c.PROJECTID   =   a.PROJECTID
left join SPEMPLOYEEINFOTABLE d on d.EMPLOYEEID   =   a.EMPLOYEEID
where c.STAGE_BEG_DATE<='2008-1-1' and c.STAGE_END_DATE>='2008-1-1'

Answer 3

PROJECT_BASE为主表有3个字段PROJECTID,PROJECT_STATE,EMPLOYEEID
副表为PROJECT_STAGE有5个字段PROJECTID,STAGE_SERIAL,STAGE_NAME,STAGE_BEG_DATE,STAGE_END_DATE
其中PROJECTID为等值的,PROJECT_STATE和STAGE_SERIAL为等值的.
STAGE_SERIAL,STAGE_NAME,STAGE_BEG_DATE为一一对应的.
STAGE_SERIAL,STAGE_NAME,STAGE_END_DATE为一一对应的.数据内容为
PROJECTID,PROJECT_STATE,EMPLOYEEID
201,1,004.
PROJECTID,STAGE_SERIAL,STAGE_NAME,STAGE_BEG_DATE,STAGE_END_DATE
201,1,需求分析,2008-1-1,null
201,5,测试,null,2008-1-30PROJECT_BASE中的一条数据对应PROJECT_STAGE中多条数据,但是需要STAGE_SERIAL=1的STAGE_BEG_DATE和
STAGE_SERIAL=5的STAGE_END_DATE.请问如何用STAGE_BEG_DATE和STAGE_END_DATE当作查询条件,这两个数据为别名.

Answer 4

无效SQL语句?
这是错误信息？不是Sql server?
你把最后的where一句去掉，看有没有效

Answer 5

[code=SQL]SELECT  a.PROJECTNAME as PROJECT_ID,b.STAGE_NAME AS PROJECT_STATE,b.EMPLOYEEID,c.STAGE_BEG_DATE as begdate,c.STAGE_END_DATE as enddate  
FROM  SPPROJECTINFOTABLE a inner join PROJECT_STAGE b on a.PROJECTID=b.PROJECTID
INNER JOIN ON PROJECT_STAGE c ON b.PROJECTID=c.PROJECTID
WHERE STAGE_SERIAL=1 OR STAGE_SERIAL=5 [/sql]

Answer 6

SELECT     a.PROJECTNAME   as   PROJECT_ID,b.STAGE_NAME   AS   PROJECT_STATE,b.EMPLOYEEID,c.STAGE_BEG_DATE   as   begdate,c.STAGE_END_DATE   as   enddate     
FROM     SPPROJECTINFOTABLE   a   inner   join   PROJECT_STAGE   b   on   a.PROJECTID=b.PROJECTID 
INNER   JOIN   ON   PROJECT_STAGE   c   ON   b.PROJECTID=c.PROJECTID 
WHERE   STAGE_SERIAL=1   OR   STAGE_SERIAL=5    

Answer 7

SELECT     a.PROJECTNAME   as   PROJECT_ID,b.STAGE_NAME   AS   PROJECT_STATE,b.EMPLOYEEID,c.STAGE_BEG_DATE   as   begdate,c.STAGE_END_DATE   as   enddate     
FROM     SPPROJECTINFOTABLE   a   inner   join   PROJECT_STAGE   b   on   a.PROJECTID=b.PROJECTID 
INNER   JOIN     PROJECT_STAGE   c   ON   b.PROJECTID=c.PROJECTID 
WHERE   STAGE_SERIAL=1   OR   STAGE_SERIAL=5   

Answer 8

排除你理解的错误:
SELECT PROJECTID AS PROJECTID ,STAGE_BEG_DATE AS begdate,STAGE_END_DATE AS enddate
FROM PROJECT_STAGE
WHERE STAGE_SERIAL=1   OR   STAGE_SERIAL=5

Answer 9

SELECT   PROJECTID   AS   PROJECTID   ,STAGE_BEG_DATE   AS   begdate,STAGE_END_DATE   AS   enddate 
FROM   PROJECT_STAGE 
WHERE   STAGE_SERIAL=1       OR       STAGE_SERIAL=5

Answer 10

select   (Select   PROJECTNAME   from   SPPROJECTINFOTABLE   where   PROJECTID   =   a.PROJECTID)PROJECTID,(Select   STAGE_NAME   from   PROJECT_STAGE   where   PROJECTID   =   a.PROJECTID   and   STAGE_SERIAL   =   a.PROJECT_STATE)   PROJECT_STATE,(Select   EMPLOYEENAME   from   SPEMPLOYEEINFOTABLE   where   EMPLOYEEID   =   a.EMPLOYEEID)   EMPLOYEEID   ,   (Select   STAGE_BEG_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   a.PROJECT_STATE   and   PROJECTID   =   a.PROJECTID)begdate   ,(Select   STAGE_END_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   5   and   PROJECTID   =   a.PROJECTID)enddate,PROJECTID   from   PROJECT_BASE     a   where 
(Select   STAGE_BEG_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   a.PROJECT_STATE   and   PROJECTID   =   a.PROJECTID)>'2008-02-02' and ,(Select   STAGE_END_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   5   and   PROJECTID   =   a.PROJECTID)<'2009-09-01'

Answer 11

太乱了，直接用语句代替别名
  where   STAGE_SERIAL   =   a.PROJECT_STATE   and   PROJECTID   =   a.PROJECTID)>'2008-02-02' and (Select   STAGE_END_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   5   and   PROJECTID   =   a.PROJECTID)<'2009-09-01'

Answer 12

太乱了，直接用语句代替别名 
(Select   STAGE_BEG_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   a.PROJECT_STATE   and   PROJECTID   =   a.PROJECTID)>'2008-02-02' 
and 
(Select   STAGE_END_DATE   from   PROJECT_STAGE   where   STAGE_SERIAL   =   5   and   PROJECTID   =   a.PROJECTID)<'2009-09-01'