我有这么一张表:
id code khname datetime data
1 11 tt 2004-5-1 14:48:59 12
2 11 tt 2004-5-8 10:45:59 45
3 11 tt 2004-5-31 24:00:00 156
4 11 tt 2004-6-1 14:48:59 34
5 11 tt 2004-6-10 10:45:59 47
6 11 tt 2004-6-30 24:00:00 189我想把它显示成这样:
tt 2004-5 144(156-12 5月份的最后一天数据减去第一天的数据)
tt 2004-6 155(189-34)上次有位朋友帮我写了,确实实现了,现在我想它能按时间的年月查询,请问我应该怎么加时间参数查询?
select A.khname, A.maxdatetime,A.mindatetime, B.data as maxdata,C.data as mindata,(B.data-C.data) as data from
(select khname, convert(char(7), datetime, 120) as yearmonth, max(datetime) as maxdatetime, min(datetime) as mindatetime from ex
group by khname, convert(char(7), datetime, 120)) A,
ex B,
ex C
where
A.khname = B.khname
and A.khname = C.khname
and A.maxdatetime = B.datetime
and A.mindatetime = C.datetime
id code khname datetime data
1 11 tt 2004-5-1 14:48:59 12
2 11 tt 2004-5-8 10:45:59 45
3 11 tt 2004-5-31 24:00:00 156
4 11 tt 2004-6-1 14:48:59 34
5 11 tt 2004-6-10 10:45:59 47
6 11 tt 2004-6-30 24:00:00 189我想把它显示成这样:
tt 2004-5 144(156-12 5月份的最后一天数据减去第一天的数据)
tt 2004-6 155(189-34)上次有位朋友帮我写了,确实实现了,现在我想它能按时间的年月查询,请问我应该怎么加时间参数查询?
select A.khname, A.maxdatetime,A.mindatetime, B.data as maxdata,C.data as mindata,(B.data-C.data) as data from
(select khname, convert(char(7), datetime, 120) as yearmonth, max(datetime) as maxdatetime, min(datetime) as mindatetime from ex
group by khname, convert(char(7), datetime, 120)) A,
ex B,
ex C
where
A.khname = B.khname
and A.khname = C.khname
and A.maxdatetime = B.datetime
and A.mindatetime = C.datetime
and convert(char(6),a.mindatetime,112) like '200405' //2004-05
and convert(char(6),a.mindatetime,112) like '____05' //05