dropbox1111
2015-01-05 02:56
浏览 48
已采纳

PHP复杂的MySQL查询从不同的表计数

I'm working on a networking/social website and I'm trying to create a function to return the users that are visiting the same page at the moment you visit it.

How can one select the users from the table "USERS" by counting and grouping the user_id from the table "READING"?

Database structure is the following :

USERS

  • user_id
  • username
  • ...

READING

  • id
  • user_id
  • update_timestamp
  • ...

What should be returned is a array like this

user
  - user_id
  - user_name
  - user_first_name
  - ...
user
  - user_id
  - ...

This is the query that I have so far

SELECT r.user_id, u.* FROM reading r, users u GROUP BY r.user_id AS count WHERE u.user_id = r.user_id ORDER BY count DESC

Example tables

USERS

// user_id // user_name // user_email
-------------------------------------
    123    /   mentos   /  m@gmail.com
-------------------------------------
    321    / freshmaker / f@gmail.com
-------------------------------------
    231    /  hubba     /  h@gmail.com

READING

//    id  //  user_id   //  timestamp
--------------------------------------
      1   /    123      /   201501050420
      2   /    321      /   201501050420

Example of what the output should be

-ARRAY
  -user
     - 123
     - mentos
     - m@gmail.com
   -user
     - 321
     - freshmaker
     - f@gmail.com

图片转代码服务由CSDN问答提供 功能建议

我正在建立一个网络/社交网站,我正在尝试创建一个函数来返回那些用户 您访问它时正在访问同一页面。

如何通过从“READING”表中对user_id进行计数和分组来从“USERS”表中选择用户?

数据库结构如下:

USERS

  • USER_ID
  • 用户名
  • ...

    <强>阅读

    • ID
    • USER_ID
    • update_timestamp
    • ...

      应该返回的是这样的数组

        user 
        -  user_id 
        -  user_name 
        -  user_first_name  
        -  ... 
      user 
        -  user_id 
        -  ... 
         
       
       

      这是我到目前为止的查询

      SELECT r.user_id,u。* FROM读取r,用户u GROUP BY r.user_id AS count WHERE u.user_id = r.user_id ORDER BY count DESC < / BLOCKQUOTE>

      实施例 充足的表格

      USERS

        // user_id // user_name // user_email 
       -----------  -------------------------- 
       123 / mentos / m@gmail.com 
       -----------  -------------------------- 
       321 / freshmaker / f@gmail.com 
       -----------  -------------------------- 
       231 / hubba / h@gmail.com 
         
       \  n 

      阅读

        // id // user_id // timestamp 
       --------------------  ------------------ 
       1/123/201501050420 
       2/321/201501050420 
         
       
       

      示例 输出应该是什么

        -ARRAY 
       -user 
        -  123 
        -  mentos 
        -  m@gmail.com 
       -user 
        -  321  
        -  freshmaker 
        -  f@gmail.com 
         
       
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongyue8640 2015-01-05 03:19
    已采纳

    Are you looking for something like this?

    SELECT u.*
      FROM 
    (
      SELECT DISTINCT user_id
        FROM reading
       WHERE update_timestamp >= NOW() - INTERVAL 5 MINUTE -- or any other appropriate time interval
         AND user_id <> 4 -- depending on the logic you might want to exclude the current user itself
    ) q JOIN users u
        ON q.user_id = u.user_id
    

    Here is a SQLFiddle demo

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题