Mysql group_by语句的逆向

Question

我有以下架构:

这个概念是这样的:我们有卖家和买家。卖家创建任务，买家发起聊天并开始对话。因此，卖家可以针对一项任务与不同的买家进行多次聊天(每个买家一次聊天，买家只能发起一次聊天，但他可以发送多条消息)并且一次聊天将包含多条消息。该消息具有作为卖家或买家的 author_id。

所以我想找到两件事(两个查询):一是我想找到所有没有 author_id 等于 tasks.seller_id 消息的任务(或者根本没有消息)。

其次，我想找到相反的任务，其中至少有一条消息的 author_id 等于 seller_id。第二个是以下查询(感谢@bill-karwin):

SELECT t.id, COUNT(*) AS sellerMessages 
FROM tasks AS t
INNER JOIN chats AS c ON c.task_id = t.id
INNER JOIN messages AS m ON m.chat_id = c.id
    AND t.seller_id = m.author_id
GROUP BY t.id
HAVING sellerMessages > 0
ORDER BY t.id;

如何找到相反的位置？

更新

我已经粘贴了一个带有记录的 sql 脚本来测试:http://pastebin.com/KmMJjQsR它创建 60 条不同的任务记录及其消息和聊天关联。

上面的 sql 查询返回 31 个任务，它是正确的。我需要找到上面的反向，它将返回其他 29 个任务。

创建脚本

DROP TABLE IF EXISTS `tasks`;

CREATE TABLE `tasks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`seller_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_tasks_on_seller_id` (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tasks` (`id`, `seller_id`)
VALUES
  (15788, 95157),
  (15789, 95158),
  (15790, 86270),
  (15791, 86270),
  (15792, 94260),
  (15793, 14354),
  (15794, 95160),
  (15795, 95161),
  (15796, 10786),
  (15797, 95162),
  (15798, 72740),
  (15799, 95163),
  (15800, 93159),
  (15801, 95165),
  (15802, 95168),
  (15803, 95170),
  (15804, 95173),
  (15805, 6983),
  (15806, 95175),
  (15807, 95177),
  (15808, 80037),
  (15809, 45066),
  (15810, 95180),
  (15811, 95181),
  (15812, 95182),
  (15813, 84020),
  (15814, 95156),
  (15815, 93418),
  (15816, 74020),
  (15817, 74020),
  (15818, 73604),
  (15819, 95190),
  (15820, 95188),
  (15821, 39132),
  (15822, 95191),
  (15823, 95192),
  (15824, 95196),
  (15825, 32979),
  (15826, 30104),
  (15827, 95198),
  (15828, 95200),
  (15829, 93974),
  (15830, 95201),
  (15831, 95202),
  (15832, 33487),
  (15833, 60076),
  (15834, 33487),
  (15835, 88081),
  (15836, 95204),
  (15837, 95205),
  (15838, 27527),
  (15839, 93055),
  (15840, 95209),
  (15841, 2297),
  (15842, 95211),
  (15843, 76806),
  (15844, 69400),
  (15845, 34273),
  (15846, 95214),
  (15847, 82877);

DROP TABLE IF EXISTS `chats`;

CREATE TABLE `chats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`creator_id` int(11) DEFAULT NULL,
`task_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `chats` (`id`, `creator_id`, `task_id`)
VALUES
  (10754, 53058, 15788),
  (10767, 36414, 15788),
  (10778, 61359, 15788),
  (10759, 61359, 15789),
  (10770, 36414, 15789),
  (10961, 10541, 15790),
  (10777, 61359, 15792),
  (10819, 11835, 15793),
  (10766, 4631, 15795),
  (10791, 18461, 15795),
  (10823, 31111, 15795),
  (10874, 11649, 15795),
  (10994, 85902, 15795),
  (10769, 36414, 15797),
  (10776, 61359, 15797),
  (10919, 53058, 15797),
  (10771, 12815, 15798),
  (10775, 83339, 15798),
  (10811, 12745, 15799),
  (11211, 35416, 15799),
  (10793, 4631, 15800),
  (10833, 85902, 15800),
  (10816, 36414, 15802),
  (10806, 36414, 15803),
  (10801, 80049, 15804),
  (10818, 36414, 15804),
  (10824, 11835, 15805),
  (10802, 7240, 15806),
  (10822, 39871, 15806),
  (10838, 79168, 15806),
  (10817, 36414, 15807),
  (10918, 53058, 15807),
  (10809, 33051, 15808),
  (10843, 20435, 15808),
  (10803, 45069, 15809),
  (10804, 5093, 15809),
  (10814, 84699, 15810),
  (10844, 58098, 15810),
  (10847, 95206, 15810),
  (10849, 95105, 15810),
  (10853, 94009, 15810),
  (10854, 94009, 15810),
  (10855, 94009, 15810),
  (10856, 94009, 15810),
  (10857, 94009, 15810),
  (10858, 94009, 15810),
  (10859, 94009, 15810),
  (10860, 94009, 15810),
  (10894, 65435, 15814),
  (11057, 91171, 15815),
  (10975, 85902, 15816),
  (10812, 4631, 15817),
  (10841, 85115, 15818),
  (10903, 87971, 15818),
  (10980, 36414, 15819),
  (10886, 39393, 15821),
  (10825, 11835, 15824),
  (10871, 2919, 15824),
  (11194, 21322, 15824),
  (11137, 94553, 15825),
  (10831, 84932, 15826),
  (10836, 83339, 15826),
  (10839, 8532, 15826),
  (10840, 11967, 15826),
  (10913, 35573, 15827),
  (10983, 36414, 15827),
  (10878, 29425, 15829),
  (10952, 36414, 15831),
  (10845, 10511, 15832),
  (10941, 7240, 15832),
  (10872, 3154, 15834),
  (10897, 36414, 15835),
  (10917, 53058, 15835),
  (11045, 36902, 15835),
  (10879, 29425, 15836),
  (11114, 94682, 15836),
  (10900, 36414, 15837),
  (10846, 4631, 15838),
  (10850, 4631, 15839),
  (10848, 4631, 15841),
  (10852, 4631, 15842),
  (10851, 4631, 15844),
  (10864, 62822, 15845),
  (10889, 85115, 15847);

