iT邦幫忙

0

請問sql語法如何使用關鍵字查詢

sql ms sql t-sql
阿薛 2017-10-13 14:24:26 ‧ 12187 瀏覽

  • 分享至 

  • xImage

各位大大您好，我想要輸入關鍵字後，查詢以下個欄位是否有該關鍵字，請問如何使用sql語法查詢？
我目前是使用此方法https://msdn.microsoft.com/zh-tw/library/ms142488.aspx
但是他只能查詢單一資料表

目前錯誤如下：
訊息 7601，層級 16，狀態 3，行 34
無法在 資料行 'CameraID' 上使用 CONTAINS 或 FREETEXT 述詞，因為它並未使用全文檢索索引。

有爬文，該文章可能有解 但沒細說http://bbs.csdn.net/topics/390458100?page=1
程式碼如下：

SELECT MyTable.*
FROM
(SELECT
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
(select min(Time_Bind) from DailyCamera where startid = a.startid)Time_Bind,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='1')uldate1,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='11')uldate11,
(select daa.CameraID  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.ResvID  where  daa.StartID= a.ResvID  and dac.PicType ='11' )CameraID,
(select daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.ResvID  where  daa.StartID= a.ResvID  and dac.PicType ='11' )Status,
(select dates from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) dates,
(select datee from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) datee,
(select min(uldate) from DailyWpic where StartID = a.startid and PicType='3')uldate3,
(select min(uldate) from DailyWpic where StartID = a.startid and PicType='4')uldate4,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='6')uldate6,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='7')uldate7,
(select top 1 reason from DailyWpic dac where startid = a.StartID and PicType='8')reasonp,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='8')uldate8,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='9')uldate9,
(select max(EndDate) from DailyResvSt dat right join DailyWpic dac on dat.ResvID=a.ResvID  where dat.startid = a.StartID and PicType='10')uldate10,
(select  top 1  examstatus from DailyResv)examstatus,
(select Site from DailyResvSt where StartID=a.StartID) Sitep,
(select top 1 pictype from DailyWpic where StartID = a.StartID) pictype,
(select ccode from CtorData CTA JOIN  DailyWpic DAC ON CTA.Cno=DAC.CtorCno )ccode,
(select top 1 supvcno from  DailyWpic where ResvID = a.ResvID)supvcno,
(select top 1 ctorcno from  DailyWpic where ResvID = a.ResvID)ctorcno,
(select top 1 reason from DailyWpic where StartID = a.StartID)reason,
 * from [RMMDB].[DBO].[DAILYrESVST] a 
) MyTable
WHERE 1=1 and  MyTable.casecode='43434'
--↓我想要全文檢索
and contains ((MyTable.CameraID,MyTable.casecode,MyTable.ccode,MyTable.ctorcno,MyTable.datee,MyTable.dates,MyTable.EndDate,MyTable.examstatus,MyTable.item,MyTable.pictype,MyTable.reason,MyTable.reasonp,MyTable.ResvID,MyTable.Site,MyTable.Sitep,MyTable.StartDate,MyTable.StartID,MyTable.Status,MyTable.supvcno,MyTable.supvcno,MyTable.Time_Bind,MyTable.uldate1,MyTable.uldate10,MyTable.uldate11,MyTable.uldate3,MyTable.uldate4,MyTable.uldate6,MyTable.uldate6,MyTable.uldate7,MyTable.uldate8,MyTable.uldate9),'中正')
--錯誤訊息：
--訊息 7601，層級 16，狀態 3，行 34
--無法在 資料行 'CameraID' 上使用 CONTAINS 或 FREETEXT 述詞，因為它並未使用全文檢索索引。

請問如何調整sql語法 或還有其他方式 謝謝各位大大！

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
神Q超人
iT邦研究生 5 級 ‧ 2017-10-13 15:29:13
最佳解答

