mysql在where条件中获取数据

Question

I have 3 table now:

First is : member_username

+-------------+------------------+
| uid         | username         |
+-------------+------------------+
| 1           | theone           |
| 2           | ohno             |
| 3           | prayforpr        |
+-------------+------------------+

Second is : member_data

+-------------+-------------------+-----------------+
| uid         | talk              | etc             |
+-------------+-------------------+-----------------+
| 1           | talk1             |                 |
| 2           | talkeee           |                 |
| 3           | iojdfnl           |                 |
+---------------------------------------------------+

Third is : member_level

+-------------+-------------------+-----------------+
| uid         | level             | fid             |
+-------------+-------------------+-----------------+
| 1           | 2                 | 1               |
| 1           | 10                | 2               |
| 2           | 1                 | 1               |
| 2           | 99                | 2               |
| 1           | 40                | 3               |
| 3           | 50                | 1               |
| 1           | 44                | 4               |
+---------------------------------------------------+

I would like to query data and display the only one uid when member_level is higher in when SUM member_level.level Where fid in 1,2,3.

my query now is like below, but this query is sum all the level including fid 4 also, how to specify only sum in fid 1,2,3? and how do I assign the SUM of member_level.level Where fid in 1,2,3 to $levelKingTotalLevel?

$levelKing = DB::query("SELECT t1.uid,t1.username,t2.talk FROM ".DB::table('member_level')." t3 JOIN ".DB::table('member_username')." t1 ON(t3.uid = t1.uid) JOIN ".DB::table('member_data')." t2 ON (t1.uid = t2.uid) GROUP BY t3.uid ORDER BY SUM(t3.level) DESC LIMIT 1");

while($rowlevelKing = DB::fetch($levelKing)) {
    $levelKingTotalLevel = $rowlevelKing['???'];
    $levelKingN = $rowlevelKing['username'];
    $levelKingUID = $rowlevelKing['uid'];
    $levelKingT = $rowlevelKing['talk'];
};
echo "The ".$levelKingN." total level is ".$levelKingTotalLevel." and he talk about ".$levelKingT;

Thank you.

Answer 1

To filter records having fid values as 1, 2 or 3, use IN statement in WHERE clause. Alias totalLevel in select statement will give you total level for a user.

SELECT t1.uid, t1.username, t2.talk, SUM(t3.level) AS totalLevel
FROM member_level t3
JOIN member_username t1
ON (t3.uid = t1.uid)
JOIN member_data t2
ON (t1.uid = t2.uid)
WHERE t3.fid IN (1,2,3)
GROUP BY t3.uid
ORDER BY totalLevel DESC
LIMIT 1