Redian新闻
>
通货膨胀太厉害,坑王坦诚也不容易
avatar
通货膨胀太厉害,坑王坦诚也不容易# Joke - 肚皮舞运动
I*9
1
I have two tables. one is person table, another is sales table. we want to
write an SQL query that returns the two youngest region champions. A region
champion is the sales person who has made the highest sales (SUM(volume)) of
all sales persons of that region.
I am think there should be two steps: - find the region champions - then
find the two youngest region champions. but how can I construct all SQL
query in one.
1. person table
personid region age;
1 ca 30
2 ca 20
5 ca 40
9 ca 35
10 ca 24
11 ca 48
3 ma 34
4 ma 50
6 ma 30
7 il 40
8 il 30
12 il 35
13 il 50
14 pa 25
15 pa 33
16 pa 32
17 pa 36
;
2. sales table;
personid volumn;
1 2000
2 30000
3 200
4 5000
5 1000
6 4000
7 7000
8 900
9 4000
10 6000
11 9000
12 80000
13 6000
14 5000
15 12000
16 13000
17 4000
avatar
r*n
3
SELECT pr.id, sa.volumn, pr.age from person pr, sales sa where pr.id=sa.id
group by pr.region order by sa.volumn desc , pr.age

region
of

【在 I*********9 的大作中提到】
: I have two tables. one is person table, another is sales table. we want to
: write an SQL query that returns the two youngest region champions. A region
: champion is the sales person who has made the highest sales (SUM(volume)) of
: all sales persons of that region.
: I am think there should be two steps: - find the region champions - then
: find the two youngest region champions. but how can I construct all SQL
: query in one.
: 1. person table
: personid region age;
: 1 ca 30

avatar
d*y
4
sql新手写得比较复杂。
MySQL测试通过。
select p1.personId, p1.region, p1.age, s1.volume, champion.maximum from (
select p.region, max(s.volume) as maximum from Person p JOIN Sales s on p.
personId=s.personId group by p.region) as champion join Person p1 on p1.
region=champion.region join Sales s1 on s1.personId=p1.personId where s1.
volume=champion.maximum order by p1.age ASC limit 2;
avatar
d*n
5
楼上的miss了SUM(volume)才是某个人的销售总合。虽然题目数据每人只有一个销售额
,但是根据题目每人可以有多个销售额在sales表中。
下面是考虑求和的版本但是也比表累赘,不知哪位牛人能简化一下
SELECT pr.id, pr.age, pr.region, ps.total FROM person pr INNER JOIN (SELECT
person_id, SUM(volume) AS total FROM sales GROUP BY person_id) AS ps ON ps.
person_id=pr.id, (SELECT MAX(ps.total) AS max, p.region FROM person p INNER
JOIN (SELECT person_id, SUM(volume) AS total FROM sales GROUP BY person_id)
AS ps ON ps.person_id = p.id GROUP BY region) AS max_region
WHERE ps.total = max_region.max AND pr.region = max_region.region ORDER BY
pr.age ASC LIMIT 2
avatar
j*1
6
写了一个复杂的,MS SQL Server 测试通过。先找每个员工的销售小计(sub_total), 再找区域冠军(champion),然后跟person表jion一下找两个最年轻的。
select top 2 sub_total2.*, p3.age
from ( -- get region champion
select region, max_volumn=MAX(sub_total1.total_volumn)
from ( -- get sub total of each person
select p1.personid, p1.region, sum(s1.volumn) as total_volumn
from person p1, sales s1
where p1.personid = s1.personid
group by region, p1.personid
) sub_total1
group by sub_total1.region
) champion,
( -- get sub total of each person
select p2.personid, p2.region, sum(s2.volumn) as total_volumn
from person p2, sales s2
where p2.personid = s2.personid
group by region, p2.personid
) sub_total2,
person p3
where sub_total2.region=champion.region
and sub_total2.total_volumn = champion.max_volumn
and p3.personid = sub_total2.personid
order by p3.age

region
of

【在 I*********9 的大作中提到】
: I have two tables. one is person table, another is sales table. we want to
: write an SQL query that returns the two youngest region champions. A region
: champion is the sales person who has made the highest sales (SUM(volume)) of
: all sales persons of that region.
: I am think there should be two steps: - find the region champions - then
: find the two youngest region champions. but how can I construct all SQL
: query in one.
: 1. person table
: personid region age;
: 1 ca 30

avatar
j*1
7
今天翻书看到SQL Server的CTE可以简化如下,但是不知道符合不符合只用
一个SQL语句的要求:
with
sub_total as
( select p.personid, p.region, sum(s.volumn) as total_volumn
from person p, sales s
where p.personid = s.personid
group by region, p.personid
),
champion as (
select region, max_volumn=MAX(total_volumn)
from sub_total
group by region
)
select top 2 s.*, age
from champion c, sub_total s, person p
where s.region=c.region
and s.total_volumn = c.max_volumn
and p.personid = s.personid
order by p.age

, 再找区域冠军(champion),然后跟person表jion一下找两个最年轻的。

【在 j*******1 的大作中提到】
: 写了一个复杂的,MS SQL Server 测试通过。先找每个员工的销售小计(sub_total), 再找区域冠军(champion),然后跟person表jion一下找两个最年轻的。
: select top 2 sub_total2.*, p3.age
: from ( -- get region champion
: select region, max_volumn=MAX(sub_total1.total_volumn)
: from ( -- get sub total of each person
: select p1.personid, p1.region, sum(s1.volumn) as total_volumn
: from person p1, sales s1
: where p1.personid = s1.personid
: group by region, p1.personid
: ) sub_total1

avatar
B*g
8
数据库版有一句名言"数据库版90%以上的SQL问题可以用partition by解决"

【在 j*******1 的大作中提到】
: 今天翻书看到SQL Server的CTE可以简化如下,但是不知道符合不符合只用
: 一个SQL语句的要求:
: with
: sub_total as
: ( select p.personid, p.region, sum(s.volumn) as total_volumn
: from person p, sales s
: where p.personid = s.personid
: group by region, p.personid
: ),
: champion as (

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。