商品交易(网易校招笔试真题)

如下有一张商品表（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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3368KB, 提交时间: 2021-09-17

select g.id, g.name, g.weight, sum(t.count) as total
from goods g, trans t
where g.id = t.goods_id and g.weight < 50
group by t.goods_id
having total > 20

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2022-01-01

SELECT
	g.id,
	g.NAME,
	g.weight,
	a.sum total 
FROM
	goods g
	JOIN ( SELECT *, sum( count ) sum FROM trans GROUP BY goods_id HAVING sum > 20 ) a ON g.id = a.goods_id 
WHERE
	g.weight < 50

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3380KB, 提交时间: 2021-12-27

SELECT g.id, g.name, g.weight, SUM(t.count) AS total
FROM goods AS g
JOIN trans AS t 
ON t.goods_id = g.id
GROUP BY t.goods_id
HAVING (total > 20 AND g.weight < 50)
ORDER BY g.id ASC

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3380KB, 提交时间: 2021-12-19

SELECT
    a.id,
    a.name,
    a.weight,
    sum(b.count) as total
from goods a
left join trans b
on a.id=b.goods_id
WHERE a.weight<50
group by a.name
having total>20;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3392KB, 提交时间: 2021-11-22

select a.id,a.name,
a.weight,sum(b.count)
from goods a
inner join trans b
on a.id = b.goods_id
 where a.weight<50
 group by a.id having sum(b.count)>20

上一题

下一题

详情