I*********9
发帖数: 15 | 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 | r******n
发帖数: 170 | 2 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
| d********y
发帖数: 2114 | 3 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; | d***n
发帖数: 65 | 4 楼上的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 | j*******1
发帖数: 425 | 5 写了一个复杂的，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
| j*******1
发帖数: 425 | 6 今天翻书看到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
| B*****g
发帖数: 34098 | 7 数据库版有一句名言＂数据库版９０％以上的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 (
|
|
