从多个公司的表格中获取最新发布的新闻 [英] Get last published news from table with multiple companies

问题描述

这是我的新闻表:

id  |   company_id  |   published  
1   |   1           |   2014-06-20         
2   |   1           |   2014-06-19         
3   |   2           |   2014-06-12

我想获取每个公司的最后发布的项目.这可以通过 1 个查询实现吗?我试过这样的事情:

I want to get the last published item, for each company. Is this possible with 1 query? I tried something like this:

SELECT DISTINCT(`company_id`), id, published 
FROM `newsitems` 
ORDER BY `published` DESC

这是我想要的结果:

id | published 
1  | 2014-06-20  
3  | 2014-06-12

公司表结构:

id  |   companyname |   status  
1   |   'name1'     |   'active'        
2   |   'name2'     |   'active'         
3   |   'name3'     |   'active'

推荐答案

这里的问题是保留id和published的组合.您的样本数据太简单，看不出区别.这是应该可行但看起来有点难看的东西.可能有更好的东西:-)

The problem here is to reserve the combination of id and published. Your sample data is too simple to see the difference. Here is something that should work but looks a bit ugly. There might be something nicer :-)

SELECT id, published FROM newsitems
WHERE concat(published, "_", id, "_", company_id) IN (
  SELECT max(concat(published, "_", id, "_", company_id))
  FROM newsitems
  GROUP BY company_id
)
ORDER BY id

用这个示例数据试试:

id  |   company_id  |   published
1   |   1           |   2014-06-20
2   |   1           |   2014-06-19
3   |   2           |   2014-06-12
4   |   1           |   2014-06-21
5   |   2           |   2014-06-13
6   |   2           |   2014-06-11

结果应该是:

id | published 
4  | 2014-06-21
5  | 2014-06-13

这就是你告诉我们你想要的.对于这种情况，不需要公司表.要使用公司名称或公司状态执行某些操作，您必须加入表格.

This is what you told us that you want. For this case the company table isn't needed. To do something with company name or company status you have to join the tables.

这篇关于从多个公司的表格中获取最新发布的新闻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！