回答編集履歴
3
修正
answer
CHANGED
|
@@ -13,7 +13,7 @@
|
|
|
13
13
|
from (
|
|
14
14
|
select *,FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm:ss') as 日時
|
|
15
15
|
-- 秒切り捨ての時間降順
|
|
16
|
-
,row_number() over(partition by
|
|
16
|
+
,row_number() over(partition by floor(時間/60) order by 時間 desc) as 順番
|
|
17
17
|
from test
|
|
18
18
|
) as X
|
|
19
19
|
where X.順番=1
|
2
追記
answer
CHANGED
|
@@ -1,6 +1,6 @@
|
|
|
1
1
|
あんまり効率いいクエリじゃないけどrow_number
|
|
2
2
|
~~秒の切り捨てはfloor(時間/100)で。~~
|
|
3
|
-
↑これだめやねちゃんとformatしましたm(_ _;)m
|
|
3
|
+
↑これだめやねちゃんとformatしましたm(_ _;)m→floor(時間/60)ならいいんかな？
|
|
4
4
|
```sql
|
|
5
5
|
with test as (
|
|
6
6
|
select 1520905810 as 時間,'5.5' as 列A,'6.6' as 列B
|
1
間違い訂正
answer
CHANGED
|
@@ -1,5 +1,6 @@
|
|
|
1
1
|
あんまり効率いいクエリじゃないけどrow_number
|
|
2
|
-
秒の切り捨てはfloor(時間/100)で。
|
|
2
|
+
~~秒の切り捨てはfloor(時間/100)で。~~
|
|
3
|
+
↑これだめやねちゃんとformatしましたm(_ _;)m
|
|
3
4
|
```sql
|
|
4
5
|
with test as (
|
|
5
6
|
select 1520905810 as 時間,'5.5' as 列A,'6.6' as 列B
|
|
@@ -10,9 +11,9 @@
|
|
|
10
11
|
)
|
|
11
12
|
select *
|
|
12
13
|
from (
|
|
13
|
-
select *
|
|
14
|
+
select *,FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm:ss') as 日時
|
|
14
15
|
-- 秒切り捨ての時間降順
|
|
15
|
-
,row_number() over(partition by
|
|
16
|
+
,row_number() over(partition by FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm') order by 時間 desc) as 順番
|
|
16
17
|
from test
|
|
17
18
|
) as X
|
|
18
19
|
where X.順番=1
|