By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `scantech_data` (
`scdate` varchar(20),
`value` float
);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL);
SELECT *
FROM scantech_data
ORDER BY scdate
| scdate | value |
|---|---|
| 2021-11-10 17:00:00 | 1943.98 |
| 2021-11-10 17:00:18 | 1957.13 |
| 2021-11-10 17:00:36 | 1953.94 |
| 2021-11-10 17:00:54 | 1946.28 |
| 2021-11-10 17:01:12 | 1960.29 |
| 2021-11-10 17:01:29 | 1950.39 |
| 2021-11-10 17:01:47 | 1942.62 |
| 2021-11-10 17:02:05 | 1954.64 |
| 2021-11-10 17:02:23 | 1958.21 |
| 2021-11-10 17:02:41 | 1950.26 |
| 2021-11-10 17:02:58 | 1945.33 |
| 2021-11-10 17:03:16 | 1960.35 |
| 2021-11-10 17:04:33 | null |
SELECT T1.scdate,T1.value,T2.value,T3.value,
CASE WHEN T1.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T2.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T3.value>1945 THEN 1 ELSE 0 END AS TempCount
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0)
r,scantech_data t
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0)
r,scantech_data t
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0)
r,scantech_data t
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
| scdate | value | value | value | TempCount |
|---|---|---|---|---|
| 2021-11-10 17:00:00 | 1943.98 | 1957.13 | 1953.94 | 2 |
| 2021-11-10 17:00:18 | 1957.13 | 1953.94 | 1946.28 | 3 |
| 2021-11-10 17:00:36 | 1953.94 | 1946.28 | 1960.29 | 3 |
| 2021-11-10 17:00:54 | 1946.28 | 1960.29 | 1950.39 | 3 |
| 2021-11-10 17:01:12 | 1960.29 | 1950.39 | 1942.62 | 2 |
| 2021-11-10 17:01:29 | 1950.39 | 1942.62 | 1954.64 | 2 |
| 2021-11-10 17:01:47 | 1942.62 | 1954.64 | 1958.21 | 2 |
| 2021-11-10 17:02:05 | 1954.64 | 1958.21 | 1950.26 | 3 |
| 2021-11-10 17:02:23 | 1958.21 | 1950.26 | 1945.33 | 3 |
| 2021-11-10 17:02:41 | 1950.26 | 1945.33 | 1960.35 | 3 |
| 2021-11-10 17:02:58 | 1945.33 | 1960.35 | null | 2 |
| 2021-11-10 17:03:16 | 1960.35 | null | null | 1 |
| 2021-11-10 17:04:33 | null | null | null | 0 |