mysql对比时间段百分比,MySQL字符串比较与百分比输出（位置非常重要

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.

Note that position matters. A match only occurs when both entries have a 1 in the first position, both have a 0 in the second position etc.

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);

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

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

解决方案

Have a look at this example.

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

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)