How to get data from two tables group by one table‘s id

Question

I need to solve this problem:

I have a subject table:

+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL                | auto_increment |
| main_title  | varchar(30)  | NO   |     | ""                  |                |
| sub_title   | varchar(30)  | NO   |     | ""                  |                |
| type        | tinyint(4)   | NO   |     | 1                   |                |
| thumbnail   | int(11)      | NO   |     | 0                   |                |
| main_pic    | int(11)      | NO   |     | 0                   |                |
| info_url    | varchar(128) | NO   |     | ""                  |                |
| create_time | timestamp    | NO   |     | CURRENT_TIMESTAMP   |                |
| modify_time | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
| status      | tinyint(4)   | NO   |     | 1                   |                |
+-------------+--------------+------+-----+---------------------+----------------+

A new table:

+---------------+---------------+------+-----+---------------------+----------------+
| Field         | Type          | Null | Key | Default             | Extra          |
+---------------+---------------+------+-----+---------------------+----------------+
| id            | int(11)       | NO   | PRI | NULL                | auto_increment |
| title         | varchar(60)   | NO   |     |                     |                |
| thumbnail     | int(11)       | NO   |     | 0                   |                |
| abstract      | varchar(2048) | NO   |     |                     |                |
| paragraphs    | text          | NO   |     | NULL                |                |
| about_subject | varchar(128)  | NO   |     |                     |                |
| pv            | int(11)       | NO   |     | 0                   |                |
| share_count   | int(11)       | NO   |     | 0                   |                |
| like_count    | int(11)       | NO   |     | 0                   |                |
| comment_count | int(11)       | NO   |     | 0                   |                |
| source        | varchar(64)   | NO   |     |                     |                |
| source_url    | varchar(128)  | NO   | UNI |                     |                |
| signature     | varchar(40)   | NO   |     |                     |                |
| creator_id    | int(11)       | NO   |     | 0                   |                |
| create_time   | timestamp     | NO   |     | CURRENT_TIMESTAMP   |                |
| publish_time  | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| editor_id     | int(11)       | NO   |     | 0                   |                |
| modify_time   | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| status        | tinyint(4)    | NO   | MUL | 0                   |                |
+---------------+---------------+------+-----+---------------------+----------------+

Now, I want to get the top 20 newest news for every subject at one query. I have tried:

SELECT 
concat('3','_',news.id) as id, 3 as target_type, 
news.id as target_id, news.title as target_title, 
news.abstract as target_abstract, news.paragraphs as target_paragraphs, 
news.source as target_source, news.pv as target_pv, 
news.like_count as target_like_count, news.comment_count as target_comment_count, news.publish_time as target_create_time,
subject.id as subject_id, subject.main_title as subject_name 
  FROM
  subject
  LEFT JOIN news ON subject.id = news.about_subject 
  ORDER BY news.publish_time DESC
  -- LIMIT 0 ,20

How can I get the top 20 for each subject and order all the data by subject.id?

Define 'top'. And consider providing proper DDLs (and/or an sqlfiddle) together with the desired result. For that, you might want to pick a number lower than 20 — Strawberry, Aug 15 '15 at 07:31

score 0 · Answer 1 · edited May 23 '17 at 12:09

0

I'd consider a select of a sub-query which would be the union of your two table or views of those tables.

This can be done in MySQL, but it is not as simple as adding a LIMIT clause. Here is an article that explains the problem in detail:

How to select the first/least/max row per group in SQL

It's a good article - he introduces an elegant but naïve solution to the "Top N per group" problem, and then gradually improves on it.

-- DanBen

Full question and article answer: https://stackoverflow.com/a/2129703/3536236

edited May 23 '17 at 12:09

Community

1
1

answered Aug 15 '15 at 07:38

DaveMac

76
10

Sir, I read one aritcle refer in that qustion page, It sovle my problem .Thank you very much . I will paste the answer right now. Tks again! – Will Liu Aug 15 '15 at 13:17

Will Liu · Answer 2 · 2015-08-16T03:44:37.547

How to select the first/least/max row per group in SQLthe aritcle have many ways ,I just choose one .

set @num = 0,@subject_id = '';

SELECT
    concat('3','_',news_inner.id) as id, 3 as target_type, news_inner.id as target_id, 
    news_inner.title as target_title, news_inner.abstract as target_abstract,
    news_inner.paragraphs as target_paragraphs, news_inner.source as target_source, 
    news_inner.pv as target_pv, news_inner.like_count as target_like_count,
    news_inner.comment_count as target_comment_count, news_inner.publish_time as target_create_time
FROM 
(
    select 
        id, about_subject, title, abstract, paragraphs, source, pv, 
        like_count, comment_count, publish_time,
        @num := if(@subject_id = about_subject, @num + 1, 1) as row_number,
        @subject_id := about_subject
    from news  
    order by about_subject DESC,publish_time DESC
) as news_inner
where news_inner.row_number  <= 20

How to get data from two tables group by one table‘s id

2 Answers2