数据库设计面试题 求各位大神帮帮忙

Question

场景：证券交易中，投资人每日会购买一定数量的股票，系统每日都会统计当天各投资人的交易信息（如：当日最高股票收盘价等）。
股票信息：股票名称，发行人
投资人：投资人名称，投资账户
①请根据该场景，设计对应数据库表。注：不需详细表结构，E-R图等示意即可；
②假设当日的交易信息中，有对应股票的收盘价信息。请写出SQL语句（可以写多条）：当日交易记录条数超过5笔的股票发行人名称和股票最高收盘价。

Answer 1

SELECT
s.id,
s.publisher,
sd.closing_price
FROM
stock s
LEFT JOIN stock_detail sd ON sd.stock_id = s.id
WHERE
s.id IN (
SELECT
a.stock_id
FROM
(
SELECT
stock_id,
count(stock_id) AS num
FROM
stock_deal
WHERE
created_time BETWEEN DATE_FORMAT(now(), '%Y-%m-%d 00:00:00')
AND DATE_FORMAT(now(), '%Y-%m-%d 23:59:59')
) a
WHERE
a.num > 5
);

SELECT
s.id,
s.publisher,
sd.closing_price
FROM
stock s
LEFT JOIN stock_detail sd ON sd.stock_id = s.id
LEFT JOIN (
SELECT
stock_id,
count(stock_id) AS num
FROM
stock_deal
WHERE
created_time BETWEEN DATE_FORMAT(now(), '%Y-%m-%d 00:00:00')
AND DATE_FORMAT(now(), '%Y-%m-%d 23:59:59')
) a ON a.stock_id = s.id
WHERE
a.num > 5;

表结构如下：
CREATE TABLE `user` (

id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='投资人表';

CREATE TABLE stock (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT '' COMMENT '股票名',
publisher varchar(255) DEFAULT '' COMMENT '发行人',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='股票表';

CREATE TABLE stock_deal (
id bigint(20) NOT NULL AUTO_INCREMENT,
stock_id bigint(20) NOT NULL,
type int(11) NOT NULL COMMENT '1:买入，2：卖出',
user_id bigint(20) NOT NULL,
created_time datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='股票交易表';

CREATE TABLE stock_detail (
id bigint(20) NOT NULL AUTO_INCREMENT,
stock_id bigint(20) NOT NULL,
opening_price double(10,2) DEFAULT NULL,
closing_price double(10,2) DEFAULT NULL,
created_time datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='股票详情表';