从子表中为N个MySQL视图中的每个父记录获取N条记录 [英] Get N number of records from child table for each parent record in a MySQL View

问题描述

我尝试在SO中找到此问题的答案，但找不到任何答案.任何链接都会有很大帮助.

I tried finding answer to this question in SO , but could not find any. Any links will be of great help.

我有一个父表和一个子表，在父表和子表之间有一对多的关系.子表包含大约一百万条记录，我想在每个父记录的子表中创建一个包含前10条记录的视图. 例子-

I have a parent table and a child table with one to many relationship between the parent and child table. The child table contains around 1 million records and I want to create a view with 1st 10 records in child table for each parent record. Example-

Parent_Table - Fields -- ID, Name
ID       Name
----     -----
1        A
2        B
3        C

Child_Table - Fields -- ID, ParentID, Date, Data
ID  ParentID  Date    Data
--------------------------
1     1        04/10   A1
2     1        04/11   A2
3     1        04/11   A3
4     1        04/12   A4
5     1        04/12   A5
6     2        04/10   B1
7     2        04/11   B2
8     2        04/12   B3
9     2        04/12   B4
10    2        04/13   B5
11    2        04/13   B6

现在，我想为每个父记录按日期排序的第1个4条记录创建一个视图.

Now, I want to create a view with 1st 4 records for each parent record sorted by date.

预期输出

ID  ParentID  Date    Data
--------------------------
1     1        04/10   A1
2     1        04/11   A2
3     1        04/11   A3
4     1        04/12   A4
6     2        04/10   B1
7     2        04/11   B2
8     2        04/12   B3
9     2        04/12   B4

任何链接或解决方案指南将不胜感激.预先感谢！

Any links or guide to the solution will be appreciated. Thanks in advance!

如果您需要任何澄清，请发表评论.

In case you need any clarification, please post a comment.

推荐答案

如果您需要创建一个VIEW，则可以使用以下内容:

If you need to create a VIEW, you could use something like this:

CREATE VIEW First_Four AS
SELECT c1.*
FROM
  Child_Table c1 LEFT JOIN Child_Table c2
  ON c1.ParentID = c2.ParentID
     AND (STR_TO_DATE(c1.`date`, '%m/%Y')>STR_TO_DATE(c2.`date`, '%m/%Y')
          OR (STR_TO_DATE(c1.`date`, '%m/%Y')=STR_TO_DATE(c2.`date`, '%m/%Y')
              AND c1.ID>c2.ID)
         ) 
GROUP BY
  c1.ID, c1.ParentID, c1.`Date`, c1.Data
HAVING
  COUNT(c2.ID)<4

我将字段数据视为VARCHAR列，因此我们需要使用STR_TO_DATE，否则，我们可以直接将c1.date与c2.date进行比较.

I'm considering the field data as a VARCHAR column, so we need to use STR_TO_DATE, if it is not we can just compare c1.date with c2.date directly.

请在此处看到小提琴.

这篇关于从子表中为N个MySQL视图中的每个父记录获取N条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！