-- GROUP_CONCAT与CONCAT的比较与应用
-- 个人理解:GROUP_CONCAT多条数据纵向同一字段列的连接。
-- CONCAT 一条数据横向多个字段值的连接
-- 例如:查询数理化三门课程的最低分、以及最低分是谁的sql
SELECT
GROUP_CONCAT(stuName ORDER BY score ASC),
min(score) AS score,
courseName
FROM
table_yours
GROUP BY
courseName;
-- GROUP_CONCAT中使用 case when
SELECT
GROUP_CONCAT(
CASE
WHEN a.flg = 2 THEN
CONCAT(b. NAME, '(已停用)')
ELSE
CONCAT(b. NAME, '')
END
) AS forwardTo
FROM
t_tableA a
LEFT JOIN t_tableB b ON a.f_to_id = b.id
WHERE
a. STATUS = 0
-- 并列最低分,是这样吗?转的
SELECT
stuName,
score,
courseName
FROM
table_yours
WHERE
(score, courseName) IN (
SELECT
min(score),
courseName
FROM
table_yours
GROUP BY
courseName
);