我遇到了一個問題，我有一個帶有 JSON 列的表，如下所示：
ID|VALUE
1 |{"a":"text1234","b":"default"}
2 |{"a":"text1234","b":"default"}
3 |{"a":"text1234","b":"text234"}
4 |{"a":"text1234","b":"default2"}
5 |{"a":"text1234","b":"default2"}
我想獲取值“b”重復的所有行，因此使用上面的表格我會得到第 1、2、4、5 行。
我試圖按 value->b 對行進行分組
$value_ids = ProductsAttributesValues::groupBy("value->b")->get();
但是當我 dd($value_ids) 行沒有按 value->default 分組時。而且我找不到將它們分組的方法，因此我可以計算它們。或者會有更好的方法來做到這一點？
uj5u.com熱心網友回復：
試試這個json_extract功能：
select count(id) dup_count, json_extract(`value`,"$.b") as dup_value
from test
group by json_extract(`value`,"$.b")
having dup_count>1
;
-- result set:
| dup_count | dup_value |
----------- ------------
| 2 | "default" |
| 2 | "default2" |
-- to get the id involved:
select id,dup_count,dup_value
from (select id,json_extract(`value`,"$.b") as dup_v
from test) t1
join
(select count(id) dup_count, json_extract(`value`,"$.b") as dup_value
from test
group by json_extract(`value`,"$.b")
having dup_count>1) t2
on t1.dup_v=t2.dup_value
;
-- result set:
| id | dup_count | dup_value |
------ ----------- ------------
| 1 | 2 | "default" |
| 2 | 2 | "default" |
| 4 | 2 | "default2" |
| 5 | 2 | "default2" |
uj5u.com熱心網友回復：
這是可以完成您的任務的查詢。
/*Extract value of "b" - Step 1*/
DROP TEMPORARY TABLE IF EXISTS d1;
CREATE TEMPORARY TABLE d1
SELECT
ID, `VALUE`, SUBSTR(VALUE FROM POSITION(',"b":' IN VALUE) 5 FOR 1000) AS v
FROM mytest
;
/*Extract value of "b" - Step 2*/
DROP TEMPORARY TABLE IF EXISTS d2;
CREATE TEMPORARY TABLE d2
SELECT
ID, LEFT(v, LENGTH(v)-1) AS b
FROM
d1
;
ALTER TABLE d2 ADD INDEX b(b);
/* Search for duplicates */
DROP TEMPORARY TABLE IF EXISTS duplicates;
CREATE TEMPORARY TABLE duplicates
SELECT
b, COUNT(b) AS b_count
FROM
d2
GROUP BY b HAVING COUNT(b)>1
;
ALTER TABLE duplicates ADD INDEX b(b);
/* Display for duplicates */
SELECT
d2.ID, d2.b
FROM
d2
INNER JOIN duplicates ON d2.b=duplicates.b
;
這應該給你：
1 "default"
2 "default"
4 "default2"
5 "default2"
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/526537.html
下一篇：限制和偏移MYSQL的排序問題