iT邦幫忙

0

sql語法請教

各位大大您好,請問以下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 謝謝各位大大

dog830228 iT邦新手 1 級 ‧ 2017-10-12 16:09:21 檢舉
請確認
[RMMDB].[DBO].[DAILYrESVST] 資料表中有無casecode欄位
dog830228 iT邦新手 1 級 ‧ 2017-10-12 16:09:23 檢舉
抱歉按了兩下 所以提交兩次
請忽略><

2 個回答

0
dog830228
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改名 感謝大大

我要發表回答

立即登入回答