关于视图.txt

飞天论坛FTBBS PHP版 v4.1.ASP源代码

travel视图

CREATE VIEW dbo.travel (id,title,row,url,fromtable)
AS
SELECT top 10 travel.dbo.hotel.id,travel.dbo.hotel.title,substring(travel.dbo.hotel.row,1,1),"/travel/hotel/view.asp?id="+ltrim(str(travel.dbo.hotel.id)),"hotel"
FROM travel.dbo.hotel
WHERE (SUBSTRING(travel.dbo.hotel.pos, 1, 1) = '1')
and substring(travel.dbo.hotel.row,1,1)<'z'
UNION
SELECT top 10 travel.dbo.route.id, travel.dbo.route.rname,substring(travel.dbo.route.row,1,1),"/travel/luxian/index_view.asp?id="+ltrim(str(travel.dbo.route.id)),"route"
FROM travel.dbo.route
WHERE (substring(travel.dbo.route.pos, 1, 1) = '1')
and substring(travel.dbo.route.row,1,1)<'z'

travelorder视图
CREATE VIEW dbo.travelorder (id,title,row,url,fromtable,orderx,orderid)
AS
SELECT dbo.travel. *,dbo.travelorderx.orderx,dbo.travelorderx.id
 from dbo.travel LEFT OUTER JOIN
      dbo.travelorderx ON dbo.travel.id = dbo.travelorderx.travelid and dbo.travel.fromtable=dbo.travelorderx.fromtable


house视图

CREATE VIEW dbo.house (id,title,row,url,fromtable)
AS
SELECT top 10 
house.dbo.bulding.id,house.dbo.bulding.bname,substring(house.dbo.bulding.brow,1,1),"/fanchan/showgs.asp?id="+ltrim(str(house.dbo.bulding.id)),"bulding"
FROM house.dbo.bulding
WHERE (SUBSTRING(house.dbo.bulding.bpos, 1, 1) = '1')
and substring(house.dbo.bulding.brow,1,1)<'z'
UNION
SELECT top 10 
house.dbo.floor.id,house.dbo.floor.housename,substring(house.dbo.floor.houserow,1,1),"/fanchan/showlp.asp?id="+ltrim(str(house.dbo.floor.id)),"floor"
FROM house.dbo.floor
WHERE (SUBSTRING(house.dbo.floor.housepos, 1, 1) = '1')
and substring(house.dbo.floor.houserow,1,1)<'z'
UNION
SELECT top 10 
house.dbo.linside.id,house.dbo.linside.lname,substring(house.dbo.linside.lrow,1,1),"/fanchan/showzj.asp?id="+ltrim(str(house.dbo.linside.id)),"linside"
FROM house.dbo.linside
WHERE (SUBSTRING(house.dbo.linside.lpos, 1, 1) = '1')
and substring(house.dbo.linside.lrow,1,1)<'z'
UNION
SELECT top 10 
house.dbo.service.id,house.dbo.service.dname,substring(house.dbo.service.drow,1,1),"/fanchan/view_home.asp?id="+ltrim(str(house.dbo.service.id)),"service"
FROM house.dbo.service
WHERE (SUBSTRING(house.dbo.service.dpos, 1, 1) = '1')
and substring(house.dbo.service.drow,1,1)<'z'
UNION
SELECT top 10 
house.dbo.decorate.id,house.dbo.decorate.dname,substring(house.dbo.decorate.drow,1,1),"/fanchan/view_zhuanshi.asp?id="+ltrim(str(house.dbo.decorate.id)),"decorate"
FROM house.dbo.decorate
WHERE (SUBSTRING(house.dbo.decorate.dpos, 1, 1) = '1')
and substring(house.dbo.decorate.drow,1,1)<'z'



houseorder视图

