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

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;


我正在使用 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 |
+———-+————+——-+


原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/271056.html

(0)
上一篇 2022年7月3日
下一篇 2022年7月3日

相关推荐

发表回复

登录后才能评论