Another one - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Another one

Another one# Database - 数据库

h*t2099-11-01 07:11

1 楼

how many students participate in the most popular student activity
what's the name of this actiity
ACTIVITY (
actid number ,
activity_name varchar2(25)
);
PARTICIPATES_IN (
stuid number,
actid number
);

w*g2099-11-01 07:11

2 楼

another one? :)
is this your homework assignment for database class?

【在 h******t 的大作中提到】

: how many students participate in the most popular student activity
: what's the name of this actiity
: ACTIVITY (
: actid number ,
: activity_name varchar2(25)
: );
: PARTICIPATES_IN (
: stuid number,
: actid number
: );

h*t2099-11-01 07:11

3 楼

come on, help me, due tomorrow

【在 w*****g 的大作中提到】

: another one? :)
: is this your homework assignment for database class?

p*y2099-11-01 07:11

4 楼

总觉得这种做法不太好.

【在 h******t 的大作中提到】

:
: come on, help me, due tomorrow

s*i2099-11-01 07:11

5 楼

creat view A-N(actid,c)
as select actid, count(*)
from participates_in
group by actid;
select activity_name, Max(c)
from A-N, ACTIVITY
where A-N.actid=activity.actid;

【在 h******t 的大作中提到】

:
: come on, help me, due tomorrow

h*t2099-11-01 07:11

6 楼

不懂就问,有啥不好
不懂装懂不好,太害羞也不好

【在 p*****y 的大作中提到】

: 总觉得这种做法不太好.

h*t2099-11-01 07:11

7 楼

I got in this way, never thought use view
thanks sui
SELECT C1, activity_name
FROM (SELECT COUNT(stuid) C1, activity_name
FROM (SELECT Participates_in.stuid, Activity.activity_name
FROM Participates_in , Activity
WHERE Participates_in.actid=Activity.actid)
GROUP BY activity_name)
WHERE C1= (SELECT MAX(C2) FROM (SELECT COUNT(stuid) C2
FROM Participates_in GROUP BY Participates_in.actid));

【在 s*i 的大作中提到】

: creat view A-N(actid,c)
: as select actid, count(*)
: from participates_in
: group by actid;
: select activity_name, Max(c)
: from A-N, ACTIVITY
: where A-N.actid=activity.actid;

q*j2099-11-01 07:11

8 楼

Is there any requirements to use just one query? "create view" is
considered as one query itself.
If it's homework question, you'd better check the requirement, otherwise,
"create view" is a more intuitive way to do the query.

【在 h******t 的大作中提到】

: I got in this way, never thought use view
: thanks sui
: SELECT C1, activity_name
: FROM (SELECT COUNT(stuid) C1, activity_name
: FROM (SELECT Participates_in.stuid, Activity.activity_name
: FROM Participates_in , Activity
: WHERE Participates_in.actid=Activity.actid)
: GROUP BY activity_name)
: WHERE C1= (SELECT MAX(C2) FROM (SELECT COUNT(stuid) C2
: FROM Participates_in GROUP BY Participates_in.actid));

s*i2099-11-01 07:11

9 楼

I remember we can not embed another SELECT clause in FROM clause!
However, I guess you can JOIN some tables in FROM clause.

【在 h******t 的大作中提到】

q*j2099-11-01 07:11

10 楼

MySQL can't, but other commercial DB can.

【在 s*i 的大作中提到】

: I remember we can not embed another SELECT clause in FROM clause!
: However, I guess you can JOIN some tables in FROM clause.

s*i2099-11-01 07:11

11 楼

I don't even believe ORACLE can.

【在 q*j 的大作中提到】

: MySQL can't, but other commercial DB can.

h*t2099-11-01 07:11