相反

相反

关注
发信
关注(28)粉丝(399)

mysql - MySQL的group_by语句相反

我有以下架构：
mysql - MySQL的group_by语句相反-LMLPHP
这个概念是这样的：
我们有卖家和买家。卖家创建任务，买家启动聊天并开始对话。因此，卖家可以与不同的买家就一项任务进行多次聊天（每个买家一次聊天，买家可以发起一次聊天，但他可以发送许多消息），一次聊天将有许多消息。该邮件的作者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。

最佳答案

我想找到。。。至少包含一条消息的任务
author_id等于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;

我想找到所有
没有author_id消息的任务等于
tasks.seller_id（或者根本没有消息）。
获取top查询相反的一个非常直接的方法是将其用作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。

关于mysql - MySQL的group_by语句相反，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39375254/

10-11 00:12
Powered by LMLPHP ©2024 相反 0.028601