mysql在where条件下获取数据 - mysql fetch data in where condition

mysql fetch data in where condition

本文关键字：获取数据条件下 where mysql | 更新日期: 2023-09-27

我现在有3个表：

第一个是：member_username

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

二是：member_data

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

三是：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               |
+---------------------------------------------------+

当member_level在SUM member_level.level中较高时，我想查询数据并显示唯一一个uid，其中fid在1,2,3中。

我现在的查询如下，但这个查询也是包括fid 4在内的所有级别的总和，如何在fid 1,2,3中只指定总和？以及如何将1,2,3中member_level.level Where fid的SUM分配给$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;

谢谢。

要筛选fid值为1, 2 or 3的记录，请在WHERE子句中使用IN语句。select语句中的别名totalLevel将为用户提供总级别。

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