首页 > 试题广场 >

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

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

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

算法知识视频讲解

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)

Mysql

dolen

# 查找购买个数超过20,质量小于50的商品，按照商品id升序排序,如:

-- 注意本题：交易表是流水表，需要聚合后，在过滤；
select 
t2.id,
t2.name,
t2.weight,
sum(t1.count) as total
from trans t1 
inner join goods t2 on t1.goods_id = t2.id 
group by 
t2.id,
t2.name,
t2.weight
having total>20 and t2.weight<50
order by 1;

发表于 2023-10-24 11:20:44 回复(0)

Mysql

勇敢牛牛子冲冲冲

好长一段sql

with
t1 as(
        select
            id
            ,name
            ,weight
        from 
            goods
        where 
            weight < 50 
        )
,t2 as(
        select
            goods_id
            ,sum(count) as total
        from 
            trans
        group by
            goods_id
        having sum(count) > 20
)

select 
    id 
    ,name 
    ,weight 
    ,total
from 
    t1 
join 
    t2
on
    t1.id = t2.goods_id
order by
    id

发表于 2023-05-26 15:49:37 回复(0)

Mysql

muchichi_mayu

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

发表于 2023-02-25 19:02:45 回复(0)

Mysql

Anout

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

发表于 2023-02-24 10:17:22 回复(0)

Mysql

小宏的大牛之路

select g.id,g.name,g.weight,sum(t.count) as 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;

发表于 2022-12-30 14:23:12 回复(0)

Mysql

一颗宏心

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

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

inner join (

select sum(count) as total,goods_id from trans group by goods_id

)t1

on t1.goods_id = t.goods_id

where g.weight<50 and t1.total>20

group by g.id

order by g.id;

说实话我自己都看不懂我自己写的了

发表于 2022-10-19 13:32:23 回复(0)

Mysql

007号编程小白

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

发表于 2022-09-13 19:37:09 回复(0)

Mysql

解槐儿

with table1 as
(
select distinct g.id id1,g.name name1,g.weight weight1,
sum(t.count) over(partition by t.goods_id order by t.goods_id) total
from goods g
join trans t
on g.id = t.goods_id
)

select id1,name1,weight1,total
from table1
where total>=20
and weight1<50;

发表于 2022-09-03 17:57:45 回复(0)

Mysql

牛客100467993号

with tt1 as(
select t1.id, t1.name, weight, count
from goods t1 join trans t2
on t1.id = t2.goods_id)
select id, name, weight, sum(count)total
from tt1
where name in(
select name
from tt1 where
weight < 50
and count >= 20)
group by id, name, weight
order by id

发表于 2022-08-19 18:29:58 回复(0)

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

小耳朵7766

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

发表于 2022-07-06 17:09:01 回复(0)

Mysql

一个好懒的人

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

发表于 2022-07-01 21:59:19 回复(0)

Mysql

圣光·明流火

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

发表于 2022-06-17 12:01:21 回复(0)

Mysql

来牛客玩玩

select distinct id, name, weight, total from goods s1
join (select goods_id, sum(count) over(partition by goods_id) as total from trans) s2
on s1.id = s2.goods_id 
where total > 20 and weight < 50
order by id;

发表于 2022-06-05 14:24:45 回复(0)

Mysql

牛客535290159号

select c.goods_id as id,c.name,round(avg(c.weight),0) as weight,sum(c.count) as total from
(select a.*,b.name,b.weight from trans as a left join goods as b
on a.goods_id = b.id) c
group by c.goods_id,c.name
having avg(c.weight)<50 and sum(c.count)>20
order by id asc

发表于 2022-05-23 13:19:46 回复(0)

Mysql

牛客460304904号

select goods.*,total from goods
right join 
(select goods_id,sum(count) as total from trans group by goods_id having total>20) a1
on goods.id=a1.goods_id
where weight<50
order by goods.id

发表于 2022-05-09 14:02:22 回复(0)