php - SQL 查询有问题。需要区分我的结果

Question

我得到了我的对话表格，我需要将所有对话列为主题。因此，如果我是发件人或收件人，它应该将其列为一个对话。所以在 2 个用户之间，他们获得哪个角色并不重要。

---

所以如果我得到:

1, me, mom, "hello mom, I'm good. How are you?", 1, 23/09-2011
2, mom, me, "hello son, how are you?", 1, 22/09-2011
3, me, dad, "hello dad, how are you?", 1, 20/09-2011

---

我想这样显示:

between You and Mom - Hello mom... - 23/09-2011 - 2 messeges
between You and Dad - Hello dad... - 20/09-2011 - 1 message

---

我似乎无法理解查询。可能有 DISTINCT 的东西。

我希望你们能帮助我:)

更新

CREATE TABLE IF NOT EXISTS `fisk_beskeder` (
  `id` int(11) NOT NULL auto_increment,
  `fra` int(11) NOT NULL,
  `til` int(11) NOT NULL,
  `dato` varchar(50) NOT NULL,
  `set` int(11) NOT NULL default '0',
  `besked` text NOT NULL,
  `svar` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

PHP

$sql = mysql_query(
    "SELECT `fra`, `til`, `besked`, `dato`, `set`, `id` 
    FROM fisk_beskeder 
    WHERE til = ".$userid." 
       OR fra = ".$userid." 
    ORDER BY id DESC") 
or die(mysql_error());

更新

好的，现在可以了。我唯一需要做的就是从组中获取最新数据。

SELECT count(id), `id`, `fra`, if(a.fra = $cfg_brugerid, a.til, a.fra) AS other, `til`, `besked`, `dato`, `set` 
FROM fisk_beskeder a
WHERE fra = $cfg_brugerid OR til = $cfg_brugerid
GROUP BY other
ORDER BY a.id DESC

Answer 1

也许是这样的:

SELECT COUNT(msg), msg, a.date,  if(a.from ='me', a.to , a.from) as otherPerson
FROM table a  
WHERE a.from = 'me' or a.to = 'me'
GROUP BY otherPerson