草庐IT

MySQL LEFT JOIN json 字段与表中的另一个 id

codeneng 2023-03-28 原文

MySQL LEFT JOIN json field with another id from table

我有两张桌子：

1
2
3
4
5
6
7
8
9
10
11
Bouquets

+----+------------+
| id | bouquet    |
+----+------------+
|  1 | Package #1 |
|  2 | Package #2 |
|  3 | Package #3 |
|  4 | Package #4 |
|  5 | Package #5 |
+----+------------+

和

1
2
3
4
5
6
Prices
+----+----------+-------------------------------------------------------------------+
| id | reseller | price                                                    
+----+----------+-------------------------------------------------------------------+
|  1 |        1 | {"1":"1.11","2":"0.00","3":"0.00","4":"4.44","5":"5.55"} |
+----+----------+-------------------------------------------------------------------+

我需要获取价格值不是"0.00"的花束名称...所以我尝试 LEFT JOIN 以在 price.price 上加入花束.id 但我不知道怎么做？

我需要得到这个：

1
2
3
4
5
6
7
+----+------------+
| id | bouquet    |
+----+------------+
|  1 | Package #1 |
|  4 | Package #4 |
|  5 | Package #5 |
+----+------------+

这是我的尝试，但我得到的是空结果：

1
2
3
SELECT b.id, b.bouquet FROM bouquets b
LEFT JOIN prices p ON JSON_CONTAINS(p.price, CAST(b.id as JSON), '$') !="0.00"
WHERE p.reseller=1;

  • JSON_CONTAINS 不会遍历所有键来寻找"0.0"的值。您可以执行类似测试 JSON_CONTAINS(price,\\'{"2" : "0.00
  • 好的，您能否发布 LEFT JOIN b.id ON p.price id 的示例代码？例如 b.id = 1 ON p.price = "1" 并读取 "1" = "1.11" 的值，这样我就可以做 > "0.00"

我正在使用 mysql package器在节点 js 中编程，这是我使用的解决方案，它正在工作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
/* QUERY - aaBouquets */
            connection.query("SELECT id, bouquet FROM bouquets ORDER BY bouquet ASC",function(err, rows, fields){
                /* BOUQUETS - number */
                var total = rows.length;

                /* FOUND - bouquets */
                if (rows.length) {

                    /* GET - prices */
                    for (var i in rows) {
                        var s = 1;
                        connection.query("SELECT '"+rows[i].id+"' AS id, '"+rows[i].bouquet+"' AS bouquet FROM prices p LEFT JOIN bouquets b ON JSON_SEARCH(p.price, 'one', '$.""+rows[i].id+""') WHERE p.reseller=? AND FORMAT(JSON_EXTRACT(price, '$.""+rows[i].id+""'), 2) != "0.00"",[qreseller], function(err, rows, results){

                            /* CHECK - prices */
                            if (s < total) {
                                if (rows.length) {
                                    /* GET - prices */
                                    data.push(rows[0]);
                                };
                                s++;
                            } else {
                                /* CHECK - prices */
                                if(data.length) {
                                    if (rows.length) {
                                        /* GET - prices */
                                        data.push(rows[0]);
                                    };

                                    /* RETURN - servers data */
                                    res.json(data);
                                };
                            }
                        });
                    }
                }
            });

您可以看到第一个查询正在获取 id 和花束名称，然后在 for 循环中我使用该 id 获取该花束 id 的值，并且仅在值不等于 "0.00" 时才显示..使用变量 s 和总计在这里使用是因为如果我调用 console.log(data) 我得到未定义的变量..因为在节点中 js 变量是本地的并且需要在 for 循环内部调用如果在外部调用我得到未定义的变量错误。

这种方式我只得到具有定义价格的花束......我不知道它是否可以在单个查询中完成(因为你不能在 p.prices 上使用 LEFT JOIN ON b.id)所以需要这两个查询...对我来说一切正常...所以如果有人可以最小化代码以提高速度或改进...欢迎。

这似乎并不容易在 mysql 中完成，最好的办法是使用(PHP、ASP 等)来完成繁重的工作，但经过大量试验和错误后，我发现了这篇文章：

将 MySQL 中的 JSON 数组转换为行

从那里这个查询似乎对我有用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
    b.id,
    b.bouquet
FROM bouquet AS b
JOIN (
    SELECT
        indx.id,
        indx.idx,
        JSON_EXTRACT(p.price, idx) AS bouquetprice
    FROM prices AS p
    JOIN (
        SELECT  '$."1"' AS idx, 1 AS id UNION
        SELECT  '$."2"' AS idx, 2 AS id UNION
        SELECT  '$."3"' AS idx, 3 AS id UNION
        SELECT  '$."4"' AS idx, 4 AS id UNION
        SELECT  '$."5"' AS idx, 5 AS id
    ) AS indx
    WHERE JSON_EXTRACT(p.price, idx) IS NOT NULL
    AND p.reseller = 1
) AS ind
ON b.id = ind.id
AND ind.bouquetprice !="0.00"

诀窍似乎是链接的 SO 帖子中的 CONCAT 不适用于您的 json 中的数字键名称。所以你必须求助于临时连接中的 2 个索引来搜索。

此外，临时连接表在创建不断增长的索引列表方面并不理想，但它至少是一个开始的地方。 (抱歉所有错误的命名 idx、indx 等)

编辑：忘记了 reseller 部分

  • 谢谢...我得到了类似的解决方案..请检查上面...这是很好的语法代码和想法我喜欢它...谢谢

称我为老式，但我真的不喜欢存储 json 数据。无论如何，一个规范化的表可能看起来像这样......

1
2
3
4
5
6
7
8
Prices
+----------+------------+-------+
| reseller | bouquet_id | price |
+----------+------------+-------+
|        1 |          1 |  1.11 |
|        1 |          4 |  4.44 |
|        1 |          5 |  5.55 |
+----------+------------+-------+

  • 我知道..但是如果我假设每个经销商有 100 个经销商和 20bouquet_id，那么我将在 mysql 表中拥有 100x20=2000 条记录......所以我会留在 json 它更容易，但我不知道如何离开加入...感谢您的想法

MySQLLEFTspannbspclassjsonmysql

有关MySQL LEFT JOIN json 字段与表中的另一个 id的更多相关文章

  1. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

    总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

  2. ruby - 其他文件中的 Rake 任务 - 2

    我试图在一个项目中使用rake，如果我把所有东西都放到Rakefile中，它会很大并且很难读取/找到东西，所以我试着将每个命名空间放在lib/rake中它自己的文件中，我添加了这个到我的rake文件的顶部:Dir['#{File.dirname(__FILE__)}/lib/rake/*.rake'].map{|f|requiref}它加载文件没问题，但没有任务。我现在只有一个.rake文件作为测试，名为“servers.rake”，它看起来像这样:namespace:serverdotask:testdoputs"test"endend所以当我运行rakeserver:testid时

  3. ruby-on-rails - Ruby net/ldap 模块中的内存泄漏 - 2

    作为我的Rails应用程序的一部分，我编写了一个小导入程序，它从我们的LDAP系统中吸取数据并将其塞入一个用户表中。不幸的是，与LDAP相关的代码在遍历我们的32K用户时泄漏了大量内存，我一直无法弄清楚如何解决这个问题。这个问题似乎在某种程度上与LDAP库有关，因为当我删除对LDAP内容的调用时，内存使用情况会很好地稳定下来。此外，不断增加的对象是Net::BER::BerIdentifiedString和Net::BER::BerIdentifiedArray，它们都是LDAP库的一部分。当我运行导入时，内存使用量最终达到超过1GB的峰值。如果问题存在，我需要找到一些方法来更正我的代

  4. ruby-on-rails - Rails 3 中的多个路由文件 - 2

    Rails2.3可以选择随时使用RouteSet#add_configuration_file添加更多路由。是否可以在Rails3项目中做同样的事情？ 最佳答案 在config/application.rb中:config.paths.config.routes在Rails3.2(也可能是Rails3.1)中，使用:config.paths["config/routes"] 关于ruby-on-rails-Rails3中的多个路由文件，我们在StackOverflow上找到一个类似的问题

  5. ruby - 使用 Vim Rails，您可以创建一个新的迁移文件并一次性打开它吗？ - 2

    使用带有Rails插件的vim，您可以创建一个迁移文件，然后一次性打开该文件吗？textmate也可以这样吗？ 最佳答案 你可以使用rails.vim然后做类似的事情::Rgeneratemigratonadd_foo_to_bar插件将打开迁移生成的文件，这正是您想要的。我不能代表textmate。 关于ruby-使用VimRails，您可以创建一个新的迁移文件并一次性打开它吗？，我们在StackOverflow上找到一个类似的问题： https://sta

  6. ruby-on-rails - Rails - 一个 View 中的多个模型 - 2

    我需要从一个View访问多个模型。以前，我的links_controller仅用于提供以不同方式排序的链接资源。现在我想包括一个部分(我假设)显示按分数排序的顶级用户(@users=User.all.sort_by(&:score))我知道我可以将此代码插入每个链接操作并从View访问它，但这似乎不是“ruby方式”，我将需要在不久的将来访问更多模型。这可能会变得很脏，是否有针对这种情况的任何技术？注意事项:我认为我的应用程序正朝着单一格式和动态页面内容的方向发展，本质上是一个典型的网络应用程序。我知道before_filter但考虑到我希望应用程序进入的方向，这似乎很麻烦。最终从任何

  7. ruby-on-rails - 渲染另一个 Controller 的 View - 2

    我想要做的是有2个不同的Controller，client和test_client。客户端Controller已经构建，我想创建一个test_clientController，我可以使用它来玩弄客户端的UI并根据需要进行调整。我主要是想绕过我在客户端中内置的验证及其对加载数据的管理Controller的依赖。所以我希望test_clientController加载示例数据集，然后呈现客户端Controller的索引View，以便我可以调整客户端UI。就是这样。我在test_clients索引方法中试过这个:classTestClientdefindexrender:template=>

  8. ruby-on-rails - Rails 3.2.1 中 ActionMailer 中的未定义方法 'default_content_type=' - 2

    我在我的项目中添加了一个系统来重置用户密码并通过电子邮件将密码发送给他，以防他忘记密码。昨天它运行良好(当我实现它时)。当我今天尝试启动服务器时，出现以下错误。=>BootingWEBrick=>Rails3.2.1applicationstartingindevelopmentonhttp://0.0.0.0:3000=>Callwith-dtodetach=>Ctrl-CtoshutdownserverExiting/Users/vinayshenoy/.rvm/gems/ruby-1.9.3-p0/gems/actionmailer-3.2.1/lib/action_mailer

  9. ruby-on-rails - Rails 应用程序中的 Rails : How are you using application_controller. rb 是新手吗？ - 2

    刚入门rails，开始慢慢理解。有人可以解释或给我一些关于在application_controller中编码的好处或时间和原因的想法吗？有哪些用例。您如何为Rails应用程序使用应用程序Controller？我不想在那里放太多代码，因为据我了解，每个请求都会调用此Controller。这是真的？ 最佳答案 ApplicationController实际上是您应用程序中的每个其他Controller都将从中继承的类(尽管这不是强制性的)。我同意不要用太多代码弄乱它并保持干净整洁的态度，尽管在某些情况下ApplicationContr

  10. ruby-on-rails - 如何验证非模型(甚至非对象)字段 - 2

    我有一个表单，其中有很多字段取自数组(而不是模型或对象)。我如何验证这些字段的存在？solve_problem_pathdo|f|%>... 最佳答案 创建一个简单的类来包装请求参数并使用ActiveModel::Validations。#definedsomewhere,atthesimplest:require'ostruct'classSolvetrue#youcouldevencheckthesolutionwithavalidatorvalidatedoerrors.add(:base,"WRONG!!!")unlesss

随机推荐