比较mysql中的两个逗号分隔值并获取匹配的计数
Compare comma separated values in mysql and get the matched output in countcolumn
比较mysql中的逗号分隔值,并在countcolumn中获取匹配的输出
Example:
id values
1 1,2,3
2 3
3 1,3
By comparing id 1 and 2 the output should be 1
通过比较id 1和2,输出应为1
By comparing id 2 and 3 the output should be 1
通过比较id 2和3,输出应为1
By comparing id 3 and 1 the output should be 2
通过比较id 3和1,输出应为2
2 个解决方案
#1
3
Firstly, the data you listed should really be stored in a format where each row of the database stores a single id
and a single value
, i.e. the 3-element list would correspond to 3 rows. If that change was made to your structure then the following answer would be relevant Compare similarities between two result sets
首先,您列出的数据实际上应该以数据库的每一行存储单个ID和单个值的格式存储,即3元素列表对应于3行。如果对您的结构进行了更改,那么以下答案将是相关的。比较两个结果集之间的相似性
As it stands though, here's a nice little MySQL function I put together that you could use for your purposes. This takes in 2 arguments which are comma-separated lists and will return the number of elements in list 1 that appear in list 2.
尽管如此,这里有一个很好的小MySQL功能,我可以将它用于你的目的。这将引入2个以逗号分隔的列表参数,并返回列表1中出现在列表2中的元素数。
This will not as it stands prevent duplicate IDs in list 1 being counted twice in list 2 (i.e. '1,1,2' and '1,2' would return a value of 3) but you could figure out how to adjust this fairly easily to do that if you so wished.
这并不是因为它可以防止列表1中的重复ID在列表2中被计数两次(即'1,1,2'和'1,2'将返回值3)但是你可以弄清楚如何公平地调整它如果你愿意,很容易做到这一点。
To use this, just do
要使用它,就这样做
SELECT
countMatchingElements( '3' , '1,3' ) AS testCase1
countMatchingElements( '1,2,3' , '1,3' ) AS testCase2
countMatchingElements( '3' , '1,2,3' ) AS testCase3;
The stored function logic is as follows
存储的功能逻辑如下
CREATE DEFINER = `yourUserGoesHere`@`%` FUNCTION `countMatchingElements`(inFirstList VARCHAR(1000), inSecondList VARCHAR(1000))
RETURNS tinyint(3) unsigned
NO SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE numReturn TINYINT UNSIGNED DEFAULT 0;
DECLARE idsInFirstList TINYINT UNSIGNED;
DECLARE currentListItem VARCHAR(255) DEFAULT '';
DECLARE currentID TINYINT UNSIGNED;
SET idsInFirstList = ( CHAR_LENGTH( inFirstList ) + 1 ) - CHAR_LENGTH( REPLACE( inFirstList , ',' , '' ) );
SET currentID = 1;
-- Loop over inFirstList, and for each element that is in inSecondList increment numReturn
firstListLoop: REPEAT
SET currentListItem = SUBSTRING_INDEX( SUBSTRING_INDEX( inFirstList , ',' , currentID ) , ',' , -1 );
IF FIND_IN_SET( currentListItem , inSecondList ) THEN
SET numReturn = numReturn + 1;
END IF;
SET currentID = currentID + 1;
UNTIL currentID > idsInFirstList
END REPEAT firstListLoop;
RETURN numReturn;
END
更多相关文章
- Django查询优化:根据多对一到多对多查找对象列表
- Android 仿微信通讯录列表侧边栏
- Android之仿B612咔叽滤镜列表操作
- Android控件之Dialog(two)列表与自定义弹窗
- Android - Espresso -滚动到非列表视图项。
- weiyi通讯录(三)仿微信下拉列表和android样式
- 使用Java解析XML文件以获取名称列表
- Android-Dialog对话框 全解(普通对话框,单选对话框,多选对话框,列表
- 在android上滚动时,列表视图的位置会发生变化