sql - 如何根据 DateTime 和其他变量计算 SQL？

Question

我有一个测试结果失败的 SQL 表：

Run           Test              DateTime
1             20                2020-01-01 00:01
1             20                2020-01-01 00:00
1             20                2020-01-01 00:04
2             21                2020-01-01 00:10
2             21                2020-01-01 00:03

这张表告诉我测试失败了，但我想知道它是第一次、第二次还是第三次测试。最后，我想用一个标志来做，其中 ax 代表失败的测试，X 代表相关的测试，就像这样：

Run           Test              StartTime              Status
1             20                2020-01-01 00:01       xXx
1             20                2020-01-01 00:00       Xxx
1             20                2020-01-01 00:04       xxX
2             21                2020-01-01 00:10       xX
2             21                2020-01-01 00:03       Xx

它必须与 COUNT 和 SUM 做一些事情，但我找不到从这个案例开始的地方。你能帮助我吗？

标签： sqlsql-serverstringcountwindow-functions

Answer 1

基本上，你想要row_number()一个窗口count()：

select t.*, 
    row_number() over(partition by run, test order by datetime) rn,
    count(*) over(partition by run, test) cnt
from mytable t

从那里开始，您可以使用字符串函数生成“状态”：

select t.*, 
    replicate('x', rn - 1) + 'X' + replicate('x', cnt - rn) status
from (
    select t.*, 
        row_number() over(partition by run, test order by datetime) rn,
        count(*) over(partition by run, test) cnt
    from mytable t
) t

DB Fiddle 上的演示：

运行 | 测试 | 日期时间 | rn | cnt | 地位
--: | ---: | :--------------- | -: | --: | :-----
  1 | 20 | 2020-01-01 00:00 | 1 | 3 | xxx   
  1 | 20 | 2020-01-01 00:01 | 2 | 3 | xXx   
  1 | 20 | 2020-01-01 00:04 | 3 | 3 | xxx   
  2 | 21 | 2020-01-01 00:03 | 1 | 2 | XX    
  2 | 21 | 2020-01-01 00:10 | 2 | 2 | xX