主页 / dba / 问题 / 209481
Accepted
Thunder Rabbit
Asked: 2018-06-13 17:56:41 +0800 CST

获取 N 个最近的辅助记录匹配条件的主记录

  • 772

我想要一份作者列表（主要记录），其最近的 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'),

    ...

对于以上数据，我只想要作者“Michael Grist”，他最近的两本书是畅销书。

我已经能够找到连续畅销书的作者，但不是最近两本书是畅销书的作者。

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';

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

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

mysql max

3 个回答

  1. Best Answer
    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基于true,true9 个字符，这足以捕捉前两个字符。
    • 我假设best_seller是一个VARCHAR.
    • ORDER BY published_date DESC- 最新的”
    • HAVING-- 不能WHERE在这种情况下使用
    • 2

  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

    • 0 
  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
;

    小提琴

    • -1

相关问题