Question

In PostgreSQL i 想一劳永逸地向每个用户发出指示。

This is my query:

SELECT id, useridx, isread, message, date
  FROM messages
 WHERE isread = 1
 GROUP BY useridx
 ORDER BY date DESC

ample data:

------------------------------------------------------
+  id  |  useridx |  isread  |  messsage |  date     +
------------------------------------------------------
   1   |  1       |  0        | Hello    |  2012-01-01    
   2   |  2       |  1        | Hi       |  2012-01-02    
   3   |  3       |  1        | Test     |  2012-01-03    
   4   |  3       |  0        | My Msg   |  2012-01-04    
   5   |  4       |  1        | sadasd   |  2012-01-05    
   6   |  4       |  1        | sdfsdfd  |  2012-01-06    
   7   |  4       |  0        | sdfsdfsd |  2012-01-07    
   8   |  5       |  0        | 5345634  |  2012-01-08
   9   |  6       |  0        | sdfdfsd  |  2012-01-09
   10  |  7       |  0        | sdfsdfsf |  2012-01-10
------------------------------------------------------

Now, what i want to do is fetch this table by grouping them via useridx and order by date.

<>Expected Result:

------------------------------------------------------
+  id  |  useridx |  isread  |  messsage |  date     +
------------------------------------------------------  
   6   |  4       |  1        | sdfsdfd  |  2012-01-06 
   3   |  3       |  1        | Test     |  2012-01-03  
   2   |  2       |  1        | Hi       |  2012-01-02    
------------------------------------------------------

<>可见>

ERROR:  column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

我不想混淆日期。我只想把使用rid子组起来,并按日期分类。

感谢任何帮助/帮助!

http://www.ohchr.org。不能满足我的需要,或者说错了。

www.un.org/Depts/DGACM/index_chinese.htm

Conclusion: For who get the same problem here can read this as an answer. Both @kgrittn s and @mu is too short s answers are correct. I will continue to use both answers and schemas on my project and in time i can understand which one is the best -i guess-. So, pick one of them and continue to your work. You will be just fine.

http://www.un.org。排除一些女胎。请允许我说,我有一栏id,我有6行。因此,与BUT级(BUT)这一结果截然不同。 <>So,使用级别!

Answer 1

请使用rank( 窗户功能,以便在每条<代码>上订购结果。一组,然后在表上总结排名结果:

select id, useridx, isread, message, date
from (
    select id, useridx, isread, message, date,
           rank() over (partition by useridx order by date desc) as r
    from messages
    where isread = 1
) as dt
where r = 1

这将向您提供<代码>id2、3和6的样本。您不妨在<>><>>>>上添加一个次要关键词,以便当您在同一天有<代码>useridx的多重信息时,始终作出选择。

你们至少需要8.4(AFAIK)的窗口功能。

Answer 2

邮局与MySQL不同,没有显示在总分类盘中未汇总的栏目的随机数据。

解决办法在错误的信息中。

ERROR:  column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

指的是,在选择本栏时,你必须按“日期”一栏组别,或使用诸如MIN()或Lex()等总功能。

例:

SELECT MIN(id), useridx, isread, message, MAX(date)
FROM messages WHERE isread = 1 
GROUP BY useridx, isread, message
ORDER BY MAX(date) DESC

Answer 3

另一种选择是使用<代码>SlectT DISTINCT ON(与简单的有很大不同)。 SlectT DISTINCT:

SELECT *
  FROM (SELECT DISTINCT ON (useridx)
            id, useridx, isread, message, date
          FROM messages
          WHERE isread = 1
          ORDER BY useridx, date DESC) x
  ORDER BY date DESC;

在某些情况下,这种规模比其他办法要好。

Answer 4

几年后,但你只能按顺序排列:

SELECT m.id, m.useridx, m.isread, m.message, m.date
FROM (
   SELECT m2.id, m2.useridx, m2.isread, m2.message, m2.date 
   FROM message m2 
   ORDER BY m2.id ASC, m2.date DESC
) m
WHERE isread = 1
GROUP BY useridx

对我来说,这在邮政局9.2处就行了。

Answer 5

你正在汇集成果。

这意味着,使用<代码>3的浏览量不是2行。你们只有一行。但是,你还选择了<条码>id,message,isread。合计增长栏。 PostgreSQL应如何提供这一数据? 是否应当使用<条码>最大程度。否

我认为,你希望获得关于最新信息的数据。询问:

SELECT id, useridx, isread, message, date FROM messages
 WHERE isread = 1 AND (useridx, date) IN
  (SELECT useridx, max(date) FROM messages WHERE isread = 1 GROUP BY useridx);

友情链接