主页 / user-14487

Thunder Rabbit's questions

Martin Hope
Thunder Rabbit
Asked: 2018-06-13 17:56:41 +0800 CST
  • 0

我想要一份作者列表（主要记录），其最近的 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
Martin Hope
Thunder Rabbit
Asked: 2018-05-18 22:51:52 +0800 CST
  • 1

Buildings并且与Facilities它们之间的 id 链接表具有多对多关系。我想选择具有特定设施的建筑物，并在单个列中显示每个建筑物的其他设施。

如果我 SELECT 基于 TABLE ，则使用 GROUP_CONCAT 有效Buildings，

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/10

但如果我还基于设施进行选择，则只会在 GROUP_CONCAT 中返回该设施。

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 AND `facility_id` = 2
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/11

如何返回包含每栋大楼所有设施的结果？

mysql group-concatenation