在mysql查询中分组和排序 [英] group by and order by in mysql query

问题描述

我正在使用php和mysql作为后端的约会网站.我有两张桌子.第一个表是将存储所有用户信息的用户表，另一个表是visituser表，如果一个用户查看另一个用户配置文件，则visituser表中将存在一个条目.每次如果一个用户访问另一个用户配置文件，那么visituser表中都会有一个条目.一个用户访问另一个用户将有多个条目.我的问题是我必须向访问者展示最近一次访问的日期.我的查询如下=>

I am working on dating website with php and mysql as backend. I've two tables. First table is user table where all user info will be stored and other table is visituser table where if one user view another users profile then there will be an entry in visituser table. Every time if one user visits another users profile then there will be an entry in visituser table. There will be multiple entry for one user visit to another user. My problem is that i've to show the visitor one time with latest visit date. My query is given below =>

SELECT
    a.*, b.onlinestatus,
    b.username,
    b.age
FROM
    tbl_visitprofile a
LEFT JOIN tbl_user b ON a.visitorid = b.id
WHERE
    b. STATUS != '2'
AND a.visitmemberid = '10'
AND a.visitorid NOT IN (
    SELECT
        user_id
    FROM
        tbl_pauseusers
)
AND a.blockstatus = '0'
GROUP BY
    a.visitorid
ORDER BY
    a.id DESC
LIMIT 0,
 12

如果我在此查询中未使用分组依据"，那么我将获取最新的访问条目，但是如果我使用分组依据"，则我将不会获得最新的访问条目.我搜索了很多，但没有找到我的答案.任何帮助将不胜感激.预先感谢

If i am not using the group by in this query then i am getting the latest visit entry but if i am using group by then i am not getting the latest visit entry. I'd searched alot but didn't find my annswer anywhere. Any help will be appreciated. Thanks in advance

推荐答案

您可以使用MAX组功能来实现.我已经清理了一下查询，但是从根本上讲，这应该保留与之前相同的逻辑，同时可以进行一些优化.只需将a.date_time更改为表中的日期时间字段即可.

You can achieve this using the MAX group function. I have cleaned up the query a bit but fundamentally this should retain the same logic you had before whilst being slightly more optimisable. Just change a.date_time for whatever the date time field is in your table.

SELECT
    a.visitorid,
    MAX( a.date_time ) AS last_visit_date_time,
    b.onlinestatus,
    b.username,
    b.age,

FROM tbl_visitprofile a

INNER JOIN tbl_user b
    ON b.id = a.visitorid
    AND b.STATUS != '2'

LEFT JOIN tbl_pauseusers p
    ON p.user_id = a.visitorid

WHERE a.visitmemberid = '10'
    AND a.blockstatus = '0'
    AND p.user_id IS NULL

GROUP BY a.visitorid
ORDER BY last_visit_date_time DESC
LIMIT 0 , 12;

这将按上次访问的日期/时间对记录进行排序，并返回最新的12.

This will order the records by the date/time of last visit and return the latest 12.

这篇关于在mysql查询中分组和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！