欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql创建视图全文搜索[完整版]

程序员文章站 2022-06-06 11:15:58
sql创建视图全文搜索[完整版]   [sql] ---每个表都要设有主键。。。。 [sql] /*--创建视图 set numeri...

sql创建视图全文搜索[完整版]

 

[sql] 
---每个表都要设有主键。。。。  
[sql] 
/*--创建视图  
set   numeric_roundabort   off    
go    
set   ansi_padding,ansi_warnings,concat_null_yields_null,arithabort,quoted_identifier,ansi_nulls   on  
go  
create view view_productlist with schemabinding  
as  
select     ba_itemnumber.innerid, dbo.ba_itemnumber.stinnerid, dbo.ba_itemnumber.material, dbo.ba_shelltrumpet.code,   
                      dbo.ba_shelltrumpet.name, dbo.ba_shelltrumpet.cdescription, dbo.ba_shelltrumpet.vulgo, dbo.ba_shelltrumpet.edescription, dbo.ba_shelltrumpet.specification,   
                      dbo.ba_shelltrumpet.od, dbo.ba_shelltrumpet.standardclass, dbo.ba_shelltrumpet.diameter, dbo.ba_shelltrumpet.premiumold,   
                      dbo.ba_shelltrumpet.premiumnew, dbo.ba_itemnumber.invoicename, dbo.ba_itemnumber.outsidepriceold, dbo.ba_itemnumber.outsidepricenew,   
                      dbo.ba_itemnumber.mainunit, dbo.ba_itemnumber.viceunit, dbo.ba_itemnumber.singlet, dbo.ba_itemnumber.bag, dbo.ba_itemnumber.box,   
                      dbo.ba_itemnumber.cartons, dbo.ba_itemnumber.boxsize, dbo.ba_itemnumber.boxgw, dbo.ba_itemnumber.cartonssize, dbo.ba_itemnumber.cartonsgw,   
                      dbo.ba_itemnumber.drawingsurl, dbo.ba_itemnumber.whetherstanding, dbo.ba_itemnumber.minimumorderquantity, dbo.ba_itemnumber.countlimit,   
                      dbo.ba_itemnumber.countlower, dbo.ba_itemnumber.malldisplaynumber, dbo.ba_itemnumber.storemaxnumber, dbo.ba_itemnumber.generaldelivery,   
                      dbo.ba_itemnumber.exceeddelivery, dbo.ba_itemnumber.surfacetreatment, dbo.ba_itemnumber.tage, dbo.ba_itemnumber.heattreatment,   
                      dbo.ba_itemnumber.strengthgrade, dbo.ba_itemnumber.thecorrespondingmold, dbo.ba_itemnumber.remark, dbo.ba_itemnumber.remarkspecial,   
                      dbo.ba_itemnumber.remarkspecialinside, dbo.ba_itemnumber.minipackageunit, dbo.ba_itemnumber.minipackagequantity, dbo.ba_itemnumber.inventory,   
                      dbo.ba_itemnumber.bulkinventory, dbo.ba_itemnumber.defectiveinventory, dbo.ba_itemnumber.barcode, dbo.ba_shelltrumpet.toothtypebig,   
                      dbo.ba_shelltrumpet.headtype, dbo.ba_shelltrumpet.groove, dbo.ba_shelltrumpet.similarstandard, dbo.ba_shelltrumpet.isdzp,   
                      dbo.ba_itemnumber.storagelocationfinished, dbo.ba_itemnumber.storagelocationbulk, dbo.ba_itemnumber.pcxm,   
                      dbo.ba_shelltrumpet.remark as shelltrumpetremark, dbo.ba_shelltrumpet.remarkspecial as shelltrumpetremarkspecial  
from         dbo.ba_shelltrumpet inner join  
                      dbo.ba_itemnumber on dbo.ba_shelltrumpet.innerid = dbo.ba_itemnumber.stinnerid  
  
  
        set ansi_nulls on  
        set ansi_padding on  
        set ansi_warnings on  
        set arithabort on  
        set concat_null_yields_null on  
        set quoted_identifier on  
        set numeric_roundabort off  
  
*/  
  
/*没有为当前数据库启用全文搜索。请使用 sp_fulltext_database 来启用数据库的全文搜索。*/  
--exec sp_fulltext_database 'enable';  
  
/*创建全文目录*/  
--create fulltext catalog ftproductlist as default  
/* 首先创建一个唯一索引,以便全文索引利用*/  
--create unique clustered  index index_view_productlist on view_productlist(innerid)  
  
/*index_view_productlist' 不是可强制使用全文搜索键的有效索引 <pre name="code" class="sql">---查看每个表都要设有主键</pre>create fulltext index on view_productlist(innerid,[name],stinnerid,material,specification,cdescription,barcode) key index index_view_productlist on ftproductlist*/select * from view_productlist where contains(*,'"内六角"  
 or "内六角沉头机螺钉" or "m5*28" or "gb818"')