编辑代码

CREATE DATABASE test;
USE test;

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    col1 INT,
    col2 INT,
    col3 INT,
    col4 INT
);

INSERT INTO my_table (id, col1, col2, col3, col4)
VALUES
    (1, 5, 12, 8, 15),
    (2, 3, 7, 11, 9),
    (3, 14, 6, 2, 18);
SELECT 
    id, col1, col2, col3, col4,
    ((col1+col2+col3+col4) / 4) AS average,
    (
        CASE WHEN col1 < ((col1+col2+col3+col4) / 4) THEN 1 ELSE 0 END +
        CASE WHEN col2 < ((col1+col2+col3+col4) / 4) THEN 1 ELSE 0 END +
        CASE WHEN col3 < ((col1+col2+col3+col4) / 4) THEN 1 ELSE 0 END +
        CASE WHEN col4 < ((col1+col2+col3+col4) / 4) THEN 1 ELSE 0 END
    ) AS count_less_than_avg
FROM my_table;