shenggong

分类

题解(49)

/ 注册

全部文章（共12篇）

题解 | #SQL类别高难度试卷得分的截断平均值#

SELECT a.tag,a.difficulty,ROUND((SUM(b.score)-MAX(b.score)-Min(b.score))/(COUNT(b.score)-2),1) AS clip_avg_score FROM examination_info a JOIN exam_rec...

Mysql

2022-07-14

0 172

题解 | #纠错4#

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state = 'MI' UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHER...

Mysql

2022-03-27

0 441

题解 | #将两个 SELECT 语句结合起来（一）#

SELECT prod_id,quantity FROM OrderItems WHERE quantity=100 UNION SELECT prod_id,quantity FROM OrderItems WHERE prod_id LIKE 'BNBG%' ORDER BY prod_id A...

Mysql

2022-03-27

0 266

题解 | #列出供应商及其可供产品的数量#

SELECT a.vend_id,COUNT(b.prod_id) FROM Vendors a LEFT JOIN Products b ON a.vend_id=b.vend_id GROUP BY a.vend_id ORDER BY a.vend_id ASC;

Mysql

2022-03-26

0 252

题解 | #返回产品名称和每一项产品的总订单数#

SELECT a.prod_name,COUNT(b.order_num) AS orders FROM Products a LEFT JOIN OrderItems b ON a.prod_id=b.prod_id GROUP BY a.prod_name ORDER BY a.prod_nam...

Mysql

2022-03-26

0 266

题解 | #确定最佳顾客的另一种方式（二）#

SELECT c.cust_name,SUM(a.item_price*a.quantity) AS total_price FROM OrderItems a INNER JOIN Orders b ON a.order_num = b.order_num INNER JOIN Customer...

Mysql

2022-03-25

0 316

题解 | #返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）#

SELECT c.cust_email FROM OrderItems a INNER JOIN Orders b ON a.order_num = b.order_num INNER JOIN Customers c ON b.cust_id=c.cust_id WHERE a.prod_id =...

Mysql

2022-03-24

0 211

题解 | #返回顾客名称和相关订单号以及每个订单的总价#

SELECT a.cust_name,b.order_num,SUM(c.quantity*c.item_price) AS OrderTotal FROM Customers a INNER JOIN Orders b ON a.cust_id=b.cust_id INNER JOIN Order...

Mysql

2022-03-24

0 234

题解 | #返回顾客名称和相关订单号#

Solution 1: SELECT a.cust_name,b.order_num FROM Customers a,Orders b WHERE a.cust_id=b.cust_id ORDER BY a.cust_name; Solution 2: SELECT a.cust_name,b....

Mysql

2022-03-12

0 231

题解 | #从 Products 表中检索所有的产品名称以及对应的销售总数#

SELECT a.prod_name,b.quant_sold FROM Products a INNER JOIN (SELECT prod_id,SUM(quantity) AS quant_sold FROM OrderItems GROUP BY prod_id) b ON a.prod_i...

Mysql

2022-03-12

0 190