mysql - mysql复杂的select语句

Question

我有一个对我来说似乎非常复杂的案例，我目前正在使用 3 个查询和大量的应用程序逻辑来做这件事。我想，可能只需要一个查询就可以了。

我有一张三桌，

1. conversations table 
2. chats table
3. users table 

请看两张图片，以便您可以看到具有实际数据的实体。

conversations table

chats table

请看对话表。在这里，我想获取所有sender=1 or reciever=1预期结果 的行id 3,4,5。现在，我还从这些选定的行返回用户详细信息。

这对我来说是最困难的部分。我不想检索用户详细信息，id 1而是我想要其用户详细信息 id are 2,3 and 4 notice 4 in sender column and 2 3 in reciever column

现在，您知道会话表中的选定行。在第二个表中，con_id is the foreign key of conversation table所以我想检索每个con_id. 在第二张图片中，您可以看到id 2,3,4,5,6hascon_id = 3但是因为我想要最后一张，所以它应该where id = 6类似地选择每张图片的所有最后一行con_id

对于这个漫长的案例，我很抱歉，希望你能解决我的问题。

提前致谢。

编辑 这是为您提供的 sql 表和虚拟数据，以便您可以快速测试粘贴

预期成绩

   id || sender || reciver || id(users id) ||  userName   || id(chats id) || con_id || msg || msg_sender
   3    1          2           2             iamsadek2          6              3      ...      2
   4    1          3           3             sadek3             10             4      ...      3
   5    4          1           4             adek4.             14.            5.     ...      4 








DROP TABLE IF EXISTS `chats`;