看這個錯誤訊息可能是還沒建立全文檢索索引，微軟的文章有提到關於這部分的設定，可以參考看看：
https://docs.microsoft.com/zh-tw/sql/relational-databases/search/get-started-with-full-text-search
我剛剛試了一下，只要把該資料表加入全文檢索目錄裡面就可以了，但是昨天的方法是把所有子查詢的資料再另外包成一個MyTable，如果不是實體的資料表可能不能用這種方式，目前我有想到兩種方法：

第一種是為這段SQL語法建立一個檢視表，再把檢視表加進全文索引目錄中。

第二種方法就是在程式裡下迴圈針對每一個欄位去跑條件，讓他的SQL字串變成這樣：


SELECT MyTable.*
FROM
(SELECT
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
(select min(Time_Bind) from DailyCamera where startid = a.startid)Time_Bind,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='1')uldate1,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='11')uldate11,
(select daa.CameraID  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.ResvID  where  daa.StartID= a.ResvID  and dac.PicType ='11' )CameraID,
(select daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.ResvID  where  daa.StartID= a.ResvID  and dac.PicType ='11' )Status,
(select dates from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) dates,
(select datee from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) datee,
(select min(uldate) from DailyWpic where StartID = a.startid and PicType='3')uldate3,
(select min(uldate) from DailyWpic where StartID = a.startid and PicType='4')uldate4,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='6')uldate6,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='7')uldate7,
(select top 1 reason from DailyWpic dac where startid = a.StartID and PicType='8')reasonp,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='8')uldate8,
(select min(uldate) from DailyWpic dac where startid = a.StartID and PicType='9')uldate9,
(select max(EndDate) from DailyResvSt dat right join DailyWpic dac on dat.ResvID=a.ResvID  where dat.startid = a.StartID and PicType='10')uldate10,
(select  top 1  examstatus from DailyResv)examstatus,
(select Site from DailyResvSt where StartID=a.StartID) Sitep,
(select top 1 pictype from DailyWpic where StartID = a.StartID) pictype,
(select ccode from CtorData CTA JOIN  DailyWpic DAC ON CTA.Cno=DAC.CtorCno )ccode,
(select top 1 supvcno from  DailyWpic where ResvID = a.ResvID)supvcno,
(select top 1 ctorcno from  DailyWpic where ResvID = a.ResvID)ctorcno,
(select top 1 reason from DailyWpic where StartID = a.StartID)reason,
 * from [RMMDB].[DBO].[DAILYrESVST] a 
) MyTable
WHERE 1=1 and  MyTable.casecode='43434'
--↓我想要全文檢索
and (MyTable.CameraID LIKE '%中正%' OR MyTable.casecode LIKE '%中正%' OR MyTable.ccode LIKE '%中正%' OR MyTable.ctorcno LIKE '%中正%' OR MyTable.datee LIKE '%中正%' OR MyTable.dates LIKE '%中正%' OR MyTable.EndDate LIKE '%中正%' OR MyTable.examstatus LIKE '%中正%' OR MyTable.item LIKE '%中正%' OR MyTable.pictype LIKE '%中正%' OR MyTable.reason LIKE '%中正%' OR MyTable.reasonp LIKE '%中正%' OR MyTable.ResvID LIKE '%中正%' OR MyTable.Site LIKE '%中正%' OR MyTable.Sitep LIKE '%中正%' OR MyTable.StartDate LIKE '%中正%' OR MyTable.StartID LIKE '%中正%' OR MyTable.Status LIKE '%中正%' OR MyTable.supvcno LIKE '%中正%' OR MyTable.supvcno LIKE '%中正%' OR MyTable.Time_Bind LIKE '%中正%' OR MyTable.uldate1 LIKE '%中正%' OR MyTable.uldate10 LIKE '%中正%' OR MyTable.uldate11 LIKE '%中正%' OR MyTable.uldate3 LIKE '%中正%' OR MyTable.uldate4 LIKE '%中正%' OR MyTable.uldate6 LIKE '%中正%' OR MyTable.uldate6 LIKE '%中正%' OR MyTable.uldate7 LIKE '%中正%' OR MyTable.uldate8 LIKE '%中正%' OR MyTable.uldate9 LIKE '%中正%')
神Q超人 iT邦研究生 5 級 ‧ 2017-10-13 15:48:27 檢舉

