MySQL字符串与百分比输出的比较(位置非常重要 [英] MySQL String Comparison with Percent Output (Position Very Important

问题描述

我正在尝试比较两个包含6个数字的条目，每个数字可以为零或1(即100001或011101).如果6中有3个匹配，我希望输出为0.5.如果6个匹配项中有2个匹配，我希望输出为.33等.

I am trying to compare two entries of 6 numbers, each number which can either can be zero or 1 (i.e 100001 or 011101). If 3 out of 6 match, I want the output to be .5. If 2 out of 6 match, i want the output to be .33 etc.

请注意，职位很重要.仅当两个条目的第一个位置都为1，第二个位置都为0时，才会发生匹配.

以下是创建表的SQL命令

Here are the SQL commands to create the table

CREATE TABLE sim
(sim_key int,
 string int);

INSERT INTO sim (sim_key, string)
VALUES (1, 111000);

INSERT INTO sim (sim_key, string)
VALUES (2, 101101);

我希望比较两个共享50％字符的字符串并输出50％的字符串.

My desired output to compare the two strings, which share 50% of the characters, and output 50%.

是否可以在SQL中进行这种比较?预先感谢

Is it possible to do this sort of comparison in SQL? Thanks in advance

推荐答案

看看这个例子.

CREATE TABLE sim     (sim_key int,      string int);
INSERT INTO sim (sim_key, string)     VALUES (1, 111000);
INSERT INTO sim (sim_key, string)     VALUES (2, 101101);

select a.string A, b.string B,
   sum(case when Substring(A.string,Pos,1) = Substring(B.string,Pos,1) then 1 else 0 end) Matches,
   count(*) as RowCount,
   (sum(case when Substring(A.string,Pos,1) = Substring(B.string,Pos,1) then 1 else 0 end) /
   count(*) * 100.0) as PercentMatch
from sim A
cross join sim B
inner join (
    select 1 Pos union all select 2 union all select 3
    union all select 4 union all select 5 union all select 6) P
        on P.Pos between 1 and length(A.string)
where A.sim_key= 1 and B.sim_key = 2
group by a.string, b.string

它很粗糙，可能包含的内容超出了要求，但显示了如何完成.最好创建一个仅包含1到1000左右数字的numbers表，该表可以在需要数字序列的许多查询中重复使用.这样的表将替换(内部联接中使用的选择.. union虚拟表)

It is crude and probably included more than required but shows how it can be done. It is better to create a numbers table with just numbers from 1 to 1000 or so, that can be used repeatedly in many queries where a number sequence is required. Such a table will replace the (select .. union virtual table used in the inner join)

这篇关于MySQL字符串与百分比输出的比较(位置非常重要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！