sampling_datetime site_id DUST
2008-5-1 1:00:00 4005 50.63
2008-5-1 2:00:00 4005 50.63
2008-5-1 3:00:00 4005 50.63
2008-5-1 4:00:00 4005 50.63
2008-5-1 5:00:00 4005 50.63
2008-5-1 6:00:00 4005 50.63
2008-5-1 7:00:00 4005 50.63
2008-5-1 8:00:00 4005 50.63
2008-5-2 1:00:00 4005 52.42
2008-5-2 2:00:00 4005 52.42
2008-5-2 3:00:00 4005 52.42
2008-5-2 4:00:00 4005 52.42
2008-5-2 5:00:00 4005 52.42
2008-5-2 6:00:00 4005 52.42
2008-5-2 7:00:00 4005 52.42
2008-5-2 8:00:00 4005 52.42我想得到是
sampling_datetime site_id DUST
2008-5-1 4005 405.64
2008-5-2 4005 419.36
2008-5-1 1:00:00 4005 50.63
2008-5-1 2:00:00 4005 50.63
2008-5-1 3:00:00 4005 50.63
2008-5-1 4:00:00 4005 50.63
2008-5-1 5:00:00 4005 50.63
2008-5-1 6:00:00 4005 50.63
2008-5-1 7:00:00 4005 50.63
2008-5-1 8:00:00 4005 50.63
2008-5-2 1:00:00 4005 52.42
2008-5-2 2:00:00 4005 52.42
2008-5-2 3:00:00 4005 52.42
2008-5-2 4:00:00 4005 52.42
2008-5-2 5:00:00 4005 52.42
2008-5-2 6:00:00 4005 52.42
2008-5-2 7:00:00 4005 52.42
2008-5-2 8:00:00 4005 52.42我想得到是
sampling_datetime site_id DUST
2008-5-1 4005 405.64
2008-5-2 4005 419.36
解决方案 »
- 求一SQL语句..
- 如何实现这样表的选择????
- 急!!!!! 時間類型轉成字符類型能不能不是英文?
- 初用sqlserver,问一个幼齿问题。
- 怎样写这样的语句
- 继刚才二SQL合并..请懒猫看一下
- 请求一个连接问题的帮忙
- 求援:最难的一种数据转换要求了,网上找了半天也未找到答案。。。
- 菜鸟免进!SQL Server 2000之日志传送功能的三个问题,弄了两天,实在郁闷了,上来请高手帮忙!
- 哪位高手可以讲讲MASTER库中各个表之间的关系吗?
- SQL的查询条件WHERE语句中的 “a.cd *= b.cd” 是什么意思?
- 邹健:你好,知道你很忙,不过关于你blog上的一个问题,还是请你指教一下 :-) 媚笑 .----
set nocount on
create table test(sampling_datetime varchar(20),site_id varchar(20),DUST float)
insert into test select '2008-5-1 1:00:00','4005','50.63'
insert into test select '2008-5-1 2:00:00','4005','50.63'
insert into test select '2008-5-1 3:00:00','4005','50.63'
insert into test select '2008-5-1 4:00:00','4005','50.63'
insert into test select '2008-5-1 5:00:00','4005','50.63'
insert into test select '2008-5-1 6:00:00','4005','50.63'
insert into test select '2008-5-1 7:00:00','4005','50.63'
insert into test select '2008-5-1 8:00:00','4005','50.63'
insert into test select '2008-5-2 1:00:00','4005','52.42'
insert into test select '2008-5-2 2:00:00','4005','52.42'
insert into test select '2008-5-2 3:00:00','4005','52.42'
insert into test select '2008-5-2 4:00:00','4005','52.42'
insert into test select '2008-5-2 5:00:00','4005','52.42'
insert into test select '2008-5-2 6:00:00','4005','52.42'
insert into test select '2008-5-2 7:00:00','4005','52.42'
insert into test select '2008-5-2 8:00:00','4005','52.42'
go
--测试
select convert(varchar(10),cast(sampling_datetime as datetime),120)
,site_id,sum(dust) from test
group by convert(varchar(10),cast(sampling_datetime as datetime),120)
,site_id--删除测试环境
drop table test
set nocount off
site_id,sum(dust) as dust
from tableA
group by convert(char(10),sampling_datetime,21),site_id