CREATE TABLE `chats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `msg_sender` int(10) unsigned DEFAULT NULL,
  `con_id` int(10) unsigned DEFAULT NULL,
  `msg` text,
  `file` varchar(255) DEFAULT NULL,
  `deleted` int(10) unsigned DEFAULT NULL,
  `seen` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `con_id` (`con_id`),
  KEY `msg_sender` (`msg_sender`),
  CONSTRAINT `chats_ibfk_1` FOREIGN KEY (`con_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chats_ibfk_2` FOREIGN KEY (`msg_sender`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `chats` WRITE;
/*!40000 ALTER TABLE `chats` DISABLE KEYS */;

INSERT INTO `chats` (`id`, `msg_sender`, `con_id`, `msg`, `file`, `deleted`, `seen`, `created_at`, `updated_at`)
VALUES
    (2,1,3,'id 1 sending msg to id 2',NULL,NULL,0,'2018-06-26 19:36:06',NULL),
    (4,2,3,'id 2 sending msg to id 1 second msg',NULL,NULL,0,'2018-06-26 19:36:37',NULL),
    (5,1,3,'id 1 sending msg to id 2 msg 3',NULL,NULL,0,'2018-06-26 19:36:42',NULL),
    (6,2,3,'id 2 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:36:46',NULL),
    (7,1,4,'id 1 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:36:49',NULL),
    (8,3,4,'id 3 sending msg to id 1 msg 2am',NULL,NULL,0,'2018-06-26 19:39:44',NULL),
    (9,3,4,'id 3 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:39:55',NULL),
    (10,3,4,'id 3 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:39:57',NULL),
    (11,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:46',NULL),
    (12,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:48',NULL),
    (13,4,5,'id 4 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:40:50',NULL),
    (14,1,5,'id 1 sending msg to id 4 msg 4',NULL,NULL,0,'2018-06-26 19:41:01',NULL),
    (15,4,11,'id 4 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:42:37',NULL),
    (16,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:57',NULL),
    (17,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:59',NULL);

/*!40000 ALTER TABLE `chats` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table conversations
# ------------------------------------------------------------

DROP TABLE IF EXISTS `conversations`;

CREATE TABLE `conversations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender` int(10) unsigned DEFAULT NULL,
  `reciever` int(10) unsigned DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `type` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sender` (`sender`),
  KEY `reciever` (`reciever`),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`sender`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_reciever_foreign` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`),
  CONSTRAINT `conversations_sender_foreign` FOREIGN KEY (`sender`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `conversations` WRITE;
/*!40000 ALTER TABLE `conversations` DISABLE KEYS */;

INSERT INTO `conversations` (`id`, `sender`, `reciever`, `status`, `type`, `created_at`, `updated_at`)
VALUES
    (3,1,2,0,0,'2018-06-26 19:32:35',NULL),
    (4,1,3,0,0,'2018-06-26 19:32:50',NULL),
    (5,4,1,0,0,'2018-06-26 19:33:11',NULL),
    (6,2,3,0,0,'2018-06-26 19:33:22',NULL),
    (11,3,4,0,0,'2018-06-26 19:33:22',NULL);

/*!40000 ALTER TABLE `conversations` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  `profilePic` varchar(255) DEFAULT 'user.png',
  `address` varchar(255) DEFAULT NULL,
  `lat` varchar(255) DEFAULT NULL,
  `lang` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `gender` varchar(11) DEFAULT NULL,
  `userType` varchar(255) DEFAULT 'User',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` varchar(11) DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_username_unique` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;

INSERT INTO `users` (`id`, `firstName`, `lastName`, `profilePic`, `address`, `lat`, `lang`, `ip`, `password`, `email`, `userName`, `gender`, `userType`, `created_at`, `updated_at`, `status`)
VALUES
    (1,'sadek','hossain','user.png',NULL,NULL,NULL,NULL,'$2a$10$ayAJE7BxkgqXPGjVxeD8iu0GVWL6CXJFdLhGcHAN.i39lCqcAR5nS','[e-mail]','iamsadek','male','User','2018-06-26 18:33:16','2018-06-26 18:33:16','active'),
    (2,'sadek2','hossain2','user.png',NULL,NULL,NULL,NULL,'$2a$10$q.LNN48POO9g1INdEC/iTO1CJjGXNBLYZPbHkyRe.oHaZJi9b8GWe','[e-mail]','iamsadek2','male','User','2018-06-26 18:42:53','2018-06-26 18:42:53','active'),
    (3,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[e-mail]','sadek3','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (4,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[e-mail]','sadek4','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (5,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[e-mail]','sadek5','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (6,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[e-mail]','sadek6','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active');

/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

标签： mysqlsql

Answer 1

基本上可以通过一些连接来完成。棘手的部分是chats. 子查询按 对聊天进行分组con_id以获得最大值id。照id原样，aAUTO_INCREMENT的最大值是对话的最后一条消息。idcon_id

SELECT co1.id,
       co1.sender,
       co1.reciever,
       us.id "id(users id)",
       us.username,
       ch1.id "id(chats id)",
       ch1.con_id,
       ch1.msg,
       ch1.msg_sender
       FROM conversations co1
            INNER JOIN users us
                       ON CASE
                            WHEN co1.sender <> 1
                              THEN co1.sender
                            WHEN co1.reciever <> 1
                              THEN co1.reciever
                          END = us.id
            INNER JOIN chats ch1
                       ON ch1.con_id = co1.id
            INNER JOIN (SELECT max(ch2.id) id,
                               ch2.con_id
                               FROM chats ch2
                               GROUP BY ch2.con_id) ch3
                       ON ch3.con_id = ch1.con_id
                          AND ch3.id = ch1.id
       WHERE 1 IN (co1.sender,
                   co1.reciever);

SQL小提琴

与INNER JOIN带有 的子查询GROUP BY不同，相关的子查询将是另一种选择。

SELECT co1.id,
       co1.sender,
       co1.reciever,
       us.id "id(users id)",
       us.username,
       ch1.id "id(chats id)",
       ch1.con_id,
       ch1.msg,
       ch1.msg_sender
       FROM conversations co1
            INNER JOIN users us
                       ON CASE
                            WHEN co1.sender <> 1
                              THEN co1.sender
                            WHEN co1.reciever <> 1
                              THEN co1.reciever
                          END = us.id
            INNER JOIN chats ch1
                       ON ch1.con_id = co1.id
       WHERE 1 IN (co1.sender,
                   co1.reciever)
             AND ch1.id = (SELECT max(ch2.id)
                                  FROM chats ch2
                                  WHERE ch2.con_id = co1.id);

SQL小提琴