主页 / 问题 / 1592618
Accepted
KrT
Asked:2024-09-02 17:40:43 +0800 CST2024-09-02 17:40:43 +0800 CST

您应该使用哪个 SQL 命令来查找包含大于特定数字的值的行？

  • 772

如何从数据库中获取其中stat "5": "1.5" 和 stat "1": 大于查询得到的数的所有数据。例如，我从查询中获取数字 100，并且需要获取统计数据“1”：大于 100。

|id |prom       |stat                                               |user|date      |
|-----------------------------------------------------------------------------------|
|1  |77-4390    |{"1":"824","2":"30","3":"9026","4":"40","5":"1.5"} |11  |10.08.2024|
|-----------------------------------------------------------------------------------|
|2  |77-4391    |{"1":"904","2":"51","3":"9222","4":"40","5":"1.5"} |23  |10.08.2024|
|-----------------------------------------------------------------------------------|
|3  |77-4355    |{"1":"334","2":"30","3":"9026","4":"40","5":"2.5"} |45  |10.08.2024|
|-----------------------------------------------------------------------------------|
|4  |77-4391    |{"1":"824","2":"60","3":"9023","4":"40","5":"1.5"} |11  |10.08.2024|
|-----------------------------------------------------------------------------------|
|5  |77-4391    |{"1":"624","2":"50","3":"9046","4":"40","5":"3.5"} |19  |10.08.2024|
|-----------------------------------------------------------------------------------|
|6  |77-4391    |{"1":"24","2":"50","3":"9122","4":"40","5":"1.5"}  |15  |10.08.2024|
+-----------------------------------------------------------------------------------+ 

для вывода "5": "1.5" использую:
SELECT * from cst WHERE prom='77-4391' AND  stat LIKE '%5":"1.5%'

+-----------------------------------------------------------------------------------+
|id |prom       |stat                                               |user|date      |
|-----------------------------------------------------------------------------------|
|2  |77-4391    |{"1":"904","2":"51","3":"9222","4":"40","5":"1.5"} |23  |10.08.2024|
|-----------------------------------------------------------------------------------|
|4  |77-4391    |{"1":"824","2":"60","3":"9023","4":"40","5":"1.5"} |11  |10.08.2024|
|-----------------------------------------------------------------------------------|
|6  |77-4391    |{"1":"24","2":"50","3":"9122","4":"40","5":"1.5"}  |15  |10.08.2024|
+-----------------------------------------------------------------------------------+ 

Каким запросом получить данные где stat "1":> 100
+-----------------------------------------------------------------------------------+
|id |prom       |stat                                               |user|date      |
|-----------------------------------------------------------------------------------|
|2  |77-4391    |{"1":"904","2":"51","3":"9222","4":"40","5":"1.5"} |23  |10.08.2024|
|-----------------------------------------------------------------------------------|
|4  |77-4391    |{"1":"824","2":"60","3":"9023","4":"40","5":"1.5"} |11  |10.08.2024|
|-----------------------------------------------------------------------------------|
mysql

2 个回答

  1. Best Answer

    它应该是这样的，但我不确定它是否可以使用数字作为字段名称：

    SELECT * from cst 
WHERE prom='77-4391' 
  AND stat->"$.5" = '1.5%'
  AND stat->"$.1" > 100

    在比较之前，该字段可能还必须INT“手动”转换。

    好吧，我不会确切地说出有哪些引用，我不经常使用不同的数据库。

    • 0

  2. 您应该检查您正在使用哪个 SQL 服务器。

    如果您使用 Postgresql，则该字段中的数据stat是一个 hstore，查询将如下所示。

    SELECT your_fields 
FROM cst
WHERE stats -> '1' > 100
    • 0

相关问题