DROP TABLE IF EXISTS `messages`;

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`chat_id` int(11) DEFAULT NULL,
`author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `messages` (`chat_id`, `author_id`)
values
  (10754, 53058),
  (10767, 36414),
  (10778, 61359),
  (10759, 61359),
  (10759, 95158),
  (10770, 36414),
  (10770, 95158),
  (10961, 10541),
  (10961, 86270),
  (10777, 61359),
  (10819, 11835),
  (10819, 14354),
  (10766, 4631),
  (10766, 95161),
  (10791, 95161),
  (10791, 18461),
  (10823, 31111),
  (10874, 11649),
  (10994, 85902),
  (10769, 36414),
  (10776, 61359),
  (10919, 53058),
  (10775, 83339),
  (10775, 72740),
  (10811, 12745),
  (10811, 95163),
  (11211, 35416),
  (11211, 95163),
  (10793, 4631),
  (10793, 93159),
  (10833, 85902),
  (10833, 93159),
  (10816, 36414),
  (10806, 36414),
  (10801, 80049),
  (10818, 36414),
  (10824, 11835),
  (10824, 6983),
  (10802, 7240),
  (10802, 95175),
  (10822, 39871),
  (10838, 79168),
  (10838, 95175),
  (10817, 36414),
  (10817, 95177),
  (10918, 53058),
  (10809, 33051),
  (10809, 80037),
  (10843, 20435),
  (10843, 80037),
  (10803, 45069),
  (10804, 5093),
  (10814, 84699),
  (10814, 95180),
  (10844, 58098),
  (10844, 95180),
  (10847, 95206),
  (10847, 95180),
  (10849, 95105),
  (10849, 95180),
  (10853, 94009),
  (10853, 95180),
  (10894, 65435),
  (10894, 95156),
  (11057, 91171),
  (10975, 85902),
  (10975, 74020),
  (10812, 4631),
  (10812, 74020),
  (10841, 85115),
  (10841, 73604),
  (10903, 87971),
  (10903, 73604),
  (10980, 36414),
  (10980, 95190),
  (10886, 39393),
  (10886, 39132),
  (10825, 11835),
  (10825, 95196),
  (10871, 2919),
  (10871, 95196),
  (11194, 21322),
  (11194, 95196),
  (11137, 94553),
  (10831, 84932),
  (10836, 83339),
  (10839, 8532),
  (10839, 30104),
  (10840, 11967),
  (10913, 35573),
  (10913, 95198),
  (10983, 36414),
  (10878, 29425),
  (10878, 93974),
  (10952, 36414),
  (10845, 33487),
  (10845, 10511),
  (10941, 7240),
  (10872, 3154),
  (10872, 33487),
  (10897, 36414),
  (10897, 88081),
  (10917, 53058),
  (11045, 36902),
  (11045, 88081),
  (10879, 29425),
  (10879, 95204),
  (11114, 94682),
  (10900, 36414),
  (10900, 95205),
  (10846, 4631),
  (10846, 27527),
  (10850, 4631),
  (10850, 93055),
  (10848, 4631),
  (10852, 4631),
  (10852, 95211),
  (10851, 4631),
  (10851, 69400),
  (10864, 62822),
  (10889, 85115);

结果:

在一个查询中(消息中包含 sender_id 的查询)应该返回 31 或工作 ID

 15789,15790,15793,15795,15798,15799,15800,
 15805,15806,15807,‌​15808,15810,15814,15‌​816,
 15817,15818,1581‌​9,15821,15824,15826,‌​15827,
 15829,15832,15‌​834,15835,15836,1583‌​7,15838,
 15839,15842,‌​15844 

在另一个查询中，结果应该是

 15841,15796,15825,15845,15809,15833,15843,
 15847,15813,15791,‌​15815,15792,15788,15‌​794,
 15797,15801,1580‌​2,15803,15804,15811,‌​15812,
 15820,15822,15‌​823,15828,15830,1583‌​1,15840,15846 

或29。

Answer 1

I want to find ... tasks with at least one message with author_id equals to seller_id.

您在问题中的查询可以稍微简化一下。不需要显式的 HAVING 过滤器，因为 INNER JOINs 可以做到。如果不需要消息计数，可以省略 COUNT。

SELECT t.id
FROM tasks AS t
INNER JOIN chats AS c ON c.task_id = t.id
INNER JOIN messages AS m 
    ON  m.chat_id = c.id
    AND t.seller_id = m.author_id
GROUP BY t.id
ORDER BY t.id;

I want to find all the tasks which don't have messages with author_id equal to tasks.seller_id (or don't have messages at all).

获取顶级查询的逆向的一种非常直接的方法是将其用作 NOT IN 的子查询:

SELECT tasks.id
FROM tasks
WHERE
  tasks.id NOT IN
  (
    SELECT t.id
    FROM tasks AS t
    INNER JOIN chats AS c ON c.task_id = t.id
    INNER JOIN messages AS m 
        ON  m.chat_id = c.id
        AND t.seller_id = m.author_id
  )
;

Here is SQL Fiddle .