我有下表和日期：
tbl_client
No Client Name
1 John Saw
2 Michael Ken
3 Sam Meyer
4 Eaves Power
和
tbl_appointment
No Name Date Type
1 1 8/4/2017 A
2 2 5/5/2017 B
3 3 9/6/2017 C
4 4 10/6/2017 C
5 1 12/6/2017 A
6 2 15/7/2017 B
7 1 11/10/2017 B
8 2 21/10/2017 A
9 3 5/11/2017 B
10 1 6/11/2017 C
11 2 5/11/2017 C
12 4 15/11/2017 A
第一个表包含客户列表，第二个表包含列表约会日期。
我想使用My-SQL生成一个像这样的表：
No Name Date A B C
1 John Saw 6/11/2017 12/6/2017 11/10/2017 -
2 Michael Ken 5/11/2017 1/11/2017 15/7/2017 -
3 Sam Meyer 5/11/2017 - - -
4 Eaves Power 15/11/2017 - - 10/6/2017
我尝试了以下查询，但没有成功：
SELECT
a.no,
a.name,
b.date,
d.date AS A
FROM
`tbl_client` `a`
INNER JOIN `tbl_appointment` `b`
ON `a`.`client_id` = `b`.`client_id`
LEFT JOIN `tbl_appointment` `d`
ON `d`.`client_id` = `a`.`id`
WHERE `d`.`client_id` = `a`.`id` ;
如何优化查询以提供上述报告？
最佳答案
阅读，标记，学习并向内消化以下内容。剩下的问题是显示问题，最好在应用程序代码中解决：
DROP TABLE IF EXISTS client;
CREATE TABLE client
(client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,client_name VARCHAR(30) NOT NULL
);
INSERT INTO client VALUES
(1,'John Saw'),
(2,'Michael Ken'),
(3,'Sam Meyer'),
(4,'Eaves Power');
DROP TABLE IF EXISTS appointment;
CREATE TABLE appointment
(appointment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,client_id INT NOT NULL
,date DATE NOT NULL
,type CHAR(1) NOT NULL
);
INSERT INTO appointment VALUES
( 1, 1 , '2017/04/08', 'A'),
( 2, 2 , '2017/05/05', 'B'),
( 3, 3 , '2017/06/09', 'C'),
( 4, 4 , '2017/06/10', 'C'),
( 5, 1 , '2017/06/12', 'A'),
( 6, 2 , '2017/07/15', 'B'),
( 7, 1 , '2017/10/11', 'B'),
( 8, 2 , '2017/10/21', 'A'),
( 9, 3 , '2017/11/05', 'B'),
(10, 1 , '2017/11/06', 'C'),
(11, 2 , '2017/11/05', 'C'),
(12, 4 , '2017/11/15', 'A');
SELECT c.*
, a.appointment_id
, date
, type
FROM client c
LEFT
JOIN appointment a
ON a.client_id = c.client_id
ORDER
BY client_id
, type;
+-----------+-------------+----------------+------------+------+
| client_id | client_name | appointment_id | date | type |
+-----------+-------------+----------------+------------+------+
| 1 | John Saw | 1 | 2017-04-08 | A |
| 1 | John Saw | 5 | 2017-06-12 | A |
| 1 | John Saw | 7 | 2017-10-11 | B |
| 1 | John Saw | 10 | 2017-11-06 | C |
| 2 | Michael Ken | 8 | 2017-10-21 | A |
| 2 | Michael Ken | 2 | 2017-05-05 | B |
| 2 | Michael Ken | 6 | 2017-07-15 | B |
| 2 | Michael Ken | 11 | 2017-11-05 | C |
| 3 | Sam Meyer | 9 | 2017-11-05 | B |
| 3 | Sam Meyer | 3 | 2017-06-09 | C |
| 4 | Eaves Power | 12 | 2017-11-15 | A |
| 4 | Eaves Power | 4 | 2017-06-10 | C |
+-----------+-------------+----------------+------------+------+
关于mysql - MySQL生成数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47053979/