问题:

I have two table user and follow.I want to write view such that it will fetch all details of perticuler user along with that two extra column as follower count and followee count alias.

eg. user id=11 then all details from user tables plus followcount 1 and followed count1

回答1:

SELECT u.id, 
       u.userid, 
       u.name, 
       u.mobile, 
       (SELECT Count(*) 
        FROM   follow f 
        WHERE  f.followerid = u.userid) AS follower, 
       (SELECT Count(*) 
        FROM   follow f 
        WHERE  f.followeeid = u.userid) AS followee 
FROM   users u

回答2:

You can achieve this is by using JOIN statements in your query:

example of how you can achieve your final result:

CREATE VIEW [Followers] AS 
SELECT a.name, a.email, a.mobile, COUNT(SELECT COUNT(followerID) FROM follow WHERE followerID = a.userid), COUNT(SELECT COUNT(followeeID) FROM follow WHERE followeeID = a.userid) FROM users a INNER JOIN follow b ON b.followerID = a.userid