问Mysql复杂select语句
EN

Stack Overflow用户

提问于 2018-06-27 06:07:01

回答 2查看 110关注 0票数 1

我有一个案例，对我来说似乎非常复杂，我目前正在用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的用户详细信息

现在，您知道了conversations表中选定的行。在第二个表con_id is the foreign key of conversation table中，我想检索每个con_id的最后一行。在第二张图片中，您可以看到id 2,3,4,5,6有con_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','sadek.hkm@gmail.com','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','sadek2.hkm@gmail.com','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','sadek3@gmail.com','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','sadek4@gmail.com','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','sadek5@gmail.com','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','sadek6@gmail.com','sadek6','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active');

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

mysql

sql

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-06-27 07:11:23

这基本上可以通过一些连接来完成。棘手的部分是给chats的最后一条消息。子查询根据con_id对聊天进行分组，以获得最大id。由于id为AUTO_INCREMENT，因此con_id的最大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 Fiddle

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

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 Fiddle

票数 1

Stack Overflow用户

发布于 2018-06-27 06:35:43

TRy it now

      SELECT id.conversations, sender.conversations, receiver.conversations, id.users, userName.users, id.chats, con_id.chats, msg.chats, msg_sender.chats
  FROM conversations, chats, users
 WHERE (sender.conversations OR receiver.conversations = 1) AND id.conversations=firstname.users 
    GROUP BY id.conversations

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/51051979

复制

相似问题

问Mysql复杂select语句
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql复杂select语句EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql复杂select语句
EN