第二種解法比較笨，還是看其他大大有沒有更好的做法

阿薛 iT邦新手 5 級 ‧ 2017-11-08 11:22:00 檢舉

已解決 程式碼如下 謝謝神Q超人大大。

sql = "SELECT MyTable.*"
            sql += "FROM"
            sql += " (SELECT"
            sql += " (select casecode from DailyResv where ResvID = a.ResvID) casecode,"
            'sql += "(select top 1 factory from [TycDigIS2].[dbo].[AppBasic] ap  right join DailyResv dv on ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)factory,"
            'sql += "(select min(start) from  [TycDigIS2].[dbo].[AppBasic] ap right join DailyResv dv On ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)start,"
            'sql += "(select min(stop) from  [TycDigIS2].[dbo].[AppBasic] ap right join DailyResv dv on ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)stop,"
            sql += "(select min(Time_Bind) from DailyCamera where startid=a.StartID)Time_Bind,"
            sql += " (Select min(uldate) from DailyWpic dac where startid=a.StartID And PicType='1')uldate1,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='11')uldate11,"
            sql += " (select  top 1 CameraID  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind asc )  CameraID,"
            sql += " (select min(daa.Time_Bind) from DailyCamera  daa right join DailyWpic dac on daa.StartID =a.StartID)minTime_Bind,"
            sql += " (select max(daa.Time_Bind) from DailyCamera  daa right join DailyWpic dac On daa.StartID =a.StartID)maxTime_Bind,"
            sql += " (select   top 1 daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind asc )  Statusa,"
            sql += " (select   top 1 daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind desc )  Statusde,"
            sql += " (select dates from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) dates,"
            sql += " (select datee from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) datee,"
            sql += " (select appdate from DailyResv dav  where ResvID = a.ResvID) appdate,"
            sql += " (select min(uldate) from DailyWpic dc where startid=a.StartID And  PicType='5' and (PicSType='7' or PicSType='8'))PicType5,"
            sql += " (select top 1 uldate from DailyWpic where startid=a.StartID and PicType='3')uldate3,"
            sql += " (select top 1 Cno from SupvChg sg  right join DailyResv dv on sg.CaseCode= dv.CaseCode where dv.ResvID=a.ResvID)supvcno, "
            sql += " (select top 1 sa.Name from Supvdata sa  right join Supvchg sg on sa.cno=sg.cno   right join  DailyResv dv on sg.CaseCode= dv.CaseCode   where dv.ResvID=a.ResvID)supvname, "
            sql += " (select top 1 Cno from ctorChg cg  right join DailyResv dv on cg.CaseCode= dv.CaseCode where dv.ResvID=a.ResvID)ctorcno, "
            sql += " (select top 1 ca.Name from ctordata ca  right join ctorchg cg on ca.cno=cg.cno   right join  DailyResv dv on cg.CaseCode= dv.CaseCode   where dv.ResvID=a.ResvID)ctorname, "
            sql += " (select top 1 uldate from DailyWpic where startid=a.StartID and PicType='4') uldate4,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='5') count5,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='5') uldate5,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='6') count6,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='6') uldate6,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='7') count7,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='7') uldate7,"
            sql += " (select top 1 reason from DailyWpic dac where startid=a.StartID and PicType='8') reasonp,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='8') uldate8,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='9') uldate9,"
            sql += " (select max(uldate) from DailyWpic dat where startid=a.StartID and PicType='10') uldate10,"
            sql += " (select  top 1  examstatus from DailyResv)examstatus,"
            sql += " (select Site from DailyResvSt where StartID=a.StartID) Sitep,"
            sql += " (select top 1 pictype from DailyWpic where startid=a.StartID) pictype,"
            sql += " (select ccode from CtorData CTA JOIN  DailyWpic DAC ON CTA.Cno=DAC.CtorCno )ccode,"
            sql += " (select top 1 reason from DailyWpic where startid=a.StartID)reason,"
            sql += " (select count(*) from ChkSType where TypeID='1' and STypeStatus=1) Chk1_TT,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and PChkResult in ('0','1','2')) PChk1_K,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and PChkResult='1') PChk1_E,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and CChkResult in ('0','1','2')) CChk1_K,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and CChkResult='1') CChk1_E,"
            sql += " (select count(*) from ChkSType where TypeID='3' and STypeStatus=1) Chk3_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and PChkResult in ('0','1','2')) PChk3_K, "
            sql += "  (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and PChkResult='1') PChk3_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and CChkResult in ('0','1','2')) CChk3_K, "
            sql += "  (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and CChkResult='1') CChk3_E, "
            sql += "(select count(*) from ChkSType where TypeID='4' and STypeStatus=1) Chk4_TT, "
            sql += "     (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and PChkResult in ('0','1','2')) PChk4_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and PChkResult='1') PChk4_E, "
            sql += "   (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and CChkResult in ('0','1','2')) CChk4_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and CChkResult='1') CChk4_E, "
            sql += " (select count(*) from ChkSType where TypeID='5' and STypeStatus=1) Chk5_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and PChkResult in ('0','1','2')) PChk5_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and PChkResult='1') PChk5_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and CChkResult in ('0','1','2')) CChk5_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and CChkResult='1') CChk5_E, "
            sql += "(select count(*) from ChkSType where TypeID='6' and STypeStatus=1) Chk6_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and PChkResult in ('0','1','2')) PChk6_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and PChkResult='1') PChk6_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and CChkResult in ('0','1','2')) CChk6_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and CChkResult='1') CChk6_E, "
            sql += "(select count(*) from ChkSType where TypeID='7' and STypeStatus=1) Chk7_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and PChkResult in ('0','1','2')) PChk7_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and PChkResult='1') PChk7_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and CChkResult in ('0','1','2')) CChk7_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and CChkResult='1') CChk7_E, "
            sql += "(select count(*) from ChkSType where TypeID='8' and STypeStatus=1) Chk8_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and PChkResult in ('0','1','2')) PChk8_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and PChkResult='1') PChk8_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and CChkResult in ('0','1','2')) CChk8_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and CChkResult='1') CChk8_E, "
            sql += "(select count(*) from ChkSType where TypeID='10' and STypeStatus=1) Chk10_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and PChkResult in ('0','1','2')) PChk10_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and PChkResult='1') PChk10_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and CChkResult in ('0','1','2')) CChk10_K,"
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and CChkResult='1') CChk10_E, "
            sql += "(select count(*) from ChkSType where TypeID='11' and STypeStatus=1) Chk11_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and PChkResult in ('0','1','2')) PChk11_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and PChkResult='1') PChk11_E,"
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and CChkResult in ('0','1','2')) CChk11_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and CChkResult='1') CChk11_E, "
            sql += "(select count(*) from ChkSType where TypeID='13' and STypeStatus=1) Chk13_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and PChkResult in ('0','1','2')) PChk13_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and PChkResult='1') PChk13_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and CChkResult in ('0','1','2')) CChk13_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and CChkResult='1') CChk13_E, "
            sql += " (select  max(wgs84_x) from DailyWpic dac where startid=a.StartID And PicType='1'  and wgs84_x >0  ) wgs84_x1,"
            sql += " (select  max(wgs84_y) from DailyWpic dac where startid=a.StartID And PicType='1'   and wgs84_y >0  ) wgs84_y1,"
            sql += " * From [RMMDB].[DBO].[DailyResvSt] a "
            sql += ") MyTable "
            sql += " Where 1 = 1"


        '// 關鍵字
            If TBKeyWord.Text <> "" Then
                sql += " and (MyTable.CameraID LIKE '%" & TBKeyWord.Text & "%' or MyTable.factory like '%" & TBKeyWord.Text & "%' or MyTable.supvname like '%" & TBKeyWord.Text & "%' or MyTable.ctorname like  '%" & TBKeyWord.Text & "%' OR MyTable.casecode LIKE '%" & TBKeyWord.Text & "%'  OR MyTable.ccode LIKE '%" & TBKeyWord.Text & "%' OR MyTable.ctorcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.datee LIKE '%" & TBKeyWord.Text & "%' OR MyTable.dates LIKE '%" & TBKeyWord.Text & "%' OR MyTable.EndDate LIKE '%" & TBKeyWord.Text & "%' OR MyTable.examstatus LIKE '%" & TBKeyWord.Text & "%' OR MyTable.item LIKE '%" & TBKeyWord.Text & "%' OR MyTable.pictype LIKE '%" & TBKeyWord.Text & "%' OR MyTable.reason LIKE '%" & TBKeyWord.Text & "%' OR MyTable.reasonp LIKE '%" & TBKeyWord.Text & "%' OR MyTable.ResvID LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Site LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Sitep LIKE '%" & TBKeyWord.Text & "%' OR MyTable.StartDate LIKE '%" & TBKeyWord.Text & "%' OR MyTable.StartID LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Statusa LIKE '%" & TBKeyWord.Text & "%' OR MyTable.supvcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.supvcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Time_Bind LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate1 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate10 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate11 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate3 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate4 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate6 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate6 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate7 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate8 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate9 LIKE '%" & TBKeyWord.Text & "%')"
            End If
