帮忙一下，在线等

[yaalice]

我有这么一张表：
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)
这样能不能写SQL语句实现，当然有很多不同的khname，就是每个khnane每个月有一条记录，请大家帮忙实现一下，谢谢！

[yaalice]

显示成这样就可以了
tt 144(156-12)
到底是哪个时间段是查询出来的

[野草飞雪]

能写，
用day(datetime),month(datetime)等函数,
再结合case when then end 语句

[野草飞雪]

不要忘记，2月份需要判断任年任月。

[楚人无衣]

select a.khname, Convert(Char(7), a.datetime, 20) as datetime, e.data-f.data as data
from
(select a.khname, Convert(Char(7), a.datetime, 20) as datetime
from tbl a group by Convert(Char(7), a.datetime, 20), a.khname) a,
(select data, Convert(Char(7), oa.datetime, 20) as datetime from tbl oa,
(select max(Convert(Char(10), datetime, 20)) as datetime, khname from tbl group by Convert(Char(7), datetime, 20), khname) c
where Convert(Char(10), oa.datetime, 20)=c.datetime) e,
(select data, Convert(Char(7), ob.datetime, 20) as datetime from tbl ob,
(select min(Convert(Char(10), datetime, 20)) as datetime, khname from tbl group by Convert(Char(7), datetime, 20), khname) d
where Convert(Char(10), ob.datetime, 20)=d.datetime) f
where a.datetime=e.datetime and a.datetime=f.datetime
这个太长，我再看能不能简化

[qybao]

试试
oracle数据库

select A.khname, A.yearmonth, (B.data-C.data)
from (select khname, to_char(datetime, 'YYYY-MM') as yearmonth, max(datetime) as maxdatetime, min(datetime) as mindatetime from you_table group by khname, yearmonth) A,
your_table B,
your_table C
where A.khname = B.khname
and A.khname = C.khname
and A.maxdatetime = B.datetime
and A.mindatetime = C.datetime

sql server 数据库
select A.khname, A.yearmonth, (B.data-C.data)
from (select khname, convert(char(7), datetime, 120) as yearmonth, max(datetime) as maxdatetime, min(datetime) as mindatetime from you_table group by khname, yearmonth) A,
your_table B,
your_table C
where A.khname = B.khname
and A.khname = C.khname
and A.maxdatetime = B.datetime
and A.mindatetime = C.datetime

convert函数对应是120还是112忘了，自己查一下

[楚人无衣]

去一点多余的，我也不知该怎么去优化，帮你up，期待楼下会有更好的
select a.khname, Convert(Char(7), a.datetime, 20) as datetime, e.data-f.data as data
from
(select khname, Convert(Char(7), datetime, 20) as datetime
from tbl group by Convert(Char(7), datetime, 20), khname) a,
(select data, Convert(Char(7), oa.datetime, 20) as datetime from tbl oa,
(select max(Convert(Char(10), datetime, 20)) as datetime, khname from tbl group by Convert(Char(7), datetime, 20), khname) c
where Convert(Char(10), oa.datetime, 20)=c.datetime) e,
(select data, Convert(Char(7), ob.datetime, 20) as datetime from tbl ob,
(select min(Convert(Char(10), datetime, 20)) as datetime, khname from tbl group by Convert(Char(7), datetime, 20), khname) d
where Convert(Char(10), ob.datetime, 20)=d.datetime) f
where a.datetime=e.datetime and a.datetime=f.datetime

[楚人无衣]

经过测试，楼上更简洁，稍做更改：
group by khname, yearmonth->group by khname, convert(char(7), datetime, 120)
...
select A.khname, A.yearmonth, (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 tbl group by khname, convert(char(7), datetime, 120)) A,
tbl B,
tbl C
where A.khname = B.khname
and A.khname = C.khname
and A.maxdatetime = B.datetime
and A.mindatetime = C.datetime

[yaalice]

nihao

[yaalice]

如果我要以时间段来查询，那我的时间参数应该怎么加进去啊