帮忙一下,在线等

yaalice 2004-05-08 03:26:07
我有这么一张表:
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每个月有一条记录,请大家帮忙实现一下,谢谢!
...全文
76 9 点赞 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yaalice 2004-05-09
如果我要以时间段来查询,那我的时间参数应该怎么加进去啊
  • 打赏
  • 举报
回复
yaalice 2004-05-09
nihao
  • 打赏
  • 举报
回复
楚人无衣 2004-05-08
经过测试,楼上更简洁,稍做更改:
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
  • 打赏
  • 举报
回复
楚人无衣 2004-05-08
去一点多余的,我也不知该怎么去优化,帮你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
  • 打赏
  • 举报
回复
qybao 2004-05-08
试试
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忘了,自己查一下

  • 打赏
  • 举报
回复
楚人无衣 2004-05-08
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
这个太长,我再看能不能简化
  • 打赏
  • 举报
回复
野草飞雪 2004-05-08
不要忘记,2月份需要判断任年任月。
  • 打赏
  • 举报
回复
野草飞雪 2004-05-08
能写,
用day(datetime),month(datetime)等函数,
再结合case when then end 语句
  • 打赏
  • 举报
回复
yaalice 2004-05-08
显示成这样就可以了
tt 144(156-12)
到底是哪个时间段是查询出来的
  • 打赏
  • 举报
回复
相关推荐
发帖
Delphi
加入

4969

社区成员

Delphi 开发及应用
申请成为版主
帖子事件
创建了帖子
2004-05-08 03:26
社区公告
暂无公告