dongshi1914
2017-11-10 06:39
浏览 39
已采纳

选择Distinct / Unique但返回所有列

I have a script setup to report a bunch of data periodically, such as IP address, and the page a visitor is on.

I need my backend script to display the latest page that an IP address has visited.

My database is setup like this:

enter image description here

I want to be able to use all of the columns, such as "browseid" and "page". I want to retrieve the last value only. To give you a frame of reference, using the example data above, I'd only want to retrieve record 60, because it's the last page that the IP xxx.xx.xxx.xx visited.

I've been trying to find a solution that works for the last hour, but I just can't get one to work. I'm also trying to select data within a certain timeframe. Here's what I've been using to check for that:

WHERE ts >= '" . strtotime("-15 minutes") . "' AND expiry <= '" . time() . "'

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

我有一个脚本设置来定期报告一堆数据,例如IP地址,访问者的页面 on。

我需要后端脚本来显示IP地址访问过的最新页面。

我的数据库设置如下:

我希望能够使用所有列,例如”browseid“和 “页”。 我想只检索最后一个值。 为了给你一个参考框架,使用上面的示例数据,我只想检索记录 60 ,因为它是IP xxx.xx.xxx.xx <的最后一页 / strong>已访问。

我一直在努力寻找一种适用于最后一小时的解决方案,但我无法让它工作。 我也试图在特定时间范围内选择数据。 以下是我用来检查的内容:

  WHERE ts&gt; ='“。strtotime(” -  15分钟“)。”'AND expiry&lt; =  '“。time()。”'
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dtnnpt11795 2017-11-10 06:44
    已采纳

    You could use a join on the max id groped by ip eg:

     select * from my_table m 
     inner join  (
        select clientip, max(browseid) as m_id 
        from my_table
        group by clientip
     ) t on t.clientip = m.clientip and t.m_id = m.browseid
    
    打赏 评论

相关推荐 更多相似问题