我有这么一张表:
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每个月有一条记录,请大家帮忙实现一下,谢谢!
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每个月有一条记录,请大家帮忙实现一下,谢谢!
解决方案 »
- 关于BusinessSkinForm皮肤控件2个问题
- 在webbroser中把网页表单里面的autocomplete="off"改成autocomplete="on"
- 怎样实现类似批处理操作?
- 有想做兼职的没,在上海!
- 如何用语句生成一Sql Server2000数据库??
- 提供《ObjectPascal中文参考手册 》下载
- 怎样取得像“%Windir%”、“%UserProfile%”这样的系统变量的实际路径?
- FLOAT如何表示为十六进制格式?
- socket多进程问题,急
- 请问Delphi7中的diagram是作什么的,是做详细设计的吗?
- 超晕问题.难道delphi不能打开光盘上的数据库?
- 小弟第一次用 reportmachine,遇到2个小问题?弟兄们帮忙哦。。
用day(datetime),month(datetime)等函数,
再结合case when then end 语句
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
这个太长,我再看能不能简化
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.datetimesql 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.datetimeconvert函数对应是120还是112忘了,自己查一下
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