阿薛 iT邦新手 5 級 ‧ 2017-11-08 11:22:01 檢舉

已解決 程式碼如下 謝謝神Q超人大大。

sql = "SELECT MyTable.*"
            sql += "FROM"
            sql += " (SELECT"
            sql += " (select casecode from DailyResv where ResvID = a.ResvID) casecode,"
            'sql += "(select top 1 factory from [TycDigIS2].[dbo].[AppBasic] ap  right join DailyResv dv on ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)factory,"
            'sql += "(select min(start) from  [TycDigIS2].[dbo].[AppBasic] ap right join DailyResv dv On ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)start,"
            'sql += "(select min(stop) from  [TycDigIS2].[dbo].[AppBasic] ap right join DailyResv dv on ap.CaseID= dv.CaseCode where dv.ResvID=a.ResvID)stop,"
            sql += "(select min(Time_Bind) from DailyCamera where startid=a.StartID)Time_Bind,"
            sql += " (Select min(uldate) from DailyWpic dac where startid=a.StartID And PicType='1')uldate1,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='11')uldate11,"
            sql += " (select  top 1 CameraID  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind asc )  CameraID,"
            sql += " (select min(daa.Time_Bind) from DailyCamera  daa right join DailyWpic dac on daa.StartID =a.StartID)minTime_Bind,"
            sql += " (select max(daa.Time_Bind) from DailyCamera  daa right join DailyWpic dac On daa.StartID =a.StartID)maxTime_Bind,"
            sql += " (select   top 1 daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind asc )  Statusa,"
            sql += " (select   top 1 daa.Status  from DailyCamera daa right join DailyWpic dac on daa.StartID =a.StartID order by  DAA.Time_Bind desc )  Statusde,"
            sql += " (select dates from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) dates,"
            sql += " (select datee from CaseHisPerd cad right join dailyresv dav on  cad.CaseCode=dav.CaseCode   where dav.ResvID=a.StartID) datee,"
            sql += " (select appdate from DailyResv dav  where ResvID = a.ResvID) appdate,"
            sql += " (select min(uldate) from DailyWpic dc where startid=a.StartID And  PicType='5' and (PicSType='7' or PicSType='8'))PicType5,"
            sql += " (select top 1 uldate from DailyWpic where startid=a.StartID and PicType='3')uldate3,"
            sql += " (select top 1 Cno from SupvChg sg  right join DailyResv dv on sg.CaseCode= dv.CaseCode where dv.ResvID=a.ResvID)supvcno, "
            sql += " (select top 1 sa.Name from Supvdata sa  right join Supvchg sg on sa.cno=sg.cno   right join  DailyResv dv on sg.CaseCode= dv.CaseCode   where dv.ResvID=a.ResvID)supvname, "
            sql += " (select top 1 Cno from ctorChg cg  right join DailyResv dv on cg.CaseCode= dv.CaseCode where dv.ResvID=a.ResvID)ctorcno, "
            sql += " (select top 1 ca.Name from ctordata ca  right join ctorchg cg on ca.cno=cg.cno   right join  DailyResv dv on cg.CaseCode= dv.CaseCode   where dv.ResvID=a.ResvID)ctorname, "
            sql += " (select top 1 uldate from DailyWpic where startid=a.StartID and PicType='4') uldate4,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='5') count5,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='5') uldate5,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='6') count6,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='6') uldate6,"
            sql += " (select count(*) from DailyWpic dac where startid=a.StartID and PicType='7') count7,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='7') uldate7,"
            sql += " (select top 1 reason from DailyWpic dac where startid=a.StartID and PicType='8') reasonp,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='8') uldate8,"
            sql += " (select min(uldate) from DailyWpic dac where startid=a.StartID and PicType='9') uldate9,"
            sql += " (select max(uldate) from DailyWpic dat where startid=a.StartID and PicType='10') uldate10,"
            sql += " (select  top 1  examstatus from DailyResv)examstatus,"
            sql += " (select Site from DailyResvSt where StartID=a.StartID) Sitep,"
            sql += " (select top 1 pictype from DailyWpic where startid=a.StartID) pictype,"
            sql += " (select ccode from CtorData CTA JOIN  DailyWpic DAC ON CTA.Cno=DAC.CtorCno )ccode,"
            sql += " (select top 1 reason from DailyWpic where startid=a.StartID)reason,"
            sql += " (select count(*) from ChkSType where TypeID='1' and STypeStatus=1) Chk1_TT,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and PChkResult in ('0','1','2')) PChk1_K,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and PChkResult='1') PChk1_E,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and CChkResult in ('0','1','2')) CChk1_K,"
            sql += " (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='1' and CChkResult='1') CChk1_E,"
            sql += " (select count(*) from ChkSType where TypeID='3' and STypeStatus=1) Chk3_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and PChkResult in ('0','1','2')) PChk3_K, "
            sql += "  (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and PChkResult='1') PChk3_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and CChkResult in ('0','1','2')) CChk3_K, "
            sql += "  (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='3' and CChkResult='1') CChk3_E, "
            sql += "(select count(*) from ChkSType where TypeID='4' and STypeStatus=1) Chk4_TT, "
            sql += "     (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and PChkResult in ('0','1','2')) PChk4_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and PChkResult='1') PChk4_E, "
            sql += "   (select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and CChkResult in ('0','1','2')) CChk4_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='4' and CChkResult='1') CChk4_E, "
            sql += " (select count(*) from ChkSType where TypeID='5' and STypeStatus=1) Chk5_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and PChkResult in ('0','1','2')) PChk5_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and PChkResult='1') PChk5_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and CChkResult in ('0','1','2')) CChk5_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='5' and CChkResult='1') CChk5_E, "
            sql += "(select count(*) from ChkSType where TypeID='6' and STypeStatus=1) Chk6_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and PChkResult in ('0','1','2')) PChk6_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and PChkResult='1') PChk6_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and CChkResult in ('0','1','2')) CChk6_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='6' and CChkResult='1') CChk6_E, "
            sql += "(select count(*) from ChkSType where TypeID='7' and STypeStatus=1) Chk7_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and PChkResult in ('0','1','2')) PChk7_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and PChkResult='1') PChk7_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and CChkResult in ('0','1','2')) CChk7_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='7' and CChkResult='1') CChk7_E, "
            sql += "(select count(*) from ChkSType where TypeID='8' and STypeStatus=1) Chk8_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and PChkResult in ('0','1','2')) PChk8_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and PChkResult='1') PChk8_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and CChkResult in ('0','1','2')) CChk8_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='8' and CChkResult='1') CChk8_E, "
            sql += "(select count(*) from ChkSType where TypeID='10' and STypeStatus=1) Chk10_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and PChkResult in ('0','1','2')) PChk10_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and PChkResult='1') PChk10_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and CChkResult in ('0','1','2')) CChk10_K,"
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='10' and CChkResult='1') CChk10_E, "
            sql += "(select count(*) from ChkSType where TypeID='11' and STypeStatus=1) Chk11_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and PChkResult in ('0','1','2')) PChk11_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and PChkResult='1') PChk11_E,"
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and CChkResult in ('0','1','2')) CChk11_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='11' and CChkResult='1') CChk11_E, "
            sql += "(select count(*) from ChkSType where TypeID='13' and STypeStatus=1) Chk13_TT, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and PChkResult in ('0','1','2')) PChk13_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and PChkResult='1') PChk13_E, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and CChkResult in ('0','1','2')) CChk13_K, "
            sql += "(select count(distinct ChkSType) from DailyCheck where startid=a.StartID And ChkType='13' and CChkResult='1') CChk13_E, "
            sql += " (select  max(wgs84_x) from DailyWpic dac where startid=a.StartID And PicType='1'  and wgs84_x >0  ) wgs84_x1,"
            sql += " (select  max(wgs84_y) from DailyWpic dac where startid=a.StartID And PicType='1'   and wgs84_y >0  ) wgs84_y1,"
            sql += " * From [RMMDB].[DBO].[DailyResvSt] a "
            sql += ") MyTable "
            sql += " Where 1 = 1"


        '// 關鍵字
            If TBKeyWord.Text <> "" Then
                sql += " and (MyTable.CameraID LIKE '%" & TBKeyWord.Text & "%' or MyTable.factory like '%" & TBKeyWord.Text & "%' or MyTable.supvname like '%" & TBKeyWord.Text & "%' or MyTable.ctorname like  '%" & TBKeyWord.Text & "%' OR MyTable.casecode LIKE '%" & TBKeyWord.Text & "%'  OR MyTable.ccode LIKE '%" & TBKeyWord.Text & "%' OR MyTable.ctorcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.datee LIKE '%" & TBKeyWord.Text & "%' OR MyTable.dates LIKE '%" & TBKeyWord.Text & "%' OR MyTable.EndDate LIKE '%" & TBKeyWord.Text & "%' OR MyTable.examstatus LIKE '%" & TBKeyWord.Text & "%' OR MyTable.item LIKE '%" & TBKeyWord.Text & "%' OR MyTable.pictype LIKE '%" & TBKeyWord.Text & "%' OR MyTable.reason LIKE '%" & TBKeyWord.Text & "%' OR MyTable.reasonp LIKE '%" & TBKeyWord.Text & "%' OR MyTable.ResvID LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Site LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Sitep LIKE '%" & TBKeyWord.Text & "%' OR MyTable.StartDate LIKE '%" & TBKeyWord.Text & "%' OR MyTable.StartID LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Statusa LIKE '%" & TBKeyWord.Text & "%' OR MyTable.supvcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.supvcno LIKE '%" & TBKeyWord.Text & "%' OR MyTable.Time_Bind LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate1 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate10 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate11 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate3 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate4 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate6 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate6 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate7 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate8 LIKE '%" & TBKeyWord.Text & "%' OR MyTable.uldate9 LIKE '%" & TBKeyWord.Text & "%')"
            End If
登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22212 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