首页 > 数据库技术 > 详细

sql 面试题

时间:2020-09-21 12:49:26      阅读:81      评论:0      收藏:0      [点我收藏+]

技术分享图片

为了解决该题,先捋一下sql解题的基本两种方法:表连接和子查询

# 建表
CREATE DATABASE IF NOT EXISTS company;
 
USE company;
CREATE TABLE customer(
user_id VARCHAR(10) NOT NULL,
user_name VARCHAR(64) NOT NULL,
PRIMARY KEY(user_id)
);
CREATE TABLE shopping(
product_id INT(10) NOT NULL,
user_id VARCHAR(10) NOT NULL,
amout DECIMAL(12,2) NOT NULL
);
INSERT INTO customer VALUES(‘A‘,‘张三‘),(‘B‘,‘李四‘),(‘C‘,‘王五‘);
INSERT INTO shopping VALUES(1000000000,‘A‘,30),(1000000001,‘A‘,80),(1000000002,‘A‘,10.50),
(1000000001,‘B‘,30),(1000000004,‘B‘,100),
(1000000005,‘C‘,200);
# 列出购物超过一次且平均单价超过50的客户信息
## 方法1 表连接
SELECT t2.*
FROM (SELECT user_id,AVG(`amout`) avg_amount,COUNT(*) num
FROM `shopping`
GROUP BY `user_id`)t1 INNER JOIN `customer` t2
WHERE t1.user_id=t2.user_id
AND t1.avg_amount>50 AND t1.num>1
## 方法2 子查询
SELECT A.user_id,A.user_name
FROM customer A
WHERE A.user_id IN (SELECT B.user_id FROM shopping B GROUP BY B.user_id HAVING COUNT(B.user_id)>1 AND AVG(B.amout)>50);

sql 面试题

原文:https://www.cnblogs.com/alidata/p/13704697.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!