mysql - 将重复 ID 的不同值连接成一行

Question

我正在开发图书馆管理应用程序。问题是，我有下表:用户、员工、贷款、book_loan，我需要一个查询来返回一个唯一的外借ID，包含用户名、员工姓名、图书名称、外借日期、授权日期和状态。我几乎做到了这一点，但它向我返回了贷款 ID 的副本，书名由行分隔。

这些是我的表格:

delimiter $$

CREATE TABLE `Loan` (
  `id_loan` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_employee` int(11) NOT NULL,
  `date_loan` date NOT NULL,
  `data_devolution` date NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_loan`),
  KEY `fk_loan_id_user_idx` (`id_user`),
  KEY `fk_loan_id_employee` (`id_employee`),
  CONSTRAINT `fk_loan_id_employee` FOREIGN KEY (`id_emplyee`) REFERENCES `Employee` (`id_employee`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_loan_id_user` FOREIGN KEY (`id_user`) REFERENCES `User` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8$$


delimiter $$

CREATE TABLE `Employee` (
  `id_employee` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(20) NOT NULL,
  `password` varchar(16) NOT NULL,
  `level` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id_employee`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8$$


delimiter $$

CREATE TABLE `Book` (
  `id_book` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `id_author` int(11) NOT NULL,
  `edition` decimal(10,0) NOT NULL,
  `editor` varchar(45) NOT NULL,
  `cutter` varchar(12) NOT NULL,
  `id_CDU` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_book`),
  KEY `fk_book_id_CDU_idx` (`id_CDU`),
  KEY `fk_book_id_author` (`id_author`),
  CONSTRAINT `fk_book_id_author` FOREIGN KEY (`id_author`) REFERENCES `Author` (`id_author`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_book_id_CDU` FOREIGN KEY (`id_CDU`) REFERENCES `CDU` (`id_CDU`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8$$


delimiter $$

CREATE TABLE `Book_Loan` (
  `id_book` int(11) NOT NULL,
  `id_loan` int(11) NOT NULL,
  KEY `fk_book_loan_id_loan` (`id_loan`),
  KEY `fk_book_loan_id_book` (`id_book`),
  CONSTRAINT `fk_book_loan_id_loan` FOREIGN KEY (`id_loan`) REFERENCES `Loan` (`id_loan`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_book_loan_id_book` FOREIGN KEY (`id_book`) REFERENCES `Book` (`id_book`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


delimiter $$

CREATE TABLE `User` (
  `id_user` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$

这是我的查询:

SELECT loan.id_loan AS ID,
        employee.login AS EMPLOYEE,
        user.name AS USER,
        book.title AS BOOK,
        loan.date_loan AS LOAN,
        loan.date_devolution AS DEVOLUTION,
        loan.status AS STATUS

FROM Loan loan
INNER JOIN Employee employee ON employee.id_employee = loan.id_employee
INNER JOIN User user ON user.id_user = loan.id_user
INNER JOIN Book_Loan book_loan ON book_loan.id_loan = loan.id_loan
INNER JOIN Book book ON book.id_book = book_loan.id_book;

这会返回我，例如:如果贷款有 3 本书，这是最大值:

+-----+-------------+-------------+---------------------------------+------------+------------+--------+
| ID  | EMPLOYEE    | USER        | BOOK                            | LOAN       | DEVOLUTION| STATUS |
+-----+-------------+-------------+---------------------------------+------------+------------+--------+
|   6 | RUBE        | John        | Book A                          | 2013-06-22 | 2013-06-29 |      0 |
|   6 | RUBE        | John        | Book B                          | 2013-06-22 | 2013-06-29 |      0 |
|   6 | RUBE        | John        | Book C                          | 2013-06-22 | 2013-06-29 |      0 |
+-----+-------------+-------------+---------------------------------+------------+------------+--------+

这就是我想要的:

+-----+-------------+-------------+---------------------------------+------------+------------+--------+
| ID  | EMPLOYEE    | USER        | BOOK                            | LOAN       | DEVOLUTION | STATUS |
+-----+-------------+-------------+---------------------------------+------------+------------+--------+
|   6 | RUBE        | John        | Book A, Book B, Book C          | 2013-06-22 | 2013-06-29 |      0 |
+-----+-------------+-------------+---------------------------------+------------+------------+--------+

Answer 1

考虑使用 GROUP_CONCAT:

SELECT loan.id_loan AS ID,
        employee.login AS EMPLOYEE,
        user.name AS USER,
        GROUP_CONCAT(book.title) AS BOOK,
        loan.date_loan AS LOAN,
        loan.date_devolution AS DEVOLUTION,
        loan.status AS STATUS
FROM Loan loan
    INNER JOIN Employee employee ON employee.id_employee = loan.id_employee
    INNER JOIN User user ON user.id_user = loan.id_user
    INNER JOIN Book_Loan book_loan ON book_loan.id_loan = loan.id_loan
    INNER JOIN Book book ON book.id_book = book_loan.id_book
GROUP BY loan.id_loan