数据库sql语句练习（较难）

例题：关系较复杂

 personinstory表                                 love表

              

● 先按照性别，然后按照美丽程度由高往低的顺序， 列出所有人物的名字，性别和美丽程度

select Person.pname 名字,Person.psex性别,Person.pbeauty美丽程度
from Person
order by psex,pbeauty desc

● 先按照人名，然后按照被爱的人名的顺序，列出 所有爱情关系的施爱人，被爱人，爱情指数，受伤指 数

select p1.pname 施爱人,p2.pname 被爱人,love.love_index爱情指数,love.iniury_index受伤指数
from Person p1,Person p2,love
where p1.pid =love.pid
and p2.pid=love.lovepid
order by pl.pname,p2.pname

● 哪些男人没有得到任何一个女人的爱？

select Person.pname没有得到爱的男人
from Person
where Person.psex='男'
and Personpid not in
(select love.lovepid
from love)

● 哪些男人至少爱着2个女人？

select Person.pname 至少爱着两个女人的男人
from love,Person
group by love.pid,Person.pname,Person.pid
having count(love.pid)>1
and Person.pid=love.pid
and love.pid in(
select Person.pid
from Person
where Person.psex='男')

● 哪个人得到了最多人的爱情？

select Person.pname 得到了最多爱的人
from Person
where Person.pid=(
select top(1) love.lovepid
from love
group by love.lovepid
order by count(*) desc)

● 列出所有人的姓名，以及爱他的人的数量

select Person.pname,
(select count(*) from love where love.pid=Person.pid) 爱他的人的数量
from Person
order by 2 desc

● 列出男人的追求者的平均美貌程度

select p1.pname 被追求者姓名，avg(p2.pbeauty) '平均美貌程度'
from love,Person p1,Person p2
where p1.pid=love.lovepid and p1.psex=’男’and p2.pid=love. pid
group by pl.pnane

● 列出追求者的平均爱情指数最低的男人的姓名,及其追求者的姓名和爱情指数

select p1.pname 追求者的平均爱情指数最低的男人,p2.pname 追求者,love.love_index 追求者的爱情指数
from Person p1,Person p2,love
where p1.pid=love.lovepid
and p2.pid=love.pid and p2.pid in(
select top 1 love.lovepid
from love,Person
where Person.psex='男'
and love.lovepid=Person.pid
group by love.lovepid
order by avg(love.love_index)
)