技術不只是技術

 

一、建立測試資料表

         CREATE TABLE [dbo].[test]
         (
          [recvtime] [datetime] NULL,
          [tag1] [int] NULL,
          [tag2] [int] NULL,
          [tag3] [int] NULL,
          [tag4] [int] NULL,
          [tag5] [int] NULL
         ) ON [PRIMARY]

二、建立測試資料

        Insert Into test values ('2010-04-11 06:01:34.000','56','98','34','25','134')
        Insert Into test values ('2010-04-11 06:02:34.000','48','88','24','20','135')
        Insert Into test values ('2010-04-11 06:03:34.000','59','95','39','31','129')
        Insert Into test values ('2010-04-11 06:04:34.000','51','98','41','19','140')
        Insert Into test values ('2010-04-11 06:05:34.000','49','101','33','27','131')
        Insert Into test values ('2010-04-11 06:06:34.000','55','99','30','24','142')
        Insert Into test values ('2010-04-11 07:07:34.000','20','12','33','67','130')
        Insert Into test values ('2010-04-11 07:16:34.000','60','34','45','99','142')

三、查詢語法

        Select ta.name as '監測點', ta.hour_time,
          ta.tag_Avg as '平均值', ta.tag_Max as '最大值',
          (
          Case When ta.name = 'tag1' Then (select top 1 recvtime From test te1 Where ta.tag_Max = te1.tag1)
            When ta.name = 'tag2' Then (select top 1 recvtime From test te2 Where ta.tag_Max = te2.tag2)
            When ta.name = 'tag3' Then (select top 1 recvtime From test te3 Where ta.tag_Max = te3.tag3)
            When ta.name = 'tag4' Then (select top 1 recvtime From test te4 Where ta.tag_Max = te4.tag4)
            When ta.name = 'tag5' Then (select top 1 recvtime From test te5 Where ta.tag_Max = te5.tag5)
          end
          ) as '最大值的時間',
          ta.tag_Min as '最小值',
          (
          Case When ta.name = 'tag1' Then (select top 1 recvtime From test te1 Where ta.tag_Min = te1.tag1)
            When ta.name = 'tag2' Then (select top 1 recvtime From test te2 Where ta.tag_Min = te2.tag2)
            When ta.name = 'tag3' Then (select top 1 recvtime From test te3 Where ta.tag_Min = te3.tag3)
            When ta.name = 'tag4' Then (select top 1 recvtime From test te4 Where ta.tag_Min = te4.tag4)
            When ta.name = 'tag5' Then (select top 1 recvtime From test te5 Where ta.tag_Min = te5.tag5)
          end
          ) as '最小值的時間'
          from
         (
          Select sc.name,
            DATEADD(minute,DATEDIFF(minute,0,T.recvtime)/60*60,0) as hour_time,
            Case When sc.name = 'tag1' Then Avg(tag1)
              When sc.name = 'tag2' Then Avg(tag2)
              When sc.name = 'tag3' Then Avg(tag3)
              When sc.name = 'tag4' Then Avg(tag4)
              When sc.name = 'tag5' Then Avg(tag5)
             End AS tag_Avg,
            Case When sc.name = 'tag1' Then Max(tag1)
              When sc.name = 'tag2' Then Max(tag2)
              When sc.name = 'tag3' Then Max(tag3)
              When sc.name = 'tag4' Then Max(tag4)
              When sc.name = 'tag5' Then Max(tag5)
             End AS tag_Max,
            Case When sc.name = 'tag1' Then Min(tag1)
              When sc.name = 'tag2' Then Min(tag2)
              When sc.name = 'tag3' Then Min(tag3)
              When sc.name = 'tag4' Then Min(tag4)
              When sc.name = 'tag5' Then Min(tag5)
             End AS tag_Min
            From sys.columns sc, test T
           Where sc.object_id = '213575799'
             And sc.name <> 'recvtime'
           Group by DATEADD(minute,DATEDIFF(minute,0,T.recvtime)/60*60,0), sc.name
         ) ta
        order by hour_time,  ta.name