MySQL - 获取列中的平均值

I'm making a list with certain songs. Each song has its own unique ID. OK let's say I have this table called list. A new:

The ID is self-explanatory. It's used to ID rows. song_one through song_ten is filled with a song's unique ID.

Every time a user makes a new list, they add songs and each row gets filled. Now, how would I get the average rank of the songs in the tables list using the song's ID that is filled between song_one through song_ten?

Redesign your database. Make a new table with songid, listid and rank. This will make your job easy.

listsongs
-----------------
songid [PK] -- Foreign key referencing the songs table
listid [PK] -- Foreign key referencing the lists table
rank

Selecting average song ranks:

SELECT
    a.song_id
    AVG(b.rank) AS avgrank
FROM
    songs a
LEFT JOIN
    listsongs b PM a.song_id = b.song_id
GROUP BY
    a.song_id

Please do as others have said about normalizing your DB structure. If you wish to continue with this design however, you can use this clunky-looking solution:

SELECT 
    a.song_id,
    AVG(
        CASE
            WHEN a.song_id = b.song_one THEN 1
            WHEN a.song_id = b.song_two THEN 2
            WHEN a.song_id = b.song_three THEN 3
            WHEN a.song_id = b.song_four THEN 4
            WHEN a.song_id = b.song_five THEN 5
            WHEN a.song_id = b.song_six THEN 6
            WHEN a.song_id = b.song_seven THEN 7
            WHEN a.song_id = b.song_eight THEN 8
            WHEN a.song_id = b.song_nine THEN 9
            WHEN a.song_id = b.song_ten THEN 10
        END
    ) AS AvgRank
FROM
    songs a
INNER JOIN
    list b ON 
        a.song_id IN 
        (
            b.song_one,
            b.song_two,
            b.song_three,
            b.song_four,
            b.song_five,
            b.song_six,
            b.song_seven,
            b.song_eight,
            b.song_nine,
            b.song_ten
        )
GROUP BY
    a.song_id

I would listen to @Mark Byers and @Shehzad Bilal, who said that you need to redesign your database structure.

When you think in the terms of tables and their attributes, think logical - think in the terms of code.

For example: If you are writing to a file, would it be easier to create a universal loop to output all the things needed, or to open the file with different pieces of code every time you needed to write something.

In your database, it would be easier to have one table that represents the song itself (that is the general idea behind a database design) than having a table that represents all the songs.

(table) (attribute)
 song
         id
         albumid (fk from table album)
         name
         title
         (...etc)
 list
         id
         songid (fk from table song)
         ip
         date
         (...etc)

If you wanted to create a ranking system, you would do it through code. In some cases, purely depending on your design, you would have a table for it, but it would also be universal.