比较字段和获得百分比

Hi and thank you again for looking into this question.

I have one mysql table for visitors. They can add their favorite singers to a list. So the table looks very simple:

user_artist_favorites
id
user_id
artistName

Let's say Moonwalker got 4 favorite singers:

 1. Michael Jackson
 2. Elvis Presley
 3. Ray Charles
 4. Stevie Wonder

The user Tester got also 4 favorite singers:

 1. Michael Jackson
 2. Ray Charles
 3. Stevie Wonder
 4. Lady Gaga (sorry folks)

What I would like to do now is compare the two users and output something like: Moonwalker & Tester have 75% the same taste in music.

How to start? Can anyone point me to the right direction please?

Thanks in advance for any help you can provide.

Select T1.user_id
    , Count( T2.user_id ) / Count( T1.user_id ) * 100.0 As PercentMatch
From user_artist_favorites As T1
    Left Join user_artist_favorites As T2
        On T2.artistname = T1.artistname
            And T2.user_id = 'Tester'
Where T1.user_id = 'Moonwalker'
Group By T1.user_id
Union All
Select T1.user_id
    , Count( T2.user_id ) / Count( T1.user_id ) * 100.0
From user_artist_favorites As T1
    Left Join user_artist_favorites As T2
        On T2.artistname = T1.artistname
            And T2.user_id = 'Moonwalker'
Where T1.user_id = 'Tester'
Group By T1.user_id

The first part of the Union All query returns the percent that Tester is like Moonwalker. The second query returns the percent that Moonwalker is like Tester.

What you can do is read the lists sorted by artist name for the two users and go through a loop to count the number of singers that match. The loop will be a bit complicated as you need to check which list is alphabetically greater at the index and increase the pointer on the one that is lower.