在模拟的Facebook SQL数据库中查询返回2行和1行2列的特定用户的性别和位置？

Question

如何：

查询此项可返回居住在加拿大的男性朋友数，以及给定用户的所有女性朋友数：

A( 将返回2行的查询——每个数字一行，以及

B( 返回1行2列的查询？

表1:Profile(userID、profile_URL、FirstName、LastName、Gender、Current_Location、Birthdate、AboutMe(

表2:Location(userID、city、country、state、zipcode(

表3:Page(pageID、pageURL、pageName、likedBy_userID(

表4:FriendRelation(userID_1、userID_2、AreFriends(

表5:user_status(userID、statusID、status、URL、timestamp(

Answer 1

这样做是可能的：A:

declare @user varchar(100) -- given user
set @user = 'user1' --Replace by your userid 
select count(*) Nbr
from  
(
select r.userid_2 from Profile p inner join FriendRelation r
on p.userid=r.userid_1 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_2
where p.userid=@user and loc.city='CANADA' and gender='Male' 
union
select r.userid_1
from  Profile p inner join FriendRelation r
on p.userid=r.userid_2 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_1
where p.userid=@user and loc.city='CANADA' and gender='Male' 
) NbrMale
union
select count(*) Nbr
from  
(
select r.userid_2 from Profile p inner join FriendRelation r
on p.userid=r.userid_1 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_2
where p.userid=@user and gender='Female' 
union
select r.userid_1
from  Profile p inner join FriendRelation r
on p.userid=r.userid_2 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_1
where p.userid=@user and gender='Female' 
) NbrFemale

B:

select * from (
declare @user varchar(100) -- given user
set @user = 'user1' --Replace by your userid 
select count(*) NbrMale
from  
(
select r.userid_2 from Profile p inner join FriendRelation r
on p.userid=r.userid_1 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_2
where p.userid=@user and loc.city='CANADA' and gender='Male' 
union
select r.userid_1
from  Profile p inner join FriendRelation r
on p.userid=r.userid_2 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_1
where p.userid=@user and loc.city='CANADA' and gender='Male' 
) NbrMale
union
select count(*) NbrFemale
from  
(
select r.userid_2 from Profile p inner join FriendRelation r
on p.userid=r.userid_1 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_2
where p.userid=@user and gender='Female' 
union
select r.userid_1
from  Profile p inner join FriendRelation r
on p.userid=r.userid_2 and areFriends='true'  --OR 0/1
inner join location loc on loc.userid=r.userid_1
where p.userid=@user and gender='Female' 
) NbrFemale) CountGend