表 a
 
DateAndTime                    TagIndex              Val
2006-10-6 15:51:00                 1                11113367.258
2006-10-6 15:51:00                 2                0.253678923
2006-10-6 15:51:00                 3                1125.68792
2006-10-6 15:51:00                 4                  1
2006-10-6 15:59:00                 1                11115367.258
2006-10-6 15:59:00                 2                0.2536789
2006-10-6 15:59:00                 3                1125.687921566458
2006-10-6 15:59:00                 4                  0
2006-10-6 16:59:00                 1                11158967.586
2006-10-6 16:59:00                 2                0.2576545454
2006-10-6 16:59:00                 3                1125.45468784565456
2006-10-6 16:59:00                 4                   1
表b
DateAndTime        Val1                  Val3                 Val4
   15         11115367.258              1125.687921566458      0
   16          11158967.586             1125.45468784565456    1
想实现的是这样的语句
要求:
条件1:读取上个小时的表a的TagIndex列的1.3.4行的Val列的数据对应写到表b的Val1,Val3,Val4
条件2:假如上个小时有多次数据,只选取时间最大的那次. 
条件3:并且把小时的数值写到表b的DateAndTime列上或者用别的办法把小时数写进去也可以
条件4:添加到b表的最后一行
最好能写个详细的注释,谢谢啦

