mysql group by不起作用

yh2wf1be 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(359)

我有一个名为nasdaq\U transactions的表，如下所示

Ticker  Close   Date
GOOG    1195.06 08/15/2018
AAPL    215.15  08/15/2018
MSFT    104.56  08/15/2018
GOOG    1198.11 08/16/2018
AAPL    216.1   08/16/2018
MSFT    105.1   08/16/2018
GOOG    1200.96 08/17/2018
AAPL    217.58  08/17/2018
MSFT    107.58  08/17/2018

要生成一个提供输出的查询吗

Ticker  08/15/2018  08/16/2018  08/17/2018
GOOG    1196.06      1198.11     1200.96
AAPL    215.15       216.1       217.58
MSFT    104.56       105.1       107.58

写了一个查询

select tabl.ticker, CASE WHEN tabl.date = '2018-08-15' THEN tabl.close END AS '2018-08-15',CASE WHEN tabl.date = '2018-08-16' THEN tabl.close END AS '2018-08-16', CASE WHEN tabl.date = '2018-08-17' THEN tabl.close END AS '2018-08-15'(select ticker, close, date from nasdaq_transactions where date in ('2018-08-15', '2018-08-16','2018-08-17'))tabl

此查询的部分输出为

+------------+------------+------------+-----------+
| Ticker     | 2018-08-15 | 2018-08-16 |2018-08-17 |
+------------+------------+------------+-----------+
| GOOG       |    1196.06 |     NULL   |     NULL  |
| GOOG       |      NULL  |    1198.11 |     NULL  | 
| GOOG       |      NULL  |     NULL |     1200.96 | 
| AAPL       |    215.15  |     NULL |     NULL    |

不允许分组，这样我就可以得到一个单一的股票代码行，我不是一个数据库Maven。感谢你的工作安排。

mysql

来源：https://stackoverflow.com/questions/51931678/mysql-group-by-does-not-work

2条答案

按热度按时间

mzsu5hc01#

使用 group by 它应该是完美的

select tabl.ticker,
 Max(CASE WHEN tabl.date = '2018-08-15' THEN tabl.close END AS '2018-08-15'),
 Max(CASE WHEN tabl.date = '2018-08-16' THEN tabl.close END AS '2018-08-16'),
 Max(CASE WHEN tabl.date = '2018-08-17' THEN tabl.close END AS '2018-08-15'),
from tabl
group by ticker;

赞(0）回复(0）举报 2021-06-20

au9on6nz2#

我猜你在找这个。请注意，通常最好处理应用程序代码中的数据显示问题。。。

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ticker CHAR(4) NOT NULL
,close DECIMAL(7,2) NOT NULL
,date DATE NOT NULL
,PRIMARY KEY(ticker,date)
);

INSERT INTO my_table VALUES
('GOOG',1195.06,'2018/08/15'),
('AAPL', 215.15,'2018/08/15'),
('MSFT', 104.56,'2018/08/15'),
('GOOG',1198.11,'2018/08/16'),
('AAPL', 216.1 ,'2018/08/16'),
('MSFT', 105.1 ,'2018/08/16'),
('GOOG',1200.96,'2018/08/17'),
('AAPL', 217.58,'2018/08/17'),
('MSFT', 107.58,'2018/08/17');

SELECT ticker
     , MAX(CASE WHEN date = '2018-08-15' THEN close END) '2018-08-15'
     , MAX(CASE WHEN date = '2018-08-16' THEN close END) '2018-08-16'
     , MAX(CASE WHEN date = '2018-08-17' THEN close END) '2018-08-17'
  FROM my_table
 GROUP 
    BY ticker;

+--------+------------+------------+------------+
| ticker | 2018-08-15 | 2018-08-16 | 2018-08-17 |
+--------+------------+------------+------------+
| AAPL   |     215.15 |     216.10 |     217.58 |
| GOOG   |    1195.06 |    1198.11 |    1200.96 |
| MSFT   |     104.56 |     105.10 |     107.58 |
+--------+------------+------------+------------+

赞(0）回复(0）举报 2021-06-20

我来回答

mysql group by不起作用

2条答案

相关问题

热门标签

最新问答