如何将查询结果的某一列加入到另外一个查询中?SELECT station_id,datetime,ctime,hr,t24max,t24min,w12
FROM v_forecast_2008_item WHERE datetime>='2013-1-14'
AND (hr=12 OR hr=24)
AND station_id='59287'
ORDER BY datetime DESC,ctime DESCpid是序号,ctime表示时段,hr代表12小时或24小时,w12是天气代码,t24max和t24min分别代表最高最低温度,24小时天气就是同一时段的两行结果:
pid |station_id|datetime |ctime|hr|t24max|t24min|w12
33463391|59287 |2013-01-14|645 |12| | |0
33463391|59287 |2013-01-14|645 |24|18.0 |7.0 |0
33461636|59287 |2013-01-14|500 |12| | |0
33461636|59287 |2013-01-14|500 |24|20.0 |9.0 |1我想将将24小时的天气组合在一行:即hr=24那行的w12 AS w24,下面是我想得到的结果:
pid |station_id|datetime |ctime|t24max|t24min|w12|w24
33463391|59287 |2013-01-14|645 |18.0 |7.0 |0 |0
33461636|59287 |2013-01-14|500 |20.0 |9.0 |0 |1我用如下代码:
SELECT DISTINCT a.pid,a.station_id,a.datetime,a.ctime,a.t24max,a.t24min,b.w12 AS w12code,b.w12 AS w24code
FROM v_forecast_2008_item a JOIN v_forecast_2008_item b ON (a.pid = b.pid)
WHERE a.datetime>='2013-1-14'
AND (a.hr=24 AND b.hr=12)
AND a.station_id='59287'
ORDER BY a.datetime DESC,a.ctime DESC,a.station_id DESC得到如下结果:(明显是错的,ctime=500那行的w24应该是1
pid |station_id|datetime |ctime|t24max|t24min|w12|w24
33463391|59287 |2013-01-14|645 |18.0 |7.0 |0 |0
33461636|59287 |2013-01-14|500 |20.0 |9.0 |0 |0请问如果修改上面的代码才能得到正确的结果?
FROM v_forecast_2008_item WHERE datetime>='2013-1-14'
AND (hr=12 OR hr=24)
AND station_id='59287'
ORDER BY datetime DESC,ctime DESCpid是序号,ctime表示时段,hr代表12小时或24小时,w12是天气代码,t24max和t24min分别代表最高最低温度,24小时天气就是同一时段的两行结果:
pid |station_id|datetime |ctime|hr|t24max|t24min|w12
33463391|59287 |2013-01-14|645 |12| | |0
33463391|59287 |2013-01-14|645 |24|18.0 |7.0 |0
33461636|59287 |2013-01-14|500 |12| | |0
33461636|59287 |2013-01-14|500 |24|20.0 |9.0 |1我想将将24小时的天气组合在一行:即hr=24那行的w12 AS w24,下面是我想得到的结果:
pid |station_id|datetime |ctime|t24max|t24min|w12|w24
33463391|59287 |2013-01-14|645 |18.0 |7.0 |0 |0
33461636|59287 |2013-01-14|500 |20.0 |9.0 |0 |1我用如下代码:
SELECT DISTINCT a.pid,a.station_id,a.datetime,a.ctime,a.t24max,a.t24min,b.w12 AS w12code,b.w12 AS w24code
FROM v_forecast_2008_item a JOIN v_forecast_2008_item b ON (a.pid = b.pid)
WHERE a.datetime>='2013-1-14'
AND (a.hr=24 AND b.hr=12)
AND a.station_id='59287'
ORDER BY a.datetime DESC,a.ctime DESC,a.station_id DESC得到如下结果:(明显是错的,ctime=500那行的w24应该是1
pid |station_id|datetime |ctime|t24max|t24min|w12|w24
33463391|59287 |2013-01-14|645 |18.0 |7.0 |0 |0
33461636|59287 |2013-01-14|500 |20.0 |9.0 |0 |0请问如果修改上面的代码才能得到正确的结果?
FROM v_forecast_2008_item a left join v_forecast_2008_item b
on a.station_id = b.station_id and a.datetime = b.datetime and b.hr = 24
WHERE a.datetime>='2013-1-14'
AND a.hr=24
AND a.station_id='59287'
ORDER BY a.datetime DESC,a.ctime DESC
FROM v_forecast_2008_item a left join v_forecast_2008_item b
on a.pid = b.pid and a.station_id = b.station_id and a.datetime = b.datetime and b.hr = 24
WHERE a.datetime>='2013-1-14'
AND a.hr=24
AND a.station_id='59287'
ORDER BY a.datetime DESC,a.ctime DESC
FROM v_forecast_2008_item a left join v_forecast_2008_item b
on a.pid = b.pid and a.station_id = b.station_id and a.datetime = b.datetime and b.hr = 12
WHERE a.datetime>='2013-1-14'
AND a.hr=24
AND a.station_id='59287'
ORDER BY a.datetime DESC,a.ctime DESC
Select
pid
,station_id
,datetime
,ctime
,MAX(Case when hr=24 Then t24max Else 0 End) As t24max
,Isnull(MIN(Case when hr=24 Then t24max End),0) As t24min
,MAX(Case when hr=12 Then w12 Else 0 End) As w12
,MAX(Case when hr=24 Then w12 Else 0 End) As w24
from (
SELECT station_id,datetime,ctime,hr,t24max,t24min,w12
FROM v_forecast_2008_item WHERE datetime>='2013-1-14'
AND (hr=12 OR hr=24)
AND station_id='59287'
--ORDER BY datetime DESC,ctime DESC
) t
Group by pid,station_id,datetime,ctime
Order by datetime desc,ctime desc