gpt4 book ai didi

MySQL 选择 json 格式的两个时间值之间的行

转载 作者:行者123 更新时间:2023-11-29 22:30:30 24 4
gpt4 key购买 nike

我有一个 MySQL 日志表,其中有一列包含 json 字符串。 json字符串包含年、月、日、时、分等,并自 Action 为日志插入。

示例表格式为,

+-------+-------------------------------------------------------------------------------+
|logId | log |
+-------+-------------------------------------------------------------------------------+
| 1 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "01", "Minute": "00" } |
| 2 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "01", "Minute": "30" } |
| 3 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "02", "Minute": "00" } |
| 4 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "02", "Minute": "30" } |
| 5 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "03", "Minute": "00" } |
| 6 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "03", "Minute": "30" } |
| 7 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "04", "Minute": "00" } |
| 8 | { "Year": "2015", "Month": "04", Date": "24", "Hour": "04", "Minute": "30" } |
| . | . |
| . | . |
| . | . |
| . | . |
| . | { "Year": "2015", "Month": "04", Date": "24", "Hour": "23", "Minute": "00" } |
| . | { "Year": "2015", "Month": "04", Date": "24", "Hour": "23", "Minute": "30" } |
| . | { "Year": "2015", "Month": "04", Date": "25", "Hour": "00", "Minute": "00" } |
| . | { "Year": "2015", "Month": "04", Date": "25", "Hour": "00", "Minute": "30" } |
+-------+-------------------------------------------------------------------------------+

现在我如何选择每天和特定日期的 02 小时 30 分钟23 小时 00 分钟 之间的行?

最佳答案

你可以试试这个:

select * from tablename where log 
not exists(select 1 from tablename where
log like '%"Hour": "01", "Minute": "00"%'
or log like '%"Hour": "01", "Minute": "30"%'
or log like '%"Hour": "02", "Minute": "00"%'
or log like '%"Hour": "23", "Minute": "30"%'
or log like '%"Hour": "00", "Minute": "00"%'
or log like '%"Hour": "00", "Minute": "30"%' )

关于MySQL 选择 json 格式的两个时间值之间的行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29843172/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com