解决方案 »

  1.   

    create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
    insert into a select '2006-10-6 15:51:00',1,11113367.258
    insert into a select '2006-10-6 15:51:00',2,0.253678923
    insert into a select '2006-10-6 15:51:00',3,1125.68792
    insert into a select '2006-10-6 15:51:00',4,1
    insert into a select '2006-10-6 15:59:00',1,11115367.258
    insert into a select '2006-10-6 15:59:00',2,0.2536789
    insert into a select '2006-10-6 15:59:00',3,1125.687921566458
    insert into a select '2006-10-6 15:59:00',4,0
    insert into a select '2006-10-6 16:59:00',1,11158967.586
    insert into a select '2006-10-6 16:59:00',2,0.2576545454
    insert into a select '2006-10-6 16:59:00',3,1125.45468784565456
    insert into a select '2006-10-6 16:59:00',4,1
    create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
    goinsert into b 
    select 
        datepart(hh,t.DateAndTime),
        (select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=1 order by DateAndTime desc),
        (select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=3 order by DateAndTime desc),
        (select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=4 order by DateAndTime desc)
    from
        a t
    group by 
        datepart(hh,t.DateAndTime)select * from b
    go
    drop table a,b
      

  2.   

    select datepart(hour,dateandtime),val1=sum(case tagindex when 1 then val else 0 end) ,
    val3=sum(case tagindex when 3 then val else 0 end) ,
    val4=sum(case tagindex when 4 then val else 0 end) 
    from tableA a where not exists (select 1 from tableA b where a.tagindex=b.tagindex and convert(varchar(13),a.dateandtime,120)=convert(varchar(13),b.dateandtime,120) and a.dateandtime<b.dateandtime)
    group by datepart(hour,dateandtime)
      

  3.   

    select b.TheHour, sum(case TagIndex 1 then Val end) as Val1,
      sum(case TagIndex 3 then Val end) as Val3, sum(case TagIndex 4 then Val end) as Val4
    from tableA a 
    join (select datepear(Hour, DateAndTime) as TheHour, max(DateAndTime) as MaxDateAndTime
          from tableA
          group by datepear(Hour, DateAndTime) b on a.DateAndTime = b.MaxDateAndTime
    group by b.TheHour, b.MaxDateAndTime
      

  4.   

    --创建测试表结构
    create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
    create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
    go--向a表insert测试数据
    insert into a select '2006-10-6 15:51:00',1,11113367.258
    insert into a select '2006-10-6 15:51:00',2,0.253678923
    insert into a select '2006-10-6 15:51:00',3,1125.68792
    insert into a select '2006-10-6 15:51:00',4,1
    insert into a select '2006-10-6 15:59:00',1,11115367.258
    insert into a select '2006-10-6 15:59:00',2,0.2536789
    insert into a select '2006-10-6 15:59:00',3,1125.687921566458
    insert into a select '2006-10-6 15:59:00',4,0
    insert into a select '2006-10-6 16:59:00',1,11158967.586
    insert into a select '2006-10-6 16:59:00',2,0.2576545454
    insert into a select '2006-10-6 16:59:00',3,1125.45468784565456
    insert into a select '2006-10-6 16:59:00',4,1
    go
    --执行insert查询
    insert into b(DateAndTime,Val1,Val3,Val4)
    select
        datepart(hh,a.DateAndTime),
        max(case a.TagIndex when 1 then a.Val end),
        max(case a.TagIndex when 3 then a.Val end),
        max(case a.TagIndex when 4 then a.Val end)
    from
        a,
        (select max(DateAndTime) as DateAndTime from a group by convert(char(13),DateAndTime,120)) c
    where
        a.DateAndTime=c.DateAndTime
    group by
        datepart(hh,a.DateAndTime)
    go
    --查看结果
    select * from b
    go
    --清除测试环境
    drop table a,b
      

  5.   

    后面的两位哥哥,只有查询没有写入的办法呀
    ------------------------------------------------------------------------------------
    每一个查询前加上这一句即可:
    insert into b(DateAndTime,Val1,Val3,Val4)  
      

  6.   

    INSERT tablename(..)
    select datepart(hour,dateandtime),val1=sum(case tagindex when 1 then val else 0 end) ,
    val3=sum(case tagindex when 3 then val else 0 end) ,
    val4=sum(case tagindex when 4 then val else 0 end) 
    from tableA a where not exists (select 1 from tableA b where a.tagindex=b.tagindex and convert(varchar(13),a.dateandtime,120)=convert(varchar(13),b.dateandtime,120) and a.dateandtime<b.dateandtime)
    group by datepart(hour,dateandtime)
      

  7.   

    create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
    insert into a select '2006-10-8 15:51:00',1,11113367.258
    insert into a select '2006-10-8 15:51:00',2,0.253678923
    insert into a select '2006-10-8 15:51:00',3,1125.68792
    insert into a select '2006-10-8 15:51:00',4,1
    insert into a select '2006-10-8 15:59:00',1,11115367.258
    insert into a select '2006-10-8 15:59:00',2,0.2536789
    insert into a select '2006-10-8 15:59:00',3,1125.687921566458
    insert into a select '2006-10-8 15:59:00',4,0
    insert into a select '2006-10-8 16:59:00',1,11158967.586
    insert into a select '2006-10-8 16:59:00',2,0.2576545454
    insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
    insert into a select '2006-10-8 16:59:00',4,1
    create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
    goinsert into b(DateAndTime,Val1,Val3,Val4)
    select 
        datepart(hh,dateadd(hh,-1,getdate())),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=1 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=3 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
    go
    drop table a,b
    create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
    insert into a select '2006-10-8 15:51:00',1,11113367.258
    insert into a select '2006-10-8 15:51:00',2,0.253678923
    insert into a select '2006-10-8 15:51:00',3,1125.68792
    insert into a select '2006-10-8 15:51:00',4,1
    insert into a select '2006-10-8 15:59:00',1,11115367.258
    insert into a select '2006-10-8 15:59:00',2,0.2536789
    insert into a select '2006-10-8 15:59:00',3,1125.687921566458
    insert into a select '2006-10-8 15:59:00',4,0
    insert into a select '2006-10-8 16:59:00',1,11158967.586
    insert into a select '2006-10-8 16:59:00',2,0.2576545454
    insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
    insert into a select '2006-10-8 16:59:00',4,1
    create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
    goinsert into b(DateAndTime,Val1,Val3,Val4)
    select 
        datepart(hh,dateadd(hh,-1,getdate())),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=1 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=3 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
    go
    drop table a,b
      

  8.   

    或者用这一段:
    -------------------------------------------------------------------------------------------------------------------------------
    --创建测试表结构
    create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
    create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
    go--向a表insert测试数据
    insert into a select '2006-10-8 15:51:00',1,11113367.258
    insert into a select '2006-10-8 15:51:00',2,0.253678923
    insert into a select '2006-10-8 15:51:00',3,1125.68792
    insert into a select '2006-10-8 15:51:00',4,1
    insert into a select '2006-10-8 15:59:00',1,11115367.258
    insert into a select '2006-10-8 15:59:00',2,0.2536789
    insert into a select '2006-10-8 15:59:00',3,1125.687921566458
    insert into a select '2006-10-8 15:59:00',4,0
    insert into a select '2006-10-8 16:59:00',1,11158967.586
    insert into a select '2006-10-8 16:59:00',2,0.2576545454
    insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
    insert into a select '2006-10-8 16:59:00',4,1
    go
    --执行insert查询
    insert into b(DateAndTime,Val1,Val3,Val4)
    select
        datepart(hh,a.DateAndTime),
        max(case a.TagIndex when 1 then a.Val end),
        max(case a.TagIndex when 3 then a.Val end),
        max(case a.TagIndex when 4 then a.Val end)
    from
        a,
        (select max(DateAndTime) as DateAndTime from a group by convert(char(13),DateAndTime,120)) c
    where
        a.DateAndTime=c.DateAndTime
        and
        datediff(hh,a.DateAndTime,getdate())=1
    group by
        datepart(hh,a.DateAndTime)
    go
    --查看结果
    select * from b
    go
    --清除测试环境
    drop table a,b
      

  9.   

    可是还是出两行数据,不是只写入上个小时的数据,子陌哥哥
    我的想法是,每个小时写入一行数据,每天00:00:00到1:00:00输出该表并清空该表
    ------------------------------------------------------------------------------------------------------------------------
    上面两个帖子是修改后的,应该可以满足你的要求。
    另外,要实现在"每天00:00:00到1:00:00输出该表并清空该表",需要使用JOB。不过不明白这个输出是个什么操作?
      

  10.   

    哈,ok啦.
    子陌哥哥,最后实际写到作业里面用
    insert into b(DateAndTime,Val1,Val3,Val4)
    select 
        datepart(hh,dateadd(hh,-1,getdate())),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=1 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=3 order by DateAndTime desc),
        (select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
    go
    这个就可以吧?
    还有要是一次不是写3行而是10多行也没问题吧?
      

  11.   

    学习!!!
    顺便问候:
    libin_ftsafe(子陌红尘:当libin告别ftsafe) ( ) 
    !!!
      

  12.   

    以下是一个创建 JOB 的操作流程说明:企业管理器
    --管理
    --SQL Server代理
    --右键作业
    --新建作业
    --"常规"项中输入作业名称
    --"步骤"项
    --新建
    --"步骤名"中输入步骤名
    --"类型"中选择"Transact-SQL 脚本(TSQL)"
    --"数据库"选择执行命令的数据库
    --"命令"中输入要执行的语句:
                           TRANCATE TABLE a  --清空a表的数据 --确定
    --"调度"项
    --新建调度
    --"名称"中输入调度名称
    --"调度类型"中选择你的作业执行安排
    --如果选择"反复出现"
    --点"更改"来设置你的时间安排
    然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行设置方法:
    我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
      

  13.   

    这个就可以吧?
    还有要是一次不是写3行而是10多行也没问题吧?
    -----------------------------------------------------------------------------
    可以&可以。
      

  14.   

    --生成测试数据
    create table  testTime
    (
    DateAndTime datetime ,
    TagIndex int , 
    val real  
    )insert into testTime
    select  '2006-10-6 15:51:00' ,                1       ,         11113367.258   union
    select  '2006-10-6 15:51:00' ,                  2      ,          0.253678923   union
    select  '2006-10-6 15:51:00' ,                  3      ,          1125.68792   union
    select  '2006-10-6 15:51:00' ,                  4      ,            1   union
    select  '2006-10-6 15:59:00' ,                  1       ,         11115367.258   union
    select  '2006-10-6 15:59:00' ,                  2      ,          0.2536789   union
    select  '2006-10-6 15:59:00' ,                  3      ,          1125.687921566458   union
    select  '2006-10-6 15:59:00' ,                  4      ,            0   union
    select  '2006-10-6 16:59:00' ,                  1      ,          11158967.586   union
    select  '2006-10-6 16:59:00' ,                  2      ,          0.2576545454   union
    select  '2006-10-6 16:59:00' ,                  3      ,          1125.45468784565456   union
    select  '2006-10-6 16:59:00' ,                  4      ,             1--显示测试数据
    select * from testtime/*
    2006-10-06 15:51:00.000 1 1.1113367E+7
    2006-10-06 15:51:00.000 2 0.25367892
    2006-10-06 15:51:00.000 3 1125.6879
    2006-10-06 15:51:00.000 4 1.0
    2006-10-06 15:59:00.000 1 1.1115367E+7
    2006-10-06 15:59:00.000 2 0.25367889
    2006-10-06 15:59:00.000 3 1125.6879
    2006-10-06 15:59:00.000 4 0.0
    2006-10-06 16:59:00.000 1 1.1158968E+7
    2006-10-06 16:59:00.000 2 0.25765455
    2006-10-06 16:59:00.000 3 1125.4547
    2006-10-06 16:59:00.000 4 1.0
    */--生成结果表
    create table  testTimeRlt
    (
    DateAndTime varchar(2) ,
    Val1 real , 
    Val2 real , 
    Val3 real , 
    )--执行查询 并且插入数据表
    --这里声明了个时间变量  如果符合当前时间
    --声明可以去掉 , 直接用 getdate() 代替就可以了
    declare @nowTime datetime
     set @nowTime = '2006-10-6 16:00:00'insert into testTimeRlt
    select 
    convert(varchar(2) , DateAndTime , 114) as DateAndTime ,
    max(case  when TagIndex = 1 then val  end) as Val1,
    max(case  when TagIndex = 3 then val  end) as Val3,
    max(case  when TagIndex = 4 then val end) as Val4
    from testTime
    where DateAndTime = 
    (
    select max(DateAndTime) as DateAndTime
    from testTime
    where DateAndTime < @nowTime and  DateAndTime >= dateadd(Hour , -1 ,@nowTime )
    )
    group by DateAndTime
    --察看结果
    select * from testTimeRlt/*
    DateAndTime   Val1     Val3        Val4
    ----------------------------------------------------
    15  1.1115367E+7 1125.6879     0.0
    */
      

  15.   

    select a.DateAndTime,
           val1=sum(case a.tagindex when 1 then val else 0 end) ,
           val3=sum(case a.tagindex when 3 then val else 0 end) ,
           val4=sum(case a.tagindex when 4 then val else 0 end) 
    (select to_date(DateAndTime,'hh') DateAndTime,
           TagIndex,
           Val,
           row_number() over (partition by to_date(DateAndTime,'hh'),TagIndex order by val desc) val_row
    from tableA)
    where val_row = '1'
      

  16.   

    --生成测试数据
    create table  testTime
    (
    DateAndTime datetime ,
    TagIndex int , 
    val real  
    )insert into testTime
    select  '2006-10-6 15:51:00' ,                1       ,         11113367.258   union
    select  '2006-10-6 15:51:00' ,                  2      ,          0.253678923   union
    select  '2006-10-6 15:51:00' ,                  3      ,          1125.68792   union
    select  '2006-10-6 15:51:00' ,                  4      ,            1   union
    select  '2006-10-6 15:59:00' ,                  1       ,         11115367.258   union
    select  '2006-10-6 15:59:00' ,                  2      ,          0.2536789   union
    select  '2006-10-6 15:59:00' ,                  3      ,          1125.687921566458   union
    select  '2006-10-6 15:59:00' ,                  4      ,            0   union
    select  '2006-10-6 16:59:00' ,                  1      ,          11158967.586   union
    select  '2006-10-6 16:59:00' ,                  2      ,          0.2576545454   union
    select  '2006-10-6 16:59:00' ,                  3      ,          1125.45468784565456   union
    select  '2006-10-6 16:59:00' ,                  4      ,             1
      

  17.   

    学习!!!
    顺便问候:
    libin_ftsafe(子陌红尘:当libin告别ftsafe) ( ) 真的了不起拉.我刚进来不久只有学习的份.
      

  18.   

    if exists(select * from tempdb..sysobjects where object_id('tempdb..#a')=id)
    drop table #a
    if exists(select * from tempdb..sysobjects where object_id('tempdb..#b')=id)
    drop table #b
    gocreate table #a(DateandTime datetime,TagIndex int,Val varchar(20))
    insert into #a select '2006-10-6 15:51:00',1,11113367.258
    insert into #a select '2006-10-6 15:51:00',2,0.253678923
    insert into #a select '2006-10-6 15:51:00',3,1125.68792
    insert into #a select '2006-10-6 15:51:00',4,1
    insert into #a select '2006-10-6 15:59:00',1,11115367.258
    insert into #a select '2006-10-6 15:59:00',2,0.2536789
    insert into #a select '2006-10-6 15:59:00',3,1125.687921566458
    insert into #a select '2006-10-6 15:59:00',4,0
    insert into #a select '2006-10-6 16:59:00',1,11158967.586
    insert into #a select '2006-10-6 16:59:00',2,0.2576545454
    insert into #a select '2006-10-6 16:59:00',3,1125.45468784565456
    insert into #a select '2006-10-6 16:59:00',4,1
    create table #b(DateandTime int,Val1 varchar(20),Val3 varchar(20),Val4 varchar(20))
    goselect * from #a
    /*
    DateAndTime                    TagIndex              Val
    2006-10-6 15:51:00                 1                11113367.258
    2006-10-6 15:51:00                 2                0.253678923
    2006-10-6 15:51:00                 3                1125.68792
    2006-10-6 15:51:00                 4                  1
    2006-10-6 15:59:00                 1                11115367.258
    2006-10-6 15:59:00                 2                0.2536789
    2006-10-6 15:59:00                 3                1125.687921566458
    2006-10-6 15:59:00                 4                  0
    2006-10-6 16:59:00                 1                11158967.586
    2006-10-6 16:59:00                 2                0.2576545454
    2006-10-6 16:59:00                 3                1125.45468784565456
    2006-10-6 16:59:00                 4                   1
    */
    insert #b
    select datepart(hour,b.DateandTime),b.Val,c.Val,d.Val from(select max(convert(varchar(16),DateandTime,120)) max_date from #a group by convert(varchar(13),DateandTime,120)) a join #a b on max_date=b.DateandTime and b.TagIndex=1 join #a c on max_date=c.DateandTime and c.TagIndex=3 join #a d on max_date=d.DateandTime and d.TagIndex=4select * from #b
    /*
    DateAndTime        Val1                  Val3                 Val4
       15         11115367.258              1125.687921566458      0
       16          11158967.586             1125.45468784565456    1
    */
      

  19.   

    if exists(select * from tempdb..sysobjects where object_id('tempdb..#a')=id)
    drop table #a
    if exists(select * from tempdb..sysobjects where object_id('tempdb..#b')=id)
    drop table #b
    gocreate table #a(DateandTime datetime,TagIndex int,Val varchar(20))
    insert into #a select '2006-10-6 15:51:00',1,11113367.258
    insert into #a select '2006-10-6 15:51:00',2,0.253678923
    insert into #a select '2006-10-6 15:51:00',3,1125.68792
    insert into #a select '2006-10-6 15:51:00',4,1
    insert into #a select '2006-10-6 15:59:00',1,11115367.258
    insert into #a select '2006-10-6 15:59:00',2,0.2536789
    insert into #a select '2006-10-6 15:59:00',3,1125.687921566458
    insert into #a select '2006-10-6 15:59:00',4,0
    insert into #a select '2006-10-6 16:59:00',1,11158967.586
    insert into #a select '2006-10-6 16:59:00',2,0.2576545454
    insert into #a select '2006-10-6 16:59:00',3,1125.45468784565456
    insert into #a select '2006-10-6 16:59:00',4,1
    create table #b(DateandTime int,Val1 varchar(20),Val3 varchar(20),Val4 varchar(20))
    goselect * from #a
    /*
    DateAndTime                    TagIndex              Val
    2006-10-6 15:51:00                 1                11113367.258
    2006-10-6 15:51:00                 2                0.253678923
    2006-10-6 15:51:00                 3                1125.68792
    2006-10-6 15:51:00                 4                  1
    2006-10-6 15:59:00                 1                11115367.258
    2006-10-6 15:59:00                 2                0.2536789
    2006-10-6 15:59:00                 3                1125.687921566458
    2006-10-6 15:59:00                 4                  0
    2006-10-6 16:59:00                 1                11158967.586
    2006-10-6 16:59:00                 2                0.2576545454
    2006-10-6 16:59:00                 3                1125.45468784565456
    2006-10-6 16:59:00                 4                   1
    */
    insert #b
    select  datepart(hour,DateandTime),val1=max(case tagindex when 1 then val  end) ,
    val3=max(case tagindex when 3 then val  end) ,
    val4=max(case tagindex when 4 then val  end) 
    from #a join (select max(DateandTime) max_date from #a) b on max_date=DateandTime
    group by datepart(hour,DateandTime)select * from #b
    /*
    DateAndTime        Val1                  Val3                 Val4
       16          11158967.586             1125.45468784565456    1
    */