mySQL - 转置表并仅打印最大值

Question

我有下表，我可以获得每个 DATE 的最大值，但是我想同时转置它。

我得到的表是:

+--------+------+-------------+----------------+------------+-------+
| LEG_ID | TAIL | REPORT_NAME | REPORT_ELEMENT | DATE       | VALUE |
+--------+------+-------------+----------------+------------+-------+
|     10 | ABC  | Report1     | A              | 2018-12-17 |  1050 |
|      9 | XYZ  | Report1     | B              | 2018-12-17 |  1020 |
|      9 | XYZ  | Report1     | A              | 2018-12-16 |  1021 |
|      8 | ABC  | Report1     | B              | 2018-12-16 |  1022 |
|      7 | XYZ  | Report1     | A              | 2018-12-15 |  1010 |
|      6 | ABC  | Report1     | B              | 2018-12-15 |  1010 |
|      5 | ABC  | Report1     | A              | 2018-12-13 |  1001 |
|      4 | XYZ  | Report1     | B              | 2018-12-12 |  1001 |
+--------+------+-------------+----------------+------------+-------+

我获取最大值的 sql 代码是:

SELECT *
FROM READING WHERE (DATE,TAIL, REPORT_ELEMENT, VALUE) IN
( SELECT MAX(DATE),TAIL, REPORT_ELEMENT, VALUE
FROM READING
GROUP BY TAIL, REPORT_ELEMENT
);

我想要的结果如下:

+---------+----------+----------+-----------+-----------+
|TAIL     | REPORT A | REPORT B | DATE A    | DATE B    |
+---------+----------+----------+-----------+-----------+
|ABC      | 1050     |    1022  |2018-12-17 |2018-12-16 |
|XYZ      | 1021     |    1020  |2018-12-16 |2018-12-17 |
+---------+----------+----------+-----------+-----------+

重现我的表格的代码:

CREATE TABLE READING
(

LEG_ID int (10),
TAIL char(255),
REPORT_NAME char (255),
REPORT_ELEMENT char(255),
DATE date,
VALUE int (10));

insert into READING values 
(10, "ABC", "Report1", "A", '2018-12-17', 1050),
(9,  "XYZ", "Report1", "B", '2018-12-17', 1020),
(9,  "XYZ", "Report1", "A", '2018-12-16', 1021),
(8,  "ABC", "Report1", "B", '2018-12-16', 1022),
(7,  "XYZ", "Report1", "A", '2018-12-15', 1010),
(6,  "ABC", "Report1", "B", '2018-12-15', 1010),
(5,  "ABC", "Report1", "A", '2018-12-13', 1001),
(4,  "XYZ", "Report1", "B", '2018-12-12', 1001);

Answer 1

像这样转动它:

SELECT 
   TAIL, 
   MAX(CASE WHEN REPORT_ELEMENT = 'A' THEN DATE END) AS DATEA,
   MAX(CASE WHEN REPORT_ELEMENT = 'B' THEN DATE END) AS DATEB,
   MAX(CASE WHEN REPORT_ELEMENT = 'A' THEN VALUE END) AS VALUEA,
   MAX(CASE WHEN REPORT_ELEMENT = 'B' THEN VALUE END) AS VALUEB
FROM READING
GROUP BY TAIL