首页 > 其他 > 详细

自己写的第一个视图(为了纪念)

时间:2014-01-16 08:36:52      阅读:268      评论:0      收藏:0      [点我收藏+]

自己写的第一个视图:

CREATE VIEW [dbo].[view_Returndate]

AS

SELECT     p.htid + ‘,‘ + p.inspectingitemAssignment AS htidassign, p.htid AS htid, c.classificationname AS classificationname, item.projectnumber AS result,

                      p.phone AS sampleid, p.times AS statu, p.client AS conversion, p.sampledate AS finishtime, item.projectnumber AS projectnumber,c.classification as classification

FROM         inspectingitem AS item, childinspecting AS c, inspectionpact AS p

WHERE     item.projectnumber IN

                          (SELECT     *

                            FROM          dbo.f_splitstr(cast(p.inspectingitem AS varchar(1024)), ‘,‘)) AND item.projectnumber = c.projectnumber AND

                    (p.htid + ‘‘ + item.projectnumber+‘‘+c.classification NOT IN

                          (SELECT     r.htid + ‘‘ + r.inspectingitem +‘‘+r.classification AS a

                            FROM          returndate AS r))

UNION

SELECT     r.htid + ‘,‘ + item.projectnumber AS htidassign, r.htid, c.classificationname AS classificationname, r.result AS result, r.sampleid AS sampleid,

                      r.statu AS statu, r.conversion AS conversion, r.finishtime AS finishtime, r.inspectingitem AS projectnumber,c.classification as classification

FROM         returndate r LEFT JOIN

                      inspectingitem item ON r.inspectingitem = item.projectnumber LEFT JOIN

                      childinspecting c ON r.inspectingitem = c.projectnumber and r.classification=c.classification

 

 

// dbo.f_splitstr这个是自己写的处理字符串的函数

自己写的第一个视图(为了纪念)

原文:http://www.cnblogs.com/wangliansong/p/3517569.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!