一条SQL语句,请教各位了~

Question

三个表
user(userid,tname,uname) PrimaryKey:userid;
base(userid,nickname,sex,birth,...) foreignKey:userid;
album(Picid,userid,click,...) PrimaryKey:Picid;ForeignKey:userid;要用语句选择出
1.前50个按照album的click降序排序的album.userid无重复的结果
2.输出中要有user里对应的tname,要有base表里的基本信息;

Answer 1

select distinct top 50 b.userid,b.nickname,b.sex,b.birth,u.tname,u.uname from user u,base b,album a where u.userid=b.userid and b.userid=a.userid order by a.click
不知道是不是你要的

Answer 2

楼上的正解,稍修改一下楼上的:
select distinct top 50 b.userid,
                       b.nickname,
                       b.sex,
                       b.birth,
                       u.tname,
                       u.uname 
from user u,base b,album a 
where u.userid=b.userid and b.userid=a.userid 
order by a.click desc (降序需要加desc关键字)

Answer 3

select distinct top 50 b.userid,
                       b.nickname,
                       b.sex,
                       b.birth,
                       u.tname,
                       u.uname
from
  album a,
  user u,
  base b,
where
  u.userid=a.userid 
  and b.userid=a.userid
  order by a.click desc

Answer 4

select distinct top 50 b.userid,
                       b.nickname,
                       b.sex,
                       b.birth,
                       u.tname,
                       u.uname
from
  album a,
  user u,
  base b,
where
  u.userid=a.userid 
  and b.userid=a.userid
  order by a.click desc

Answer 5

随便凑了下　不知道对不对．
select distinct top 50 base,.userid,
                       base,.nickname,
                       base,.sex,
                       base,.birth,
                       user,.tname,
                       user,.uname
from
  album,
  user,
  base,
where
  user,.userid=album .userid 
  and base,.userid=album .userid
  order by album .click desc

Answer 6

用这句的结果
select distinct top 50 b.user_id,
                       b.nickname,
                       b.sex,
                       u.username,
                       a.pic_click
from
  match_album a,
  [user] u,
  match_base b
where
  u.userid=a.user_id 
  and b.user_id=a.user_id
  order by a.pic_click desc
User_ID NickName Sex UserName Pic_Click
49 asdfasdf 0 rainbow 5
49 asdfasdf 0 rainbow 4
48 asdfasdf 0 nextcn 0
49 asdfasdf 0 rainbow 0之前我也试过这样了.但User_ID还是有重复呀,我理想结果应该是这样
User_ID NickName Sex UserName Pic_Click
49 asdfasdf 0 rainbow 5
48 asdfasdf 0 nextcn 0

Answer 7

不知对不对，试试这个
select distinct top 50 b.userid,
                       b.nickname,
                       b.sex,
                       b.birth,
                       u.tname,
                       u.uname
from album a 
  inner join user u on u.userid=a.userid
  inner join base b on b.userid=a.userid
order by a.pic_click desc

Answer 8

TO:Taxion(谁在旁边放屁) 
结果还是这样 49 asdfasdf 0 rainbow 5
49 asdfasdf 0 rainbow 4
48 asdfasdf 0 nextcn 0
49 asdfasdf 0 rainbow 0

Answer 9

select distinct top 50 b.user_id,
                       b.nickname,
                       b.sex,
                       u.username,
     from
  
  user u,
  base b,
(select distinct top 50 user_id from album  order by pic_click desc) c
where
   and b.user_id=a.user_id
and u.userid = c.userid
  

Answer 10

TO: wzs_wzs123(蓝天百云) 
虽然你是输出了 User_ID NickName Sex UserName
49 asdfasdf 0 rainbow
48 asdfasdf 0 nextcn
但是,如果在结果中加入album.pic_id 的话就又不行了

Answer 11

select distinct top 50 b.user_id,
                       b.nickname,
                       b.sex,
                       u.username,
.pic_id 
     from
  album  ,a
  user u,
  base b,
(select distinct top 50 user_id from album  order by pic_click desc) c
where   and b.user_id=a.user_id
and u.userid = c.userid
group by b.user_id,
                       b.nickname,
                       b.sex,
                       u.username

Answer 12

select distinct top 50 b.user_id,
                       b.nickname,
                       b.sex,
                       u.username,
                      min(a.pic_id )
     from
  album  ,a
  user u,
  base b,
(select distinct top 50 user_id from album  order by pic_click desc) c
where   and b.user_id=a.user_id
and u.userid = c.userid
group by b.user_id,
                       b.nickname,
                       b.sex,
                       u.username

Answer 13

其实我想了一下,但GROUP语句不会用
其是我是想从ablum表里以UserID分组,分组后显示每组的信息是该USERID组中的pic_click 的最大值的记录,然后再根据这个,调出该 USERID的信息,我试过DISTINCT这语名了,它是对每行进不不重复处理,因为我要在AUBLM表里至到取得PIC_CLICK最大值和USERID,就这两个列的话,这样如USERID是19，有2张相片，CLICK分别是10和20，distinct 这两个列出来的结果还是
19 20
19 10
所以我想是不是用GROUP BY 语句，但这语句我不太会用，请各位高手指教一下