首页 > 试题广场 >

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

[编程题]商品交易(网易校招笔试真题)

热度指数：32027 时间限制：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

算法知识视频讲解

pein531

交易表作为主表，直接左关联商品表然后以goods_id分组计算每个商品的购买个数，再从结果中过滤出购买个数和商品质量符合要求的商品

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

发表于 2021-01-08 17:36:37 回复(17)

牛客178763728号

思路：

trans表只需要提供每个商品的购买总数，所以嵌套查询，1先处理好trans表，命名为b(select子查询)，2再结合goods表完成任务(外层select)

程序：

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

发表于 2021-06-15 23:19:35 回复(2)

BpointA

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

用了一个group by之后的技巧。因为在goods里面goods id和name、weight是一一对应的，所以在join过程中始终保持这种对应关系，也就是对每一条join后的值，只要id一样，name和weight就一样。所以直接加一个max函数，就能又实现group by后必加聚合函数，又取出goods表里一一对应的值。

发表于 2021-08-09 21:55:36 回复(0)

狗头蜀黍

select 之后应该只有group by后面的字段和聚合函数，left join后面尽量不要用虚拟表，索引会失效，用inner join好一点

select g.id,g.name,g.weight, t.total

from goods as g
inner join (select tr.goods_id,sum(tr.count) as total from trans tr group by tr.goods_id having total >20
) as t
on g.id = t.goods_id
where g.weight < 50;

发表于 2021-03-10 16:26:07 回复(0)

Sqlite

李玉婷的旁听生

SELECT g.*,SUM(COUNT) AS total
FROM goods g
LEFT JOIN trans t ON g.id=t.goods_id
GROUP BY g.id
HAVING  weight<50
AND total>20
order by  g.id

发表于 2021-10-06 09:04:48 回复(0)

Mysql

秃头的少年

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;

算是提供一个带where的思路吧，其实where的条件也可以放在having条件里面的，用and连接。

发表于 2021-09-30 09:11:45 回复(2)

Mysql

牛客162953490号

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

发表于 2022-08-07 23:28:56 回复(0)

Mysql

想吃烤肉的四郎在做ppt

select 
        g.id as id,
        g.name as name,
        g.weight  as weight,
        sum(t.count) as total
    from trans as t left join goods as g on t.goods_id=g.id
    group by t.goods_id
    having (total>20 and g.weight<50)
    order by g.id asc;
    
    
#这个题描述有歧义，应该写清楚，total是总的购买个数，weight是单个商品的总量，

发表于 2021-10-21 11:40:59 回复(0)

Lauseusing

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

发表于 2021-04-16 01:47:05 回复(0)

牛客845115516号

SELECT id, name, weight, t.total AS total

FROM goods JOIN (SELECT goods_id, SUM(count) AS total

FROM trans

GROUP BY goods_id

HAVING SUM(count)>20) AS t

ON goods.id=t.goods_id

WHERE weight<50;

发表于 2021-03-11 22:51:21 回复(0)

牛客804265579号

基础题

SELECT g.*, sum(t.count) AS total

FROM goods g JOIN trans t
ON g.id = t.goods_id
WHERE weight < 50
GROUP BY t.goods_id
HAVING total > 20;

发表于 2021-01-03 01:54:20 回复(0)

好奇的打工鸭在泡澡

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

编辑于 2024-04-12 10:46:13 回复(0)

zxcv0112358

with goods_cnt as(
    select goods_id,sum(count) as total from trans group by goods_id
)
,
tmp1 as(
    select goods.id,goods.name,goods.weight,goods_cnt.total
    from goods inner join goods_cnt 
    on goods.id=goods_cnt.goods_id
)

select  id,	name,	weight,	total from tmp1
where total>20 and weight<50 
order by id asc



-- mysql 8.0开始支持 with tmp1 as ...语法
-- 如果你写不清楚，就一步一个临时表写。

发表于 2023-01-08 17:29:25 回复(0)

Mysql

世界都爱热热闹闹

好久没写sql,差点忘了having,这题的关键点就在于分组group by之后跟上having做条件筛选,提供两种思路(其实我没大理解select之后为什么可以放出group by之外的非聚合函数,感觉有待商榷??):

select a.*,sum(count) total from goods a,trans b

where a.id = b.goods_id
group by goods_id
having weight < 50 and total > 20
order by a.id

select a.*,b.total total from goods a,
(select goods_id,sum(count) total from trans
group by goods_id
having sum(count) > 20) b
where weight < 50
and a.id = b.goods_id
order by a.id

发表于 2021-11-03 19:52:28 回复(0)

Mysql

360内推-佳佳

这道题蛮常规的，不过给自己提个醒，窗口函数分区不是全局分区！

select DISTINCT t1.id, t1.name, t1.weight, t2.total
FROM goods t1,
(select goods_id, sum(count) over (partition by goods_id) total
from trans) t2
where t1.id = t2.goods_id
and total>20
and weight<50

发表于 2021-10-06 18:19:28 回复(1)

友好的大师兄在泡池子

SELECT g.id, g.name, g.weight, sum(t.count) as total
FROM goods g right join trans t on g.id = t.goods_id
GROUP BY g.id
HAVING g.weight<50 and total>20
ORDER BY id
------
SELECT g.id, g.name, g.weight, total
FROM goods g , (
    SELECT goods_id, sum(count) as total
    FROM trans
    group by goods_id
) b
where g.weight<50
and g.id = b.goods_id and total>20
ORDER BY id

发表于 2024-03-07 14:43:20 回复(0)

牛客712664144号

select g.id,g.name,g.weight, sum(t.count) total

from goods g left join trans t on g.id=t.goods_id

where g.weight <50

group by g.id

having total>20

order by g.id

编辑于 2024-03-04 14:46:39 回复(0)

Mysql

keepyang

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

发表于 2024-01-23 21:51:16 回复(0)

Mysql

安之ccy

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

发表于 2024-01-03 15:45:16 回复(0)

Mysql

Kalina2023

select
    goods_id as id,
    name,
    weight,
    cnt as total
from
(
select
    t.goods_id,
    g.name,
    g.weight,
    sum(t.count) as cnt
from trans t 
left join goods g on t.goods_id = g.id
group by 1,2,3
) t1
where weight < 50
and cnt > 20
order by goods_id asc

发表于 2023-11-27 23:19:32 回复(0)