按两列列出表

I have two tables each with two columns

Table 1: UserID, UserName
Table 2: UserID, rate

The rate column can take value A or B.

Is it possible to list usernames ordered by (num of As) - (num of Bs)

Basically each UserID can have several number of As and Bs, I want to list the UserID in terms of (num of As minus num of Bs)

Try using this:

SELECT t1.UserName, 
    (SUM(IF(t2.rate = 'A'), 1, 0) - SUM(IF(t2.rate = 'B'), 1, 0)) as temp 
FROM table1 t1 INNER JOIN table2 t2 
ON t1.UserID = t2.UserID 
GROUP BY t1.UserID 
ORDER BY temp;

I think that this is the query you're looking for:

SELECT T1.UserID
      ,COUNT(T2A.rate) - COUNT(T2B.rate) AS [rateDiff]
FROM Table1 T1
LEFT OUTER JOIN Table2 T2A ON T2A.UserID = T1.UserID
                            AND T2A.rate = 'A'
LEFT OUTER JOIN Table2 T2B ON T2B.UserID = T1.UserID
                            AND T2B.rate = 'B'
GROUP BY T1.UserID
ORDER BY COUNT(T2A.rate) - COUNT(T2B.rate)

Hope this will help, tell me if the result is not the one you expect.