CREATE VIEW dbo.houseorder (id,title,row,url,fromtable,orderx,orderid) AS SELECT dbo.house. *,dbo.houseorderx.orderx,dbo.houseorderx.id FROM dbo.house LEFT OUTER JOIN dbo.houseorderx ON dbo.house.id = dbo.houseorderx.travelid AND dbo.house.fromtable=dbo.houseorderx.fromtable 




home视图
CREATE VIEW dbo.home (id,title,row,url,fromtable)
AS
SELECT top 10 
home.dbo.cinamen.id,home.dbo.cinamen.cname,substring(home.dbo.cinamen.row,1,1),"/home/read_movie.asp?id="+ltrim(str(home.dbo.cinamen.id)),"cinamen"
FROM home.dbo.cinamen
WHERE (SUBSTRING(home.dbo.cinamen.pos, 1, 1) = '1')
and substring(home.dbo.cinamen.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.law.id,home.dbo.law.lname,substring(home.dbo.law.row,1,1),"/home/read_law.asp?id="+ltrim(str(home.dbo.law.id)),"law"
FROM home.dbo.law
WHERE (SUBSTRING(home.dbo.law.pos, 1, 1) = '1')
and substring(home.dbo.law.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.marksales.id,home.dbo.marksales.mname,substring(home.dbo.marksales.row,1,1),"/home/topmarket.asp?id="+ltrim(str(home.dbo.marksales.id)),"marksales"
FROM home.dbo.marksales
WHERE (SUBSTRING(home.dbo.marksales.pos, 1, 1) = '1')
and substring(home.dbo.marksales.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.marrycompany.id,home.dbo.marrycompany.mname,substring(home.dbo.marrycompany.row,1,1),"/home/view_company.asp?id="+ltrim(str(home.dbo.marrycompany.id)),"marrycompany"
FROM home.dbo.marrycompany
WHERE (SUBSTRING(home.dbo.marrycompany.pos, 1, 1) = '1')
and substring(home.dbo.marrycompany.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.medicineperson.id,home.dbo.medicineperson.mname,substring(home.dbo.medicineperson.row,1,1),"/home/read_person.asp?id="+ltrim(str(home.dbo.medicineperson.id)),"medicineperson"
FROM home.dbo.medicineperson
WHERE (SUBSTRING(home.dbo.medicineperson.pos, 1, 1) = '1')
and substring(home.dbo.medicineperson.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.medicineservice.id,home.dbo.medicineservice.mname,substring(home.dbo.medicineservice.row,1,1),"/home/read_hospital.asp?id="+ltrim(str(home.dbo.medicineservice.id)),"medicineservice"
FROM home.dbo.medicineservice
WHERE (SUBSTRING(home.dbo.medicineservice.pos, 1, 1) = '1')
and substring(home.dbo.medicineservice.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.medicinestore.id,home.dbo.medicinestore.mname,substring(home.dbo.medicinestore.row,1,1),"/home/read_medicine.asp?id="+ltrim(str(home.dbo.medicinestore.id)),"medicinestore"
FROM home.dbo.medicinestore
WHERE (SUBSTRING(home.dbo.medicinestore.pos, 1, 1) = '1')
and substring(home.dbo.medicinestore.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.newmovie.id,home.dbo.newmovie.moviename,substring(home.dbo.newmovie.row,1,1),"/home/read_movie.asp?id="+ltrim(str(home.dbo.newmovie.id)),"newmovie"
FROM home.dbo.newmovie
WHERE (SUBSTRING(home.dbo.newmovie.pos, 1, 1) = '1')
and substring(home.dbo.newmovie.row,1,1)<'z'
UNION
SELECT top 10 
home.dbo.supermark.id,home.dbo.supermark.sname,substring(home.dbo.supermark.row,1,1),"/home/readmarket.asp?id="+ltrim(str(home.dbo.supermark.id)),"supermark"
FROM home.dbo.supermark
WHERE (SUBSTRING(home.dbo.supermark.pos, 1, 1) = '1')
and substring(home.dbo.supermark.row,1,1)<'z'


homeorder视图
CREATE VIEW dbo.homeorder (id,title,row,url,fromtable,orderx,orderid) AS SELECT dbo.home. *,dbo.homeorderx.orderx,dbo.homeorderx.id FROM dbo.home LEFT OUTER JOIN dbo.homeorderx ON dbo.home.id = dbo.homeorderx.travelid AND dbo.home.fromtable=dbo.homeorderx.fromtable 




market视图
CREATE VIEW dbo.market(id, title, row, url, fromtable) AS SELECT TOP 10 market.dbo.mark.id, market.dbo.mark.mname, substring(market.dbo.mark.row, 1, 1), '/market/index_jinp_x.asp?id=' + ltrim(str(market.dbo.mark.id)), 'mark' FROM market.dbo.mark WHERE (SUBSTRING(market.dbo.mark.pos, 1, 1) = '1') AND substring(market.dbo.mark.row, 1, 1) < 'z' UNION SELECT TOP 10 market.dbo.zhongjie.id, market.dbo.zhongjie.zname, substring(market.dbo.zhongjie.row, 1, 1), '/market/index_zhongjie.asp' + ltrim(str(market.dbo.zhongjie.id)), 'zhongjie' FROM market.dbo.zhongjie WHERE (substring(market.dbo.zhongjie.pos, 1, 1) = '1') AND substring(market.dbo.zhongjie.row, 1, 1) < 'z'

marketorder视图
CREATE VIEW dbo.marketorder (id,title,row,url,fromtable,orderx,orderid) AS SELECT dbo.market. *,dbo.marketorderx.orderx,dbo.marketorderx.id FROM dbo.market LEFT OUTER JOIN dbo.marketorderx ON dbo.market.id = dbo.marketorderx.travelid AND dbo.market.fromtable=dbo.marketorderx.fromtable 






CREATE VIEW dbo.travel (id,title,title1,row,url,fromtable)
AS
SELECT top 10 job.dbo.person.id,job.dbo.person.pname,job.dbo.person.pqzy,substring(job.dbo.person.row,1,1),"/job/person/about_7_1.asp?id="+ltrim(str(job.dbo.person.id)),"person"
FROM job.dbo.person
WHERE (SUBSTRING(job.dbo.person.pos, 1, 1) = '1')
and substring(job.dbo.person.row,1,1)<'z'
UNION
SELECT TOP 10 id, job.dbo.company.cname,
          (SELECT TOP 1 job.dbo.invite.zpost
         FROM job.dbo.invite
         WHERE job.dbo.invite.user_name = job.dbo.company.user_name
         ORDER BY job.dbo.invite.id DESC), SUBSTRING(row, 1, 1),
      '/job/person/aboutcompany.asp?id=' + LTRIM(STR(id)), 
      'company'
FROM job.dbo.company
WHERE (SUBSTRING(pos, 1, 1) = '1') AND (SUBSTRING(row, 1, 1) < 'z')
UNION
SELECT TOP 10 id, 
          (SELECT TOP 1 job.dbo.company.cname
         FROM job.dbo.company
         WHERE job.dbo.company.user_name = job.dbo.invite.user_name),zpost,SUBSTRING(row, 1, 1), 
      '/job/person/showjobinfor.asp?id=' + LTRIM(STR(id)), 
      'invite'
FROM job.dbo.invite
WHERE (SUBSTRING(pos, 1, 1) = '1') AND (SUBSTRING(row, 1, 1) < 'z')


CREATE VIEW dbo.houseorder (id,title,title1,row,url,fromtable,orderx,orderid) AS SELECT dbo.job.*,dbo.joborderx.orderx,dbo.joborderx.id FROM dbo.job LEFT OUTER JOIN dbo.joborderx ON dbo.job.id = dbo.joborderx.travelid AND dbo.job.fromtable=dbo.joborderx.fromtable