当前位置：Gxlcms > 数据库问题 > 【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

时间：2021-07-01 10:21:17 帮助过：9人阅读

@table1 table(name nvarchar,createdate smalldatetime)
  • insert into @table1
  • select ‘a‘,‘2011-03-01 10:00:00‘
  • union all select ‘a‘,‘2011-03-01 11:00:00‘
  • union all select ‘a‘,‘2011-03-01 14:00:00‘
  • union all select ‘b‘,‘2011-03-01 13:00:00‘
  • union all select ‘b‘,‘2011-03-01 13:20:00‘
  • union all select ‘b‘,‘2011-03-01 14:00:00‘
  • select name,
  • createdate,
  • (select count(createdate)
  • from @table1 b
  • where a.name=b.name and
  • a.createdate<=b.createdate and
  • dateadd(hh,2,a.createdate) >= b.createdate
  • ) as count
  • from @table1 a
  • where not exists
  • (select 1 from
  • @table1 b
  • where a.name=b.name and
  • a.createdate>b.createdate and
  • a.createdate<dateadd(hh,2,b.createdate))
  • group by name,createdate


    • 但是这个解答其实是有问题的，当把临时表中的第3条数据的createdate改为‘2011-03-01 12:00:00‘，那么显示的结果是：

    name        createdate                           count
    a                2011-03-01 10:00:00        3
    b                2011-03-01 13:00:00        3

    在其中没有包括createdate为‘2011-03-01 12:00:00‘的记录，因为这个时间到为‘2011-03-01 10:00:00‘是超过2个小时了，也就是说为‘2011-03-01 10:00:00‘是第一个出现时间，到为‘2011-03-01 11:59:59‘为止，接下来应该是从‘2011-03-01 12:00:00‘开始的下个区间了，而这里显然是有问题的。

    以下是我写的解法，虽然效率不是太高，但是能解决这个问题：

    1. <code class="language-sql hljs"><ol class="hljs-ln hundred"><li><span class="hljs-keyword">declare</span> @table1 <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>,createdate smalldatetime)</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @table1</li><li><span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 10:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 11:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 12:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:10:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:20:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 14:30:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 15:15:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 16:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:00:00‘</span></li><li> </li><li> </li><li>  </li><li>;with aa  <span class="hljs-comment">--按照name分区，同时按照createdate排序编号</span></li><li>as</li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       ROW_NUMBER() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span></li><li>                             <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> createdate) <span class="hljs-keyword">as</span> k1</li><li><span class="hljs-keyword">from</span> @table1 </li><li>),</li><li> </li><li>r</li><li><span class="hljs-keyword">as</span></li><li>(</li><li><span class="hljs-keyword">select</span> v.name,</li><li>       starts=v.k1,  <span class="hljs-comment">--区间开始的编号</span></li><li>       </li><li>       ends=<span class="hljs-keyword">isnull</span>(</li><li>               <span class="hljs-keyword">min</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">and</span> <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,v.createdate) <= a.createdate </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                         <span class="hljs-keyword">else</span> <span class="hljs-literal">null</span> </li><li>                   <span class="hljs-keyword">end</span>)<span class="hljs-number">-1</span>,</li><li> </li><li>               <span class="hljs-keyword">max</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                        <span class="hljs-keyword">else</span> v.k1 </li><li>                   <span class="hljs-keyword">end</span>)</li><li>             ),     <span class="hljs-comment">--区间结尾的编号      </span></li><li>                             </li><li>           <span class="hljs-keyword">isnull</span>(</li><li>               <span class="hljs-keyword">min</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">and</span> <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,v.createdate) <= a.createdate </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                         <span class="hljs-keyword">else</span> <span class="hljs-literal">null</span> </li><li>                   <span class="hljs-keyword">end</span>)<span class="hljs-number">-1</span>,</li><li> </li><li>               <span class="hljs-keyword">max</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                        <span class="hljs-keyword">else</span> v.k1 </li><li>                   <span class="hljs-keyword">end</span>)</li><li>             ) - v.k1 <span class="hljs-keyword">as</span> diff     <span class="hljs-comment">--区间结尾编号与区间开始编号之间的差值</span></li><li>                              </li><li><span class="hljs-keyword">from</span> aa v  </li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> aa a </li><li>        <span class="hljs-keyword">on</span> v.name = a.name  <span class="hljs-comment">--只关联name相等的</span></li><li><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> v.name,</li><li>         v.k1</li><li><span class="hljs-keyword">having</span> <span class="hljs-keyword">isnull</span>(</li><li>               <span class="hljs-keyword">min</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">and</span> <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,v.createdate) <= a.createdate </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                         <span class="hljs-keyword">else</span> <span class="hljs-literal">null</span> </li><li>                   <span class="hljs-keyword">end</span>)<span class="hljs-number">-1</span>,</li><li> </li><li>               <span class="hljs-keyword">max</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1<a.k1 </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                        <span class="hljs-keyword">else</span> v.k1 </li><li>                   <span class="hljs-keyword">end</span>)</li><li>             )  >=v.k1   </li><li>        <span class="hljs-keyword">and</span>            </li><li>        <span class="hljs-keyword">isnull</span>(</li><li>               <span class="hljs-keyword">max</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1>a.k1 <span class="hljs-keyword">and</span> </li><li>                             <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">-2</span>,v.createdate) >= a.createdate</li><li>                             <span class="hljs-keyword">then</span> v.k1 - <span class="hljs-number">1</span> </li><li>                        <span class="hljs-keyword">else</span> <span class="hljs-literal">null</span> </li><li>                   <span class="hljs-keyword">end</span>) + <span class="hljs-number">1</span>,</li><li> </li><li>               <span class="hljs-keyword">min</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> v.k1>a.k1 </li><li>                             <span class="hljs-keyword">then</span> a.k1 </li><li>                        <span class="hljs-keyword">else</span> v.k1 </li><li>                   <span class="hljs-keyword">end</span>)</li><li>              ) = v.k1</li><li>)</li><li> </li><li><span class="hljs-comment">--select * from r</span></li><li> </li><li> </li><li><span class="hljs-keyword">select</span> aa.name,</li><li>       aa.createdate,</li><li>       diff + <span class="hljs-number">1</span></li><li><span class="hljs-keyword">from</span> r</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> aa </li><li>        <span class="hljs-keyword">on</span> aa.name = r.name</li><li>           <span class="hljs-keyword">and</span> aa.k1 =r.starts</li><li><span class="hljs-keyword">where</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span></li><li>
    2. (<span class="hljs-keyword">select</span> <span class="hljs-number">1</span></li><li>
    3.  <span class="hljs-keyword">from</span> r rr</li><li>
    4.  <span class="hljs-keyword">where</span> rr.name = r.name <span class="hljs-keyword">and</span></li><li>
    5.        rr.starts <> r.starts <span class="hljs-keyword">and</span></li><li>
    6.        rr.starts < r.starts <span class="hljs-keyword">and</span></li><li>
    7.        (rr.ends = r.ends <span class="hljs-keyword">or</span></li><li>
    8.         rr.ends = r.starts)</li><li>
    9. )           </li></ol></code>

     不过发现我的这个解法也是有问题的，最大的问题在与不能准确的确定上限在那里，还得继续考虑问题的解法。

    下面这个也是有问题的：

    1. <code class="language-sql hljs"><ol class="hljs-ln"><li><span class="hljs-keyword">declare</span> @table1 <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>,createdate smalldatetime)</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @table1</li><li><span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 10:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 11:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 12:00:00‘</span></li><li> </li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 13:10:00‘</span></li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 13:20:00‘</span></li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 14:30:00‘</span></li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 15:15:00‘</span></li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 16:00:00‘</span></li><li><span class="hljs-comment">--union all select ‘b‘,‘2011-03-01 17:15:00‘</span></li><li> </li><li> </li><li>  </li><li>;with a  <span class="hljs-comment">--按照name分区，同时按照createdate排序编号</span></li><li>as</li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       ROW_NUMBER() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span></li><li>                             <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> createdate) <span class="hljs-keyword">as</span> k1</li><li><span class="hljs-keyword">from</span> @table1 </li><li>),</li><li> </li><li>c</li><li><span class="hljs-keyword">as</span></li><li>(</li><li><span class="hljs-keyword">select</span> a1.name,</li><li>       a1.createdate,</li><li>       a1.k1,</li><li>       <span class="hljs-keyword">MIN</span>(a2.createdate) <span class="hljs-keyword">as</span> nextCreatedate,</li><li>       <span class="hljs-keyword">MIN</span>(a2.k1) <span class="hljs-keyword">as</span> nextK1</li><li><span class="hljs-keyword">from</span> a a1</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> a a2</li><li>        <span class="hljs-keyword">on</span> a1.name = a2.name</li><li>           <span class="hljs-keyword">and</span> a2.createdate < <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,a1.createdate)</li><li>           <span class="hljs-keyword">and</span> a2.createdate >= a1.createdate</li><li>           <span class="hljs-keyword">and</span> a1.k1 <= a2.k1</li><li><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> a1.name,</li><li>         a1.createdate,</li><li>         a1.k1</li><li>),</li><li> </li><li>w</li><li><span class="hljs-keyword">as</span></li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       k1</li><li>       </li><li>       <span class="hljs-comment">--null,</span></li><li>       <span class="hljs-comment">--null,</span></li><li>       <span class="hljs-comment">--null</span></li><li><span class="hljs-keyword">from</span> a</li><li><span class="hljs-keyword">where</span> k1 = <span class="hljs-number">1</span></li><li> </li><li><span class="hljs-keyword">union</span> all</li><li> </li><li><span class="hljs-keyword">select</span> c.name,</li><li>       c.nextCreatedate,</li><li>       c.nextK1</li><li>       </li><li><span class="hljs-keyword">from</span> W</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> a</li><li>        <span class="hljs-keyword">on</span> a.name = w.name</li><li>           <span class="hljs-keyword">and</span> <span class="hljs-keyword">dateadd</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,w.createdate) <= a.createdate</li><li>           <span class="hljs-keyword">and</span> w.k1 <= a.k1</li><li>           <span class="hljs-keyword">and</span> w.createdate <> <span class="hljs-string">‘2011-03-01 12:00:00‘</span> </li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> c</li><li>        <span class="hljs-keyword">on</span> w.name = c.name </li><li>           <span class="hljs-keyword">and</span> w.createdate = c.createdate</li><li>           <span class="hljs-keyword">and</span> w.k1 = c.k1</li><li><span class="hljs-keyword">where</span> w.k1 <=<span class="hljs-number">3</span></li><li>)</li><li> </li><li><span class="hljs-keyword">SELECT</span> * </li><li><span class="hljs-keyword">FROM</span> w</li></ol></code>


    下面的解法是正确的，不过用的是T-SQL，不是纯sql了：

    1. <code class="language-sql hljs"><ol class="hljs-ln"><li><span class="hljs-keyword">declare</span> @table1 <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">100</span>),createdate smalldatetime)</li><li> </li><li><span class="hljs-keyword">declare</span> @table2 <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">100</span>),createdate smalldatetime,rnum <span class="hljs-built_in">bigint</span>)</li><li> </li><li><span class="hljs-keyword">declare</span> @temp <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">100</span>),createdate smalldatetime,rnum <span class="hljs-built_in">bigint</span>)</li><li> </li><li><span class="hljs-keyword">declare</span> @i <span class="hljs-built_in">int</span> = <span class="hljs-number">1</span>;</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @table1</li><li><span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 10:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 11:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 12:00:00‘</span></li><li> </li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:10:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:20:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 14:30:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 15:15:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 16:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:16:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:15:00‘</span></li><li> </li><li>  </li><li>;with a  <span class="hljs-comment">--按照name分区，同时按照createdate排序编号</span></li><li>as</li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       ROW_NUMBER() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span></li><li>                             <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> createdate) <span class="hljs-keyword">as</span> k1</li><li><span class="hljs-keyword">from</span> @table1 </li><li>)</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @table2</li><li><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> a</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @temp</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       rnum</li><li><span class="hljs-keyword">from</span> @table2</li><li><span class="hljs-keyword">where</span> rnum = <span class="hljs-number">1</span></li><li> </li><li><span class="hljs-comment">--select * from @temp</span></li><li> </li><li> </li><li><span class="hljs-keyword">while</span> @i <= (<span class="hljs-keyword">select</span> <span class="hljs-keyword">MAX</span>(rnum) <span class="hljs-keyword">from</span> @table2)</li><li><span class="hljs-keyword">begin</span></li><li>    <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @temp</li><li>    </li><li>
    2. <span class="hljs-keyword">select</span> t2.name,</li><li>
    3.        <span class="hljs-keyword">min</span>(t2.createdate),</li><li>
    4.        @i +<span class="hljs-number">1</span>
    5. </li><li>
    6. <span class="hljs-keyword">from</span> @temp t1</li><li>
    7. <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> @table2 t2        </li><li>
    8.         <span class="hljs-keyword">on</span> t1.name = t2.name</li><li>
    9.            <span class="hljs-keyword">and</span> t2.createdate >= <span class="hljs-keyword">dateadd</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,t1.createdate)
    10.            </li><li>
    11. <span class="hljs-keyword">where</span> t1.rnum = @i     </li><li>
    12. <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> t2.name</li><li>
    13.             </li><li>
    14. <span class="hljs-keyword">set</span> @i = @i + <span class="hljs-number">1</span>
    15. </li><li><span class="hljs-keyword">end</span></li><li> </li><li>;with r</li><li>as</li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate</li><li><span class="hljs-keyword">from</span> @temp</li><li><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span>,</li><li>         createdate</li><li>)</li><li> </li><li><span class="hljs-keyword">select</span> r.name,</li><li>       r.createdate,</li><li>       <span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>)</li><li><span class="hljs-keyword">from</span> r</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> @table1 t</li><li>        <span class="hljs-keyword">on</span> t.name = r.name</li><li>           <span class="hljs-keyword">and</span> t.createdate >= r.createdate</li><li>           <span class="hljs-keyword">and</span> t.createdate <<span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">HOUR</span>,<span class="hljs-number">2</span>,r.createdate)</li><li><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> r.name,</li><li>         r.createdate           </li></ol></code>

     

    其实这个问题是个递归问题，由上一个找到下一个，但是得构造一下：

    1. <code class="language-sql hljs"><ol class="hljs-ln"><li><span class="hljs-keyword">declare</span> @table1 <span class="hljs-keyword">table</span>(<span class="hljs-keyword">name</span> <span class="hljs-keyword">nvarchar</span>,createdate smalldatetime)</li><li> </li><li><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> @table1</li><li><span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 10:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 11:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 12:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘a‘</span>,<span class="hljs-string">‘2011-03-01 12:20:00‘</span></li><li> </li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:10:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 13:20:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 14:30:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 15:15:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 16:00:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:20:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:15:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 19:16:00‘</span></li><li><span class="hljs-keyword">union</span> all <span class="hljs-keyword">select</span> <span class="hljs-string">‘b‘</span>,<span class="hljs-string">‘2011-03-01 17:15:00‘</span></li><li> </li><li>  </li><li>;with a  <span class="hljs-comment">--按照name分区，同时按照createdate排序编号</span></li><li>as</li><li>(</li><li><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,</li><li>       createdate,</li><li>       ROW_NUMBER() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span></li><li>                             <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> createdate) <span class="hljs-keyword">as</span> k1</li><li><span class="hljs-keyword">from</span> @table1 </li><li>),</li><li> </li><li>c   <span class="hljs-comment">--对于每个时间,找到大于这个时间2小时的时间中最小那个时间</span></li><li><span class="hljs-keyword">as</span></li><li>(</li><li><span class="hljs-keyword">select</span> a1.name,</li><li>       a1.createdate,</li><li>       a1.k1,</li><li>       <span class="hljs-keyword">MIN</span>(a2.createdate) <span class="hljs-keyword">as</span> nextCreatedate,</li><li>       <span class="hljs-keyword">MIN</span>(a2.k1) <span class="hljs-keyword">as</span> nextK1</li><li><span class="hljs-keyword">from</span> a a1</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> a a2</li><li>        <span class="hljs-keyword">on</span> a1.name = a2.name</li><li>           <span class="hljs-keyword">and</span> a2.createdate >= <span class="hljs-keyword">DATEADD</span>(<span class="hljs-keyword">hour</span>,<span class="hljs-number">2</span>,a1.createdate)</li><li>           </li><li><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> a1.name,</li><li>         a1.createdate,</li><li>         a1.k1</li><li> </li><li><span class="hljs-keyword">union</span> all</li><li> </li><li><span class="hljs-keyword">select</span> a.name,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>,a.createdate,<span class="hljs-number">1</span>  <span class="hljs-comment">--构造递归运行时需要的层级</span></li><li><span class="hljs-keyword">from</span> a</li><li><span class="hljs-keyword">where</span> k1 = <span class="hljs-number">1</span></li><li>),</li><li> </li><li>w   <span class="hljs-comment">--递归查询</span></li><li><span class="hljs-keyword">as</span></li><li>(</li><li><span class="hljs-keyword">select</span> c.name,</li><li>       c.createdate,</li><li>       c.k1,</li><li>       c.nextCreatedate,</li><li>       c.nextK1,</li><li>       <span class="hljs-number">1</span> <span class="hljs-keyword">as</span> lev</li><li><span class="hljs-keyword">from</span> c</li><li><span class="hljs-keyword">where</span> createdate <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span></li><li>      <span class="hljs-keyword">and</span> k1 <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span></li><li> </li><li><span class="hljs-keyword">union</span> all</li><li> </li><li><span class="hljs-keyword">select</span> c.name,</li><li>       c.createdate,</li><li>       c.k1,</li><li>       c.nextCreatedate,</li><li>       c.nextK1,</li><li>       lev + <span class="hljs-number">1</span></li><li><span class="hljs-keyword">from</span> W</li><li><span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> c</li><li>        <span class="hljs-keyword">on</span> w.name = c.name</li><li>           <span class="hljs-keyword">and</span> w.nextCreatedate = c.createdate</li><li> </li><li>)</li><li> </li><li><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">distinct</span> <span class="hljs-keyword">name</span>,</li><li>       nextCreatedate,</li><li>       nextK1 </li><li><span class="hljs-keyword">FROM</span> w</li></ol></code>



     

     

     

    • 点赞
    • 收藏
    • 分享
      • 文章举报
    技术图片 技术图片 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万+ 他的留言板 关注

    【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

    标签：col   关注   相等   from   访问   line   vat   temp   select   

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！