r**********e
发帖数: 194 | 1 小弟想了半天也没啥思路，想上来请教诸位大牛
现在有四个table
PEAK (NAME, ELEV, DIFF, MAP, REGION)
CLIMBER (NAME, SEX)
PARTICIPATED (TRIP_ID, NAME)
CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)
Your assignment is to write SQL queries that answer the following question
A repeat ascent of a peak is defined as any ascent after the first one by a
particular person. So if a person climbs a peak three times, and another
person climbs that peak four times, the peak has five repeated ascents. Find
the ten peaks with the greatest number of repeat ascents.
这个题目我的想法是先求出每一个人爬某座山的次数，然后再算这个山的repeat
ascent. 但是我不知道如何在sql里实现，跪求这里的诸位高手给点意见..... | c******n
发帖数: 7263 | 2 你这四张表的字段有问题：
peak和climber都有name，participated里面的name应该是指其中一个，指的是哪一个
climbed里面有个peak，应该对应peak表里面一个字段吧
你说明一下
a
Find
【在 r**********e 的大作中提到】
: 小弟想了半天也没啥思路，想上来请教诸位大牛
: 现在有四个table
: PEAK (NAME, ELEV, DIFF, MAP, REGION)
: CLIMBER (NAME, SEX)
: PARTICIPATED (TRIP_ID, NAME)
: CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)
: Your assignment is to write SQL queries that answer the following question
: A repeat ascent of a peak is defined as any ascent after the first one by a
: particular person. So if a person climbs a peak three times, and another
: person climbs that peak four times, the peak has five repeated ascents. Find
| r**********e
发帖数: 194 | 3 这里是说明：
PEAK gives information about the mountain peaks that the SPCC is interested
in. This table lists the name of each peak, its elevation, its difficulty
level for climbers (on a scale of 1 to 5), the map that it is located on,
and the region of the Sierra Nevada that it is located in.
CLIMBER lists the SPCC membership, and gives their name and gender.
PARTICPATED gives the set of climbers who participated in each of the
various SPCC-sponsored climbing trips. The number of participants in each
trip varies.
CLIMBED tells which peaks were climbed on each of the SPCC-sponsored
climbing trips, along with the date that each peak was climbed.
peak里的name是说山峰的名字，climber的name是说登山者的名字。participated里的
name是说参与登山的人的名字。climbed是说已经登过的山的名字，里面的peak对应的
就是山峰的名字
【在 c******n 的大作中提到】
: 你这四张表的字段有问题：
: peak和climber都有name，participated里面的name应该是指其中一个，指的是哪一个
: climbed里面有个peak，应该对应peak表里面一个字段吧
: 你说明一下
:
: a
: Find
| y*******u
发帖数: 930 | 4 首先match join(2个TRIP_ID相等） PARTICIPATED和climbed， 形成par-climbed,
这里面就是哪个人 去了啥peak
然后对新的这个relation group over （NAME，PEAK） 就得出啥人登啥山几次，然后
projection 算出来一个新column 把登几次都减一，
最后group over PEAK， 算出你要的东西。
小弟想了半天也没啥思路，想上来请教诸位大牛
现在有四个table
PEAK (NAME, ELEV, DIFF, MAP, REGION)
CLIMBER (NAME, SEX)
PARTICIPATED (TRIP_ID, NAME)
CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)
Your assignment is to write SQL queries that answer the following question
A repeat ascent of a peak is defined as any ascent after the first one by a
particular person. So if a person climbs a peak three times, and another
person climbs that peak four times, the peak has five repeated ascents. Find
the ten peaks with the greatest number of repeat ascents.
这个题目我的想法是先求出每一个人爬某座山的次数，然后再算这个山的repeat
ascent. 但是我不知道如何在sql里实现，跪求这里的诸位高手给点意见.....
【在 r**********e 的大作中提到】
: 小弟想了半天也没啥思路，想上来请教诸位大牛
: 现在有四个table
: PEAK (NAME, ELEV, DIFF, MAP, REGION)
: CLIMBER (NAME, SEX)
: PARTICIPATED (TRIP_ID, NAME)
: CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)
: Your assignment is to write SQL queries that answer the following question
: A repeat ascent of a peak is defined as any ascent after the first one by a
: particular person. So if a person climbs a peak three times, and another
: person climbs that peak four times, the peak has five repeated ascents. Find
| y*******u
发帖数: 930 | 5 当然这个的前提是你前2个relation 的identifier分别是NAME 和NAME
也就是climber和PEAK都没有重名的。
【在 y*******u 的大作中提到】
: 首先match join(2个TRIP_ID相等） PARTICIPATED和climbed， 形成par-climbed,
: 这里面就是哪个人 去了啥peak
: 然后对新的这个relation group over （NAME，PEAK） 就得出啥人登啥山几次，然后
: projection 算出来一个新column 把登几次都减一，
: 最后group over PEAK， 算出你要的东西。
:
: 小弟想了半天也没啥思路，想上来请教诸位大牛
: 现在有四个table
: PEAK (NAME, ELEV, DIFF, MAP, REGION)
: CLIMBER (NAME, SEX)
| n****f
发帖数: 905 | 6 你把四个表用 KEY 给JOIN 起来， 就形成一个完整的信息。
1. SELECT A.*, B.*, C.*, D.*
FROM A,B,C,D WHERE A.XX= B.XX ..... <= 这些您得自己去写。
暂时叫这个表是 W
在 W 里面， 你要把每人， 每山头的次数提取出来。
所以，
1. 中的 SQL 就应该CHANGE TO:
2. SELECT COUNT(*)-1 AS CT, CLIMBER.NAME AS CN, PEAK.NAME AS PN FROM
A,B,C,D WHERE A.XX= B.XX .....
GROUP BY LIMBER.NAME, PEAK.NAME
3. SELECT PN, SUM(CT) FROM
(SELECT COUNT(*)-1 AS CT, CLIMBER.NAME AS CN, PEAK.NAME AS PN FROM
A,B,C,D WHERE A.XX= B.XX .....
GROUP BY LIMBER.NAME, PEAK.NAME) X
GROUP BY PN
ORDER BY SUM(CT) DESC
至于是 TOP 10 还是 20 ， 得您自己搞定了。 | B*****g
发帖数: 34098 | 7 问家庭作业要给包子！！！
又来了，90%+的数据库版SQL问题可以用partition by解决
a
Find
【在 r**********e 的大作中提到】
: 小弟想了半天也没啥思路，想上来请教诸位大牛
: 现在有四个table
: PEAK (NAME, ELEV, DIFF, MAP, REGION)
: CLIMBER (NAME, SEX)
: PARTICIPATED (TRIP_ID, NAME)
: CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)
: Your assignment is to write SQL queries that answer the following question
: A repeat ascent of a peak is defined as any ascent after the first one by a
: particular person. So if a person climbs a peak three times, and another
: person climbs that peak four times, the peak has five repeated ascents. Find
| r**********e
发帖数: 194 | 8 倾家荡产给包子
【在 B*****g 的大作中提到】
: 问家庭作业要给包子！！！
: 又来了，90%+的数据库版SQL问题可以用partition by解决
:
: a
: Find
| c******n
发帖数: 7263 | 9 我觉得还是不要发包子的好，有问题知道的就说一声，举手之劳而已，万一有新人没包
子咋办？
【在 B*****g 的大作中提到】
: 问家庭作业要给包子！！！
: 又来了，90%+的数据库版SQL问题可以用partition by解决
:
: a
: Find
| e****7
发帖数: 4387 | 10 -- CREATE TABLE STRUCTURE
IF OBJECT_ID('dbo.PEAK', 'U') IS NOT NULL DROP TABLE PEAK
GO
IF OBJECT_ID('dbo.CLIMBER', 'U') IS NOT NULL DROP TABLE CLIMBER
GO
IF OBJECT_ID('dbo.PARTICIPATED', 'U') IS NOT NULL DROP TABLE PARTICIPATED
GO
IF OBJECT_ID('dbo.CLIMBED', 'U') IS NOT NULL DROP TABLE CLIMBED
GO
CREATE TABLE PEAK
(
NAME VARCHAR(255),
ELEV INT,
DIFF INT,
MAP INT,
REGION INT
)
GO
CREATE TABLE CLIMBER
(
NAME VARCHAR(255),
SEX CHAR(1)
)
GO
CREATE TABLE PARTICIPATED
(
TRIP_ID INT,
NAME VARCHAR(255)
)
GO
CREATE TABLE CLIMBED (
TRIP_ID INT, -- REFERS TO PARTICIPATED TABLE
PEAK VARCHAR(255), -- REFERS TO PEAK TABLE
WHEN_CLIMBED DATE
)
GO
-- INSERT SAMPLE DATA
INSERT INTO PEAK VALUES('P1', 1, 1, 1, 1)
INSERT INTO PEAK VALUES('P2', 1, 1, 1, 1)
INSERT INTO PEAK VALUES('P3', 1, 1, 1, 1)
INSERT INTO PEAK VALUES('P4', 1, 1, 1, 1)
INSERT INTO PEAK VALUES('P5', 1, 1, 1, 1)
INSERT INTO CLIMBER VALUES('C1', 'F')
INSERT INTO CLIMBER VALUES('C2', 'F')
INSERT INTO CLIMBER VALUES('C3', 'M')
INSERT INTO CLIMBER VALUES('C4', 'F')
INSERT INTO CLIMBER VALUES('C5', 'M')
INSERT INTO CLIMBER VALUES('C6', 'M')
INSERT INTO CLIMBER VALUES('C7', 'U')
INSERT INTO PARTICIPATED VALUES('1', 'C1')
INSERT INTO PARTICIPATED VALUES('1', 'C2')
INSERT INTO PARTICIPATED VALUES('2', 'C1')
INSERT INTO PARTICIPATED VALUES('2', 'C2')
INSERT INTO PARTICIPATED VALUES('3', 'C2')
INSERT INTO PARTICIPATED VALUES('3', 'C3')
INSERT INTO PARTICIPATED VALUES('3', 'C5')
INSERT INTO PARTICIPATED VALUES('3', 'C6')
INSERT INTO PARTICIPATED VALUES('4', 'C1')
INSERT INTO PARTICIPATED VALUES('4', 'C3')
INSERT INTO PARTICIPATED VALUES('4', 'C5')
INSERT INTO PARTICIPATED VALUES('4', 'C6')
INSERT INTO PARTICIPATED VALUES('5', 'C1')
INSERT INTO PARTICIPATED VALUES('5', 'C4')
INSERT INTO PARTICIPATED VALUES('5', 'C5')
INSERT INTO PARTICIPATED VALUES('5', 'C6')
INSERT INTO PARTICIPATED VALUES('5', 'C7')
INSERT INTO PARTICIPATED VALUES('5', 'C3')
INSERT INTO CLIMBED VALUES(1, 'P1', '02/12/2012')
INSERT INTO CLIMBED VALUES(1, 'P2', '02/12/2012')
INSERT INTO CLIMBED VALUES(1, 'P3', '02/13/2012')
INSERT INTO CLIMBED VALUES(1, 'P4', '02/14/2012')
INSERT INTO CLIMBED VALUES(2, 'P1', '03/12/2012')
INSERT INTO CLIMBED VALUES(2, 'P2', '03/12/2012')
INSERT INTO CLIMBED VALUES(2, 'P3', '03/13/2012')
INSERT INTO CLIMBED VALUES(2, 'P4', '03/14/2012')
INSERT INTO CLIMBED VALUES(3, 'P1', '04/12/2012')
INSERT INTO CLIMBED VALUES(3, 'P2', '04/12/2012')
INSERT INTO CLIMBED VALUES(3, 'P3', '04/13/2012')
INSERT INTO CLIMBED VALUES(3, 'P4', '04/14/2012')
INSERT INTO CLIMBED VALUES(4, 'P5', '05/12/2012')
INSERT INTO CLIMBED VALUES(4, 'P2', '05/12/2012')
INSERT INTO CLIMBED VALUES(4, 'P3', '05/13/2012')
INSERT INTO CLIMBED VALUES(4, 'P4', '05/14/2012')
INSERT INTO CLIMBED VALUES(5, 'P1', '06/12/2012')
INSERT INTO CLIMBED VALUES(5, 'P2', '06/12/2012')
INSERT INTO CLIMBED VALUES(5, 'P3', '06/13/2012')
INSERT INTO CLIMBED VALUES(5, 'P4', '06/14/2012')
INSERT INTO CLIMBED VALUES(5, 'P5', '06/14/2012')
GO
-- SOLUTION
WITH PEAK_CLIMBED AS
(
SELECT P.TRIP_ID, C.WHEN_CLIMBED, P.NAME AS PERSON, C.PEAK
FROM PARTICIPATED P JOIN CLIMBED C ON P.TRIP_ID = C.TRIP_ID
), ASCENT AS (
SELECT PEAK, PERSON, COUNT(*) AS ASCENTS
FROM PEAK_CLIMBED
GROUP BY PEAK, PERSON
), REPEATED_ASCENT AS
(
SELECT PEAK, PERSON, ASCENTS - 1 AS REPEATED_ASCENTS
FROM ASCENT
WHERE ASCENTS > 1
)
SELECT TOP 10 PEAK, SUM(REPEATED_ASCENTS) AS NUMBER_OF_REPEATED_ASCENTS
FROM REPEATED_ASCENT
GROUP BY PEAK
ORDER BY SUM(REPEATED_ASCENTS) DESC | e****7
发帖数: 4387 | 11
以上是tsql version
呵呵，码了半天，怎么也发一个吧。
【在 r**********e 的大作中提到】
: 倾家荡产给包子
| B*****g
发帖数: 34098 | 12 问家庭作业是不对的，不给酬劳怎么行
【在 c******n 的大作中提到】
: 我觉得还是不要发包子的好，有问题知道的就说一声，举手之劳而已，万一有新人没包
: 子咋办？
| n****f
发帖数: 905 | 13 呵呵， 有包子吗？ 连一个谢字也没看见哟？
叫原本饥饿的民工我多了一份心寒啊! | e****7
发帖数: 4387 | 14
呵呵，没有
【在 n****f 的大作中提到】
: 呵呵， 有包子吗？ 连一个谢字也没看见哟？
: 叫原本饥饿的民工我多了一份心寒啊!
|
|
