连续显示多条记录 [英] Show multiple records in a row

问题描述

假设我创建了 2 个表.一个是学生，另一个是事件.表结构和记录就像它们一样.我想在以逗号分隔的一行中显示事件结果.

Suppose I've created 2 tables. one is students and another is events. The tables structures and the records are like them. I would like to show the events result in one row separated by commas.

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `event_name` varchar(100) NOT NULL,
  `event_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `event_name`, `event_id`) VALUES
(1, 'dance', 1),
(2, 'sing', 2),
(3, 'acting', 3),
(4, 'debate', 4);

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(100) NOT NULL,
  `student_name` text NOT NULL,
  `event_id` int(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `student_name`, `event_id`) VALUES
(1, 'student 1', 1),
(2, 'student1', 2),
(3, 'student1', 3),
(4, 'student1', 4),
(5, 'student2', 3);
(6, 'student3', 2);
(7, 'student3', 4);

我想看到这样的结果

Students  |  Events
----------|---------
Student1  | dance, sing, acting, debate
Student2  | acting
Student3  | sing, debate

什么是 SQL 查询?

What would be the SQL query?

谢谢

推荐答案

只需在两个表之间进行组连接和连接即可:

Just do a group concatenation along with a join between the two tables:

SELECT
    s.student_name AS Students,
    GROUP_CONCAT(e.event_name) AS Events
FROM students s
LEFT JOIN events e
    ON s.event_id = e.event_id
GROUP BY
    s.student_name;

演示

请注意，您的表架构似乎没有完全规范化.通常，处理这个问题的方法是有一个学生信息表，一个事件表，然后是一个连接学生和事件的连接表.您的 students 表似乎既用作连接表又用作包含唯一学生信息的表.

Note that your table schema appears to not be completely normalized. Typically, the way to handle this problem is to have a single table for student information, a single table for events, and then a junction table which connects students to events. Your students table appears to be serving both as a junction table and as a table containing unique student information.

因此，在这里进行的更好方法是重构 students 以包含此内容:

So a better way to proceed here would be to refactor students to contain this:

(1, 'student1'),
(2, 'student2'),
(3, 'student3');

并创建一个新的连接表student_event来包含学生和事件之间的关系:

And create a new junction table student_event to contain the relationship between students and events:

(id, student_id, event_id)
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 2, 3),
(6, 3, 2),
(7, 3, 4);

这篇关于连续显示多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！