视图问题

Question

CREATE VIEW dbo.v_outcont_tinvoice_query AS SELECT dbo.outcont1.ocontno, 
                                                          dbo.outcont1.cstname, 
                                                          dbo.outcont1.predate, 
                                                          dbo.outcont1.shpte, 
                                                          dbo.outcont1.curr, dbo.outcont1.amt, 
                                                          dpid = dept.dpname, 
                                                          userid = cdstaff.name, 
                                                          dbo.outcont1.country, 
                                                          dbo.CheckMain.chkdate,invno=isnull(tinvoice.invno,'没有发票'),zjzamt=isnull(tinvoice.zjzamt,0)
                                                    FROM dbo.outcont1, dbo.CheckMain, 
                                                          cdstaff, dept, tinvoice
                                                    WHERE dbo.outcont1.ocontno = dbo.CheckMain.dzid
                                                           AND 
                                                          outcont1.userid = cdstaff.code AND 
                                                          outcont1.dpid = dept.dpid AND 
                                                          (dbo.CheckMain.agreeflg = 'z') AND 
                                                          tinvoice.ocontno =* outcont1.ocontno
然后,我认为view相当于一个表操作\所以,select * from v_outcont_tinvoice_query where  invno='200520138S2'   把
invno='200520138S2' 的数据检索出来,结果把 '没有发票' 数据检索出来了,能处理吗?
然后我尝试:\
CREATE VIEW dbo.v_outcont_tinvoice_query AS select * from (
                SELECT dbo.outcont1.ocontno, 
                                                          dbo.outcont1.cstname, 
                                                          dbo.outcont1.predate, 
                                                          dbo.outcont1.shpte, 
                                                          dbo.outcont1.curr, dbo.outcont1.amt, 
                                                          dpid = dept.dpname, 
                                                          userid = cdstaff.name, 
                                                          dbo.outcont1.country, 
                                                          dbo.CheckMain.chkdate,invno=isnull(tinvoice.invno,'没有发票'),zjzamt=isnull(tinvoice.zjzamt,0)
                                                    FROM dbo.outcont1, dbo.CheckMain, 
                                                          cdstaff, dept, tinvoice
                                                    WHERE dbo.outcont1.ocontno = dbo.CheckMain.dzid
                                                           AND 
                                                          outcont1.userid = cdstaff.code AND 
                                                          outcont1.dpid = dept.dpid AND 
                                                          (dbo.CheckMain.agreeflg = 'z') AND 
                                                          tinvoice.ocontno =* outcont1.ocontno
) as abc也不行

Answer 1

CREATE   VIEW   dbo.v_outcont_tinvoice_query
AS
SELECT   dbo.outcont1.ocontno,   
        dbo.outcont1.cstname,   
        dbo.outcont1.predate,   
        dbo.outcont1.shpte,   
        dbo.outcont1.curr,   dbo.outcont1.amt,   
        dpid   =   dept.dpname,   
        userid   =   cdstaff.name,   
        dbo.outcont1.country,   
        dbo.CheckMain.chkdate,invno=isnull(tinvoice.invno,'没有发票'),zjzamt=isnull(tinvoice.zjzamt,0) 
FROM   dbo.outcont1,   dbo.CheckMain,   
            cdstaff,   dept,   tinvoice 
WHERE   dbo.outcont1.ocontno   =   dbo.CheckMain.dzid AND   
            outcont1.userid   =   cdstaff.code   AND   
            outcont1.dpid   =   dept.dpid   AND   
            (dbo.CheckMain.agreeflg   =   'z')   AND   
            tinvoice.ocontno   =*   outcont1.ocontno  AND
tinvoice.invno IS NOT NULL