我正在为一个类设计一个简单的微博网站,需要一些关于滚动分页的sql语句的帮助。
我有两张表:User和Follows。
在我的"用户"表中,我有uid、FirstName、LastName、Email和Username。
我有另一个"Follows"表,它有rid、FollowedName和FollowingName。
例如,如果用户名"Alex"后跟用户名"Bob",则将存在一个新行,其中FollowedName="Bob"和FollowingName="Alex"。
对于搜索页面,我允许用户根据受欢迎程度(根据关注者的数量)进行排序。因此,我需要一个SQL语句,它将从"User"中选择所有行,然后根据每个用户在"Follows"表中的条目数量对它们进行排序。
除此之外,我还需要该语句来过滤那些uid位于字符串名称$explodedids中的用户。
我有一切工作,但我不知道把"WHERE uid NOT IN (".$explodedids.")".
放在哪里
这是我的声明,它正确返回,但没有过滤掉$explodedid:
"SELECT Username, FirstName, LastName, Email, COUNT( Followingname ) AS count
FROM (SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname
FROM User AS u LEFT JOIN Follows AS f ON u.Username = f.Followingname)
AS T GROUP BY Username ORDER BY count DESC LIMIT ".$postnumbers
$postnumbers只是我滚动分页的限制数字。我确信我只是把WHERE NOT IN放错了地方,但如果你们能帮我,那就太棒了。
这里有一个符合您要求的select语句:
--Test Data
CREATE TABLE #User ( uid int
, FirstName varchar(50)
, LastName varchar(50)
, Email varchar(50)
, Username varchar(50));
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(1,'MackF','MackL','mack@mack.com','MackUname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(2,'2F','2L','2@mack.com','2Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(3,'3F','3L','3@mack.com','3Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(4,'4F','4L','4@mack.com','4Uname');
CREATE TABLE #Follows( rid int
, FollowedName varchar(50)
, FollowingName varchar(50));
INSERT INTO #Follows VALUES(1,'4Uname','2Uname');
INSERT INTO #Follows VALUES(2,'MackUname','4Uname');
INSERT INTO #Follows VALUES(3,'4Uname','3Uname');
INSERT INTO #Follows VALUES(4,'MackUname','3Uname');
--Query
SELECT u.Username
, u.FirstName
, u.LastName
, u.Email
, COUNT( f.Followingname ) AS followercount
FROM #User u
LEFT JOIN #Follows f
ON u.Username = f.Followingname
WHERE uid NOT IN (2,4)--here is where your WHERE clause goes remove this line to see all records
GROUP BY u.Username
ORDER BY followercount DESC
--Results
USERNAME FIRSTNAME LASTNAME EMAIL FOLLOWERCOUNT
3Uname 3F 3L 3@mack.com 2
MackUname MackF MackL mack@mack.com 0
这里有一个SQL小提琴来尝试这一切。
NB:我把你的专栏改名为";计数";至";followercount";因为CCD_ 2是保留字。
"SELECT Username, FirstName, LastName, Email,
COUNT( Followingname ) AS count
FROM (
SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname
FROM User AS u
LEFT JOIN Follows AS f
ON u.Username = f.Followingname
WHERE u.id NOT IN($explodids)
) AS T
GROUP BY Username
ORDER BY count DESC
LIMIT $postnumbers";
假设$explodids是一个格式为"x,y,z"的字符串