SELECT truck, task_no, LOAD_WGT, LOAD_MEAS, CASE WHEN getdate()
> REQUARRIVETIME THEN state_loc END as state_loc,
case when getdate()
> REQUARRIVETIME then REQUARRIVETIME end as REQUARRIVETIME
FROM (SELECT a.TRUCK, a.task_no, a.LOAD_WGT, a.LOAD_MEAS, a.state_loc,
c.REQUARRIVETIME
FROM (SELECT z.*
FROM flttask x INNER JOIN
fltbkdtl_cn z ON x.TASK_NO = z.TASK_NO AND
(x.TASK_END_TIME IS NULL OR
x.TASK_END_TIME = '') AND z.REQUARRIVETIME <> '')
c INNER JOIN
flttaskstate a ON a.task_no = c.TASK_NO
GROUP BY a.TRUCK, a.task_no, a.LOAD_WGT, a.LOAD_MEAS,
c.REQUARRIVETIME, a.state_loc) c
查出如下数据:
truck task_no LOAD_WGT LOAD_MEAS state_loc REQUARRIVETIME
JJ200 123 1.0 2.0 浙江 2006-08-03 12:02:12
JJ200 1234 2.0 2.0 NULL NULL
JJ200 12345 3.0 3.0 上海 2006-08-03 12:48:12想得到
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 上海 6.0 7.0state_loc是根据REQUARRIVETIME最大的时间来得到的;要求在原语句上修改,
我这样写:
SELECT truck,max(REQUARRIVETIME)as REQUARRIVETIME, SUM(load_wgt) AS load_wgt, SUM(load_meas) AS load_meas
FROM (上面的语句) d
GROUP BY truck 却得到的是:
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 浙江 6.0 7.0 不是我要的数据.请帮忙看看谢谢!
> REQUARRIVETIME THEN state_loc END as state_loc,
case when getdate()
> REQUARRIVETIME then REQUARRIVETIME end as REQUARRIVETIME
FROM (SELECT a.TRUCK, a.task_no, a.LOAD_WGT, a.LOAD_MEAS, a.state_loc,
c.REQUARRIVETIME
FROM (SELECT z.*
FROM flttask x INNER JOIN
fltbkdtl_cn z ON x.TASK_NO = z.TASK_NO AND
(x.TASK_END_TIME IS NULL OR
x.TASK_END_TIME = '') AND z.REQUARRIVETIME <> '')
c INNER JOIN
flttaskstate a ON a.task_no = c.TASK_NO
GROUP BY a.TRUCK, a.task_no, a.LOAD_WGT, a.LOAD_MEAS,
c.REQUARRIVETIME, a.state_loc) c
查出如下数据:
truck task_no LOAD_WGT LOAD_MEAS state_loc REQUARRIVETIME
JJ200 123 1.0 2.0 浙江 2006-08-03 12:02:12
JJ200 1234 2.0 2.0 NULL NULL
JJ200 12345 3.0 3.0 上海 2006-08-03 12:48:12想得到
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 上海 6.0 7.0state_loc是根据REQUARRIVETIME最大的时间来得到的;要求在原语句上修改,
我这样写:
SELECT truck,max(REQUARRIVETIME)as REQUARRIVETIME, SUM(load_wgt) AS load_wgt, SUM(load_meas) AS load_meas
FROM (上面的语句) d
GROUP BY truck 却得到的是:
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 浙江 6.0 7.0 不是我要的数据.请帮忙看看谢谢!
FROM (上面的语句) d
GROUP BY truck 却得到的是:
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 浙江 6.0 7.0 -------------------------------------
你都沒有把state_loc放到select里,怎麼可能出來這個結果呢?
上面的語句只會出來
truck REQUARRIVETIME LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 6.0 7.0 如果想把"上海"加上去,可以考慮用join
select A.truck,B.REQUARRIVETIME,A.state_loc,B.LOAD_WGT,B.LOAD_MEAS
from (上面的语句) A
inner join
(SELECT truck,max(REQUARRIVETIME)as REQUARRIVETIME, SUM(load_wgt) AS load_wgt, SUM(load_meas) AS load_meas FROM (上面的语句) d GROUP BY truck ) B
on A.truck=B.truck and A.REQUARRIVETIME=B.REQUARRIVETIME
用你的语句查询的数据不对,查询出来的结果是:
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 浙江 6.0 7.0 我要的结果:
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
JJ200 2006-08-03 12:48:12 上海 6.0 7.0state_loc是根据REQUARRIVETIME最大的时间来得到的
create table t(truck varchar(10),task_no int,load_wgt int,load_meas int,state_loc varchar(10),requarrivetime datetime)
insert into t select 'JJ200',123,1,2,'zhejiang','2006-08-03 12:02:12'
insert into t select 'JJ200',1234,2,2,null,null
insert into t select 'JJ200',12345,3,3,'shanghai','2006-08-03 12:48:12'select A.truck,B.REQUARRIVETIME,A.state_loc,B.LOAD_WGT,B.LOAD_MEAS
from t A
inner join
(SELECT truck,max(REQUARRIVETIME)as REQUARRIVETIME, SUM(load_wgt) AS load_wgt, SUM(load_meas) AS load_meas FROM t GROUP BY truck ) B
on A.truck=B.truck and A.REQUARRIVETIME=B.REQUARRIVETIME/*The result*/
truck REQUARRIVETIME state_loc LOAD_WGT LOAD_MEAS
---------- -------------------------------------------------------------
JJ200 2006-08-03 12:48:12.000 shanghai 6 7