drep94225
2013-04-21 12:42
浏览 54
已采纳

如何在sql中使用order by和group by?

Here is my problem: I have these tables:

felado  fogado  fehide  fohide  datum   olvasott    tartalom

felado = sender, fogado = receiver,

I would like to get all where felado or fogado is me and group the lines. So If there are the next lines

sender 1 receiver 2 id 1
sender 1 receiver 2 id 2
sender 1 receiver 2 id 3
sender 2 receiver 1 id 4

I only would like to get one from these lines, the last by ID.

Now I'm trying like this:

SELECT * 
FROM belso_levelezes 
WHERE (felado="'.$_SESSION["userData"]["id"].'"
   or fogado="'.$_SESSION["userData"]["id"].'" )
ORDER BY id DESC 
GROUP BY felado, fogado

It gaves me only one from all, but not the last records. How can I get the last records from each group? I want to get only this:

sender 2 receiver 1 id 4

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

这是我的问题: 我有这些表:

  felado fogado fehide fohide datum olvasott tartalom 
   
 
 

felado = sender,fogado = receiver,

我想得到所有 felado或fogado是我的地方,并将线条分组。 所以如果有下一行

 发送者1接收者2 id 1 
sender 1接收者2 id 2 
sender 1接收者2 id 3 
sender 2接收者1 id 4 
    
 
 

我只想从这些行中获取一个,最后一个是ID。

现在我正在尝试这样:

  SELECT * 
FROM belso_levelezes 
WHERE(felado =“'。$ _ SESSION [”  userData“] [”id“]。'”
或者fogado =“'。$ _ SESSION [”userData“] [”id“]。'”)
ORDER BY id DESC 
GROUP BY felado,fogado 
   
 
 

它只给我一个,但不是最后的记录。 如何从每个组中获取最后的记录? 我想只得到这个:

  sender 2 receiver 1 id 4 
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanqiao3608 2013-04-21 13:09
    已采纳

    You could use a subquery to create two columns that are unique per conversation. You can then group on those columns, and grab the maximum id for the conversation. The example assumes your user id is 42:

    SELECT  sr1
    ,       sr2
    ,       max(id)
    FROM    (
            SELECT  case when Sender < Receiver then Sender else Receiver end as sr1
            ,       case when Sender > Receiver then Sender else Receiver end as sr2
            ,       id
            FROM    YourTable
            WHERE   42 in (Sender, Receiver)
            ) as SubQueryAlias
    GROUP BY
            sr1
    ,       sr2
    
    已采纳该答案
    打赏 评论
  • doulun5683 2013-04-21 13:10
    'SELECT MAX(id),felado,fegado,... FROM belso_levelezes WHERE (felado="'.$_SESSION["userData"]["id"].'" or fogado="'.$_SESSION["userData"]["id"].'" ) GROUP BY felado, fogado '
    
    打赏 评论

相关推荐 更多相似问题