获取N个最近的次级记录匹配条件的主记录

Question

我想要一份作者(主要记录)的列表，其中2本最近的书(次要记录)是畅销书。

INSERT INTO Authors
    (`author_id`, `author_name`)
VALUES
    (41, 'Heida Palffrey'),
    (42, 'Michael Grist'),
     ...

INSERT INTO Books
    (`book_id`, `author_id`, `book_title`, `published_date`, `best_seller`)
VALUES
(156, 41, 'Red Heat',      '2007-01-21', 'false'),
(161, 41, 'Far From Home', '2003-12-27', 'true'),
(859, 41, 'Storm Warning', '1992-08-19', 'true'),
(914, 41, 'Dead Heat',     '1981-01-04', 'false'),

(780, 42, 'The Last',      '2017-01-31', 'true'),
(198, 42, 'Mr Ruin',       '2016-01-18', 'true'),
(166, 42, 'Mr. Troop Mom', '1982-06-27', 'false'),
(796, 42, 'Ben Bootin',    '1972-12-01', 'false'),

    ...

对于上述数据，我只想要作者“迈克尔·克里斯特”，他最近的两本书都是畅销书。

我已经找到了连续畅销书的作者，但没有最近两本书是畅销书的作者。

SELECT t.author_id, a.author_name, t.consecutive, t.best_seller, COUNT(1) consecutive_count
FROM (
  SELECT b.* ,
    @r:= CASE WHEN @g = b.`best_seller` THEN @r ELSE @r + 1 END consecutive,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
GROUP BY t.author_id,t.consecutive,t.`best_seller`
HAVING consecutive_count >= 2 AND  t.`best_seller` = 'true';

对于下面的数据，只有Chaddie Dreakin和Michael符合最近两本畅销书的要求。

http://sqlfiddle.com/#!9/8808649/8

Answer 1

SET @@group_concat_max_len = 9;

SELECT  a.author_name
    FROM  
    (
        SELECT  author_id,
                GROUP_CONCAT(best_seller ORDER BY  published_date DESC) AS bs
            FROM  Books
            GROUP BY  author_id
            HAVING  bs = 'true,true' 
    ) AS x
    JOIN  Authors AS a USING(author_id) ;

备注：

• 9基于9个字符的true,true，这足以捕获前两个字符。
• 我假设best_seller是VARCHAR。
• ORDER BY published_date DESC --“最新”
• HAVING --在这种情况下不能使用WHERE

Answer 2

这里有一个对我有用的答案。

SELECT t.author_id, a.author_name, t.consecutive, t.new
FROM (
  SELECT b.* ,
    @n:= CASE WHEN @a = b.`author_id` THEN @n+1  ELSE 1 END new,
    @r:= CASE WHEN @n = 1 THEN 
            CASE WHEN b.`best_seller` = 'true' THEN 1
                 ELSE 0
            END
         ELSE
            CASE WHEN @g = b.`best_seller` THEN @r + 1
             WHEN b.`best_seller` = 'true' THEN 1
            ELSE 0 END
         END consecutive,
    @a:= b.`author_id` a,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date DESC
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
WHERE `consecutive` = 2 AND  `new` = 2;

乐意选择另一个答案，如果有人能显着地清理这个问题。

http://sqlfiddle.com/#!9/8808649/39

Answer 3

select author_name from 
(
select * from 
(                                      -- emulate row_number()
select ta.*,
if(@typex=ta.author_id, 
@rownum:=@rownum+1, 
@rownum:=1+least(0,@typex:=ta.author_id)) rown 
from Books ta, (select @rownum:=1, @typex:='_') zz
order by author_id, published_date DESC
) yy
where rown < 3                          -- get first 2 records
) zz join authors using(author_id)
group by author_id
having sum(best_seller = 'true') = 2    -- check both are best sellers
; 

小提琴