如何基于DateTime和其他变量计算SQL？ [英] How to count SQL based on a DateTime and an other variable?

问题描述

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

I have a SQL table with failed testresults:

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代表相关测试，就像这样：

This table is telling me that the test has failed, but I want to know if it is the first, second or third test. In the end I would like to do it with a logo where a x stands for a failed test, and a X for the concerned test, like this way:

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进行处理，但是我找不到从这种情况开始的地方。您能帮我吗？

It has to do something with COUNT and SUM, but I couldn't find out where to start with this case. Can you help me?

推荐答案

这似乎很奇怪，但我认为这可以满足您的要求：

This seems like a weird requirement, but I think this does what you want:

select stuff(replicate('x', count(*) over (partition by run, test)),
             row_number() over (partition by run, test order by starttime), 1, 'X'
            ) as status
from t;

这使用字符串操作来构造状态字符串。

This uses string operations to construct the status string.

这篇关于如何基于DateTime和其他变量计算SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！