One table, three column MYSQL query question

In reference to my previous question: One table, two column MYSQL query challenge

The command: "SELECT hostname, GROUP_CONCAT(DISTINCT name) as name FROM comments GROUP BY hostname"

returns the expected results:

192.168.0.1 user1,user2

192.168.3.5 user3

The same table "comments" also has a "timestamp" field. How do I include the latest (most current) timestamp for each user using that ip?

Example:

192.168.0.1 user1-1277146500,user2-1277250087

192.168.3.5 user3-1237150048

I tried a number of variations of the command, but could only get all the timestamps, not the most current one...

also.. if possible it would be nice to convert the epoch timestamp to something more friendly before displaying it.

---

SELECT 
     hostname, 
     GROUP_CONCAT(CONCAT_WS("-", name, m_timestamp))
FROM
     (SELECT 
          hostname, 
          name,
          cast(max(timestamp) as char(24)) as m_timestamp 
     FROM 
          comments 
     GROUP BY 
          hostname,
          name) as A
GROUP BY 
     hostname

Edit

Updated column name to timestamp (was c_timestamp) per OP's comments

---

Concat the varchar converted Max(timestamp) to the name and add timestamp in the Group by clause

SELECT hostname,
  GROUP_CONCAT(DISTINCT name, CAST(MAX('timestamp') AS VARCHAR) as name 
FROM comments GROUP BY hostname

Think it will work, though I have not tested this.

---

MAX(`timestamp`)

链接地址: http://www.djcxy.com/p/5166.html

上一篇: 我如何从我的mysql数据库表“userlog”获得高峰时间

下一篇: 一个表，三列MYSQL查询问题