這是我的表結構。（MariaDB 或 MySql）
| ID | 資料 |
|---|---|
| 1 | {“一”：“1”，“二”：“3”} |
| 2 | {“一”：“2??”，“二”：“4”} |
我想得到這樣的輸出
| ID | 一 | 二 |
|---|---|---|
| 1 | 好的 | 壞的 |
| 2 | 更多好 | 很壞 |
Values
1 = Good
2 = More Good
3 = Bad
4 = Very Bad
這是我在 MySQL 中的錯誤查詢
SET @ONE = 'Good';
SET @TWO = 'More Good';
SET @THREE = 'Bad';
SET @FOUR = 'Very Bad';
SELECT id,
CASE
WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 1 THEN @ONE
WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 2 THEN @TWO
WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 3 THEN @THREE
WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 4 THEN @FOUR
ELSE 'NO'
END as One,
CASE
WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 1 THEN @ONE
WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 2 THEN @TWO
WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 3 THEN @THREE
WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 4 THEN @FOUR
ELSE 'NO'
END as Two
From TableName;
uj5u.com熱心網友回復：
您可以使用字串函式，例如CONCAT_WS()創建所有字串變數的逗號分隔串列并SUBSTRING_INDEX()選擇正確的值：
SET @ONE = 'Good';
SET @TWO = 'More Good';
SET @THREE = 'Bad';
SET @FOUR = 'Very Bad';
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(
CONCAT_WS(',', @ONE, @TWO, @THREE, @FOUR),
',',
json_extract(data, '$.one')
), ',', -1) One,
SUBSTRING_INDEX(SUBSTRING_INDEX(
CONCAT_WS(',', @ONE, @TWO, @THREE, @FOUR),
',',
json_extract(data, '$.two')
), ',', -1) Two
FROM tablename;
請參閱演示。
uj5u.com熱心網友回復：
WITH
dictionary AS ( SELECT 1 id, 'Good' val UNION ALL
SELECT 2, 'More Good' UNION ALL
SELECT 3, 'Bad' UNION ALL
SELECT 4, 'Very Bad' )
SELECT test.id, dict_1.val One, dict_2.val Two
FROM test
JOIN dictionary dict_1 ON JSON_EXTRACT(test.data, '$.one') 0 = dict_1.id
JOIN dictionary dict_2 ON JSON_EXTRACT(test.data, '$.two') 0 = dict_2.id
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=4e7cee70ca62f4c19936bc2896d1791e
PS。將“我想得到這樣的輸出”保存到單獨的字典表中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/409074.html
標籤：
上一篇：SQL查詢-我不知道所有識別符號