select top 20 total_s.sid,total=Count(*),selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
--這裡改一下total=Count(*),改成 total=(select count(*) from total_s...)
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
--這裡改一下total=Count(*),改成 total=(select count(*) from total_s...)
但是显示的时候有相同记录,十分恼火。哎~~
想加一个唯一的标识。但是不知道怎么加。或者能区分出30至50里是否已经有过那条记录,后面就不显示了。比如total字段跟sid为
sid total
a 1
b 1
c 1
d 1
.......如果有很多条total都是1 那么就有可能在30-50,还有50-70都有显示同一记录
比如 c 1 ....
Into #T
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
and selfmusic.username=userinfo.username
Group By total_s.sid,selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
Order by total DESC我这么建出来是按照total排序的,id字段依然是乱排的。 between也就没有意义了。怎么搞?
id sid total ....75 42 4 ....
1 73 3 ....
51 1119 3 ....
64 1154 3 ....
63 1153 2 ....
61 1150 2 ....
57 1135 2 ....
有没有不建临时表的办法啊?
Into #T
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
and selfmusic.username=userinfo.username
Group By total_s.sid,selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
Order by total DESC
这样得出来的ID会是你上面那样子吗??应该是排序好的才对啊。
我把问题简化一下select top 1 total_s.sid,total=Count(*),selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
and selfmusic.username=userinfo.username
Group By total_s.sid,selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
Order by total DESC
select top 1 total_s.sid,total=Count(*),selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
and selfmusic.username=userinfo.username
and total_s.sid
not in(Select sid from (Select top 2 sid,Count(*) As total from total_s Group By sid Order by total DESC) A)
Group By total_s.sid,selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
Order by total DESC这两条记录提不出来用户名为igex的记录,只能显示 nils 跟 tttsid total username73 4 nils
42 4 igex
19 3 ttt
insert into t1(sid,total,username,music,intro,photo)
select total_s.sid,total=Count(*),selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
from total_s,selfmusic,userinfo
where total_s.sid=selfmusic.id
and selfmusic.username=userinfo.username
Group By total_s.sid,selfmusic.username,selfmusic.music,selfmusic.intro,userinfo.photo
Order by total DESC每次truncate table t1 然后用between
谢谢鱼。放分