iT邦幫忙

0

sql語法請教

sql ms sql select t-sql
阿薛 2017-10-12 15:56:20 ‧ 7035 瀏覽

各位大大您好，請問以下sql查詢語法如何查詢casecode，我目前是用where 但是失敗

SELECT
--↓casecode欄位 我想搜尋此欄位
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
--↑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')reason,
(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 reason from DailyResv dav 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) Site,
(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 
where 1=1
--以下失敗 casecode無法萃取出來 呈現紅底字
and casecode='43434'

請問如何查詢casecode 謝謝各位大大

石頭 iT邦高手 1 級 ‧ 2017-10-12 16:09:21 檢舉
請確認
[RMMDB].[DBO].[DAILYrESVST] 資料表中有無casecode欄位
石頭 iT邦高手 1 級 ‧ 2017-10-12 16:09:23 檢舉
抱歉按了兩下 所以提交兩次
請忽略><
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

0
石頭
iT邦高手 1 級 ‧ 2017-10-12 16:11:46
最佳解答 
SELECT
--↓casecode欄位 我想搜尋此欄位
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
--↑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')reason,
(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 reason from DailyResv dav 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) Site,
(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 
where (select casecode from DailyResv where ResvID = a.ResvID)='43434'

我猜你因該是想要這樣寫
你不能將(select casecode from DailyResv where ResvID = a.ResvID) casecode
直接當作欄位來當條件 因為上面的casecode是結果集欄位不是資料表欄位

阿薛 iT邦新手 5 級 ‧ 2017-10-12 16:33:59 檢舉

解答如下(一樣)：

SELECT
--↓casecode欄位 我想搜尋此欄位
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
--↑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')reason,
(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 reason from DailyResv dav 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) Site,
(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 
where (select casecode from DailyResv where ResvID = a.ResvID)='43434'

感謝dog830228大大

登入發表回應
1
神Q超人
iT邦研究生 5 級 ‧ 2017-10-12 16:02:42

改成這樣試試看

SELECT MyTable.*
FROM
(SELECT
--↓casecode欄位 我想搜尋此欄位
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
--↑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')reason,
(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 reason from DailyResv dav 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) Site,
(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 
where 1=1) MyTable

WHERE MyTable.casecode='43434'

因為casecode並不是DAILYrESVST資料表中的欄位，而WHERE只對FROM裡面的資料表欄位有效而已，所以如果要讓他可以直接以casecode下條件有兩個方式：
第一種就像上面那種方法，把查詢出來的所有資料變成一張自訂資料表「MyTable」後，在下去搜尋。
第二種方法是不寫子查詢，用JOIN的方式去串聯資料表，讓WHERE對JOIN資料表中的欄位也有效。

阿薛 iT邦新手 5 級 ‧ 2017-10-13 10:08:18 檢舉

解答如下(依建議微調)：

SELECT MyTable.*
FROM
(SELECT
--↓casecode欄位 我想搜尋此欄位
(select casecode from DailyResv where ResvID = a.ResvID) casecode,
--↑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 reason from DailyResv dav 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 
where 1=1) MyTable

WHERE MyTable.casecode='43434'

有依神Q超人建議將重複欄位reason跟site改名 感謝大大

登入發表回應

我要發表回答

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