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

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

2个回答

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='股票详情表';

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='股票详情表'

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问