问题描述
考虑我有一个名为table_1的表，如下所示
id name products_id
1 hi 1,2,3,4,5,6
2 hello 2,3,5,6
3 wow 5,6,9,10,13
4 total 1,2,5,6,9,13
现在我想比较id = 1和id = 4，并在表中另做一列，该列仅具有products_id中两个行中都相似的值(即id = 1和id = 4)./p>
所以现在我该如何在MySQL中做到这一点，我希望针对所有行的上述过程发生，并且相似性应存储在第4列(即相似性列)中.
我希望决赛桌看起来像
id name products_id similarity
1 hi 1,2,3,4,5,6 1,2,5,6
2 hello 2,3,5,6 2,3,5,6
3 wow 5,6,9,10,13 5,6,9,13
4 total 1,2,5,6,9,13 1,2,5,6,9,13
我已经做了类似的事情，用逗号分隔的ID列表来模拟位掩码.技巧是使用'％，id，％(其中id是由id索引的表中的键)进行联接.对于您的情况，我将table_1连接到一个有序列整数的表，结果包含product_id列中每个id的一行.
对于您请求的每个ID，我都会执行一次，然后将生成的派生表连接起来以查找常见的表.
这是查询:
SELECT p1.id, p1.name name1,p2.name name2, GROUP_CONCAT(p1.N) similarities
FROM (
SELECT *
FROM table_1 t1
JOIN sequence8 s8 on CONCAT(',',t1.product_ids,',') LIKE CONCAT('%,',s8.N,',%')
WHERE t1.id =1
) p1
JOIN
(
SELECT *
FROM table_1 t1
JOIN sequence8 s8 ON CONCAT(',',t1.product_ids,',') LIKE CONCAT('%,',s8.N,',%')
WHERE t1.id =4
) p2 ON p2.n = p1.n
GROUP BY p1.id
consider I have a table named table_1 as shown below
id name products_id
1 hi 1,2,3,4,5,6
2 hello 2,3,5,6
3 wow 5,6,9,10,13
4 total 1,2,5,6,9,13
now i want to compare id=1 and id=4 and make another column in the table which is having only the values in products_id that are similar in both the rows(that is id=1 and id=4).
so now how do i do it in MySQL and i want it the above process to happen for all the rows and the similarities should be stored in column 4 (that is the similarities column).
I want the final table to look like
id name products_id similarity
1 hi 1,2,3,4,5,6 1,2,5,6
2 hello 2,3,5,6 2,3,5,6
3 wow 5,6,9,10,13 5,6,9,13
4 total 1,2,5,6,9,13 1,2,5,6,9,13
I have done something similar treating comma separated lists of IDs to emulate bit masks. Trick is to do a join using like '%,id,% (where id is the key in a table indexed by id). For your case I join table_1 to a table of sequenced integers and the result includes a row for every id in your product_id column.
I do this once for each id you are requesting and join the resulting derived tables to find the common ones.
Here is the query:
SELECT p1.id, p1.name name1,p2.name name2, GROUP_CONCAT(p1.N) similarities
FROM (
SELECT *
FROM table_1 t1
JOIN sequence8 s8 on CONCAT(',',t1.product_ids,',') LIKE CONCAT('%,',s8.N,',%')
WHERE t1.id =1
) p1
JOIN
(
SELECT *
FROM table_1 t1
JOIN sequence8 s8 ON CONCAT(',',t1.product_ids,',') LIKE CONCAT('%,',s8.N,',%')
WHERE t1.id =4
) p2 ON p2.n = p1.n
GROUP BY p1.id
这篇关于我如何比较两行并将两行的相似性存储在另一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！