[编程题]商品交易(网易校招笔试真题)
-
热度指数：61888
时间限制：C/C++ 1秒，其他语言2秒
空间限制：C/C++ 32M，其他语言64M
-
算法知识视频讲解
如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量
| id | name | weight |
| 1 | A1 | 100 |
| 2 | A2 | 20 |
| 3 | B3 | 29 |
| 4 | T1 | 60 |
| 5 | G2 | 33 |
| 6 | C0 | 55 |
还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数
| id | goods_id | count |
| 1 | 3 | 10 |
| 2 | 1 | 44 |
| 3 | 6 | 9 |
| 4 | 1 | 2 |
| 5 | 2 | 65 |
| 6 | 5 | 23 |
| 7 | 3 | 20 |
| 8 | 2 | 16 |
| 9 | 4 | 5 |
| 10 | 1 | 3 |
查找购买个数超过20,质量小于50的商品，按照商品id升序排序,如:
| id | name | weight | total |
| 2 | A2 | 20 | 81 |
| 3 | B3 | 29 | 30 |
| 5 | G2 | 33 | 23 |
示例1
输入
CREATE TABLE `goods` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`weight` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `trans` (
`id` int(11) NOT NULL,
`goods_id` int(11) NOT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
insert into goods values(1,'A1',100);
insert into goods values(2,'A2',20);
insert into goods values(3,'B3',29);
insert into goods values(4,'T1',60);
insert into goods values(5,'G2',33);
insert into goods values(6,'C0',55);
insert into trans values(1,3,10);
insert into trans values(2,1,44);
insert into trans values(3,6,9);
insert into trans values(4,1,2);
insert into trans values(5,2,65);
insert into trans values(6,5,23);
insert into trans values(7,3,20);
insert into trans values(8,2,16);
insert into trans values(9,4,5);
insert into trans values(10,1,3);
输出
2|A2|20|81
3|B3|29|30
5|G2|33|23
牛客题解官
发表于 2025-02-26 17:27:25
精华题解
这道题目要求我们从商品和交易数据中筛选出符合条件的商品，我们要做的事情如下：
1. 确定总体问题
我们需要从交易数据中计算每个商品的总购买数量，并结合商品信息筛选出购买数量超过20且质量小于50的商品，并按商品ID升序排序。
2. 分析关键问题
连接表：将goods和trans表连接起来，以便获取
展开全文
广东菜心
发表于 2021-10-18 15:53:27
先对trans表中每个商品的购买数量的求和。
将trans表与goods表用商品id进行连接。
利用where语句进行筛选，并用id进行排序。
select g.id, g.name, g.weight,a.total
from goods g join (
select
展开全文
知识点
首先把两个表连接，使用group by进行分组各类返回一行。
筛选条件使用having进行筛选最后对id进行排序。
代码
select g.id, name, weight, sum(count) as total
from trans as t
join goods as g
on t
展开全文
白色森林
发表于 2025-08-21 10:30:16
WITH goodstotal AS(
SELECT
g.id,
SUM(t.count) AS total
FROM
goods AS g
INNER JOIN
trans AS t ON g.id=t.goods_id
GROUP BY
g.i
展开全文
牛萍萍
发表于 2021-10-16 23:18:07
select g.id, g.name, g.weight, t.total
from goods g
left join( select goods_id, sum(count) total from trans group by goods_id having sum(count)>20
展开全文
# 表意不明的一条题目
select
g.id,
g.name,
g.weight,
sum(t.count) as total
from
goods g inner join trans t
on g.id = t.goods_id
group b
展开全文
内连接+子查询。
先用分组查询，查出每个商品的销量作为虚拟表，最后内连接商品表来查询题目要求的数据。
SELECT goods.*,t.total
FROM goods,(SELECT trans.goods_id,sum(trans.count) total FROM trans GROUP B
展开全文
select
g.id,
g.name,
g.weight,
sum(t.count)
from goods g left join trans t on g.id=t.goods_id
where
g.weight<50
group by t.goods_id
having sum(t.
展开全文
产品小朋友
发表于 2021-10-28 17:13:43
SELECT
goods.id,goods.name,
goods.weight AS weight,
SUM(trans.count) AS total
FROM goods
LEFT JOIN trans
ON goods.id=trans.goods_id
GROUP BY
展开全文
思路：
先对trans表中，根据goods_id分组，使用having过滤出购买数量总和大于20的数据。
连接goods表与上面的临时表，即可查询出所需数据
select g.*, t.total
from goods as g join (select goods_id, sum(count)
展开全文
走走做足
发表于 2021-10-06 14:55:37
select id,name,weight,tatol
from goods as g,
(select goods_id,sum(count) as tatol from trans group by goods_id) t
where g.id = t.goods_id and tatol>
展开全文
