Question

我有以下功能

function availableChatRoom($topic_id){
$max_rooms = 2;
$max_users = 2;

$sqli = "SELECT COUNT(DISTINCT room_num) AS numRooms FROM forum_chat WHERE topic_id =  $topic_id ";
$num_rooms = queryColumnVal($sqli, "numRooms");

$sqlii = "SELECT room_num, COUNT(user_id) AS numUsers FROM forum_chat 
          WHERE topic_id =  $topic_id  AND (status =  online  AND status =  offline ) AND chat_msg =   
          GROUP BY room_num
          HAVING numUsers < $max_users ORDER BY msg_date";

$num_rooms_with_spaces = mysqlNumRows($sqlii, "room_num");
queryColumnVal($sqlii, "room_num");

if($num_rooms == 0){
    return 1;
时 时
elseif($num_rooms <= $max_rooms){

    if($num_rooms_with_spaces == 0){
        return $num_rooms + 1;
    时 时
    else{
        return queryColumnVal($sqlii, "room_num");
    时 时
时 时
else{
    return "none";
时 时

时时

the idea is for the function to return a chat room number with space. -Every time a user joins the room, a line with no msg is inserted with status online -Every time a user leaves the room a line with no msg is inserted with status offline I am now trying to write a function which checks if there are any spare rooms for each topic The idea is to select the room_num where the number of users who have logged in but not logged out is less than the maximum chatter per room. Can someone help me with my $sqlii.

谢谢谢谢

Answer 1

您的查询中有几个错误 :

(status = online AND status = offline )

这表示您的查询 wil 返回时完全没有。请使用: < code> (status = 在线 OR status = 离线) 或等效的 < code> (status IN (线上, 离线)) 或等效的 < code > (status IN (线上, 离线))

COUNT(user_id)

这不计算线上离线用户与线上离线用户的差别。请更正逻辑。

ORDER BY msg_date

在 GROUP by 查询查询中,尽管 MySQL 允许使用 haining 、 SELECT 或 ORDER 条款,但使用非聚合列是不妥的。除非该栏取决于分组表达式。由于 msg_date 显然不依赖于 room_num ,所以您应该用一个综合功能替换:

ORDER by MAX(msg_date) 或 ORDER by MIN(msg_date) (msg_date)

$sqlii = "
   SELECT room_num, 
          COUNT(CASE WHEN status =  online  THEN 1 END) 
            - COUNT(CASE WHEN status =  offline  THEN 1 END) 
          AS numUsers 
   FROM forum_chat 
   WHERE topic_id =  $topic_id  
     AND status IN ( online ,  offline )
     AND chat_msg =   
   GROUP BY room_num
   HAVING COUNT(CASE WHEN status =  online  THEN 1 END) 
          - COUNT(CASE WHEN status =  offline  THEN 1 END) 
          < $max_users 
   ORDER BY MAX(msg_date)
     ";

Answer 2

HAVING 条款 是在 SELECT 之前进行“强”评估的,因此服务器还不知道该别名。

SELECT room_num, COUNT(user_id) AS numUsers 
FROM forum_chat 
WHERE topic_id =  $topic_id  AND 
      (status =  online  OR status =  offline ) AND 
      chat_msg =   
GROUP BY room_num
HAVING COUNT(user_id) < $max_users ORDER BY msg_date

First the product of all tables in the from clause is formed.
The where clause is then evaluated to eliminate rows that do not satisfy the search_condition.
Next, the rows are grouped using the columns in the group by clause.
Then, Groups that do not satisfy the search_condition in the having clause are eliminated.
Next, the expressions in the select clause target list are evaluated.
If the distinct keyword in present in the select clause, duplicate rows are now eliminated.
The union is taken after each sub-select is evaluated. 8.Finally, the resulting rows are sorted according to the columns specified in the order by clause.

Answer 3

You cannot use the alias of the column in HAVING in the same SELECT level. That is because in yout MySQL ONLY_FULL_GROUP_BY would be activated.

试试这个

HAVING COUNT(user_id) < $max_users ORDER BY msg_date

友情链接