我两天来一直在想这个问题。

首先，让我说，我是一个新的开发，所以这可能是一个简单的查询，但我不能解决它。 我在这个网站上搜索过试图找到一个解决方案，但如果有一个是类似的，我只是不明白。

我要做的是从数据库中获取一些数字。 我需要用户的总数，不同的用户，收到的消息数量和发送的消息数量，以及电子邮件，收集的电话号码和订阅的用户。 所有这些数据都必须与特定的account_id相关联。 下面是我的问题所在。

select COUNT(r.uid) as total_users,COUNT(DISTINCT(r.uid)) as unique_users,  COUNT(r.message) as messages, COUNT(s.message) as messages_sent,
count(ps.email) as emails_collected, 
count(ps.phone_number) as phone_nums_collected,
count(ps.added) as new_users
from conversation_facebook_page_received r
INNER JOIN conversation_facebook_page_sent s ON r.account_id = s.account_id
INNER JOIN conversation_facebook_page_subscribers ps on r.account_id = ps.account_id
WHERE DATE(FROM_UNIXTIME(r.received_time / 1000)) > "2018-01-01 00:00:00" AND r.account_id =616646015120462;

示例数据:

CREATE TABLE `conversation_facebook_page_received` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `msg_timestamp` bigint(20) unsigned NOT NULL,
  `received_time` bigint(20) unsigned DEFAULT NULL,
  `flow_id` int(10) unsigned DEFAULT NULL,
  `step_id` tinyint(3) unsigned DEFAULT NULL,
  `message_id` varchar(100) DEFAULT NULL,
  `message_type` varchar(20) DEFAULT NULL,
  `message` text,
  PRIMARY KEY (`account_id`,`uid`,`msg_timestamp`),
  KEY `flow_id` (`flow_id`),
  KEY `account_id` (`account_id`,`uid`,`received_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `conversation_facebook_page_received` (`account_id`, `uid`, `msg_timestamp`, `received_time`, `flow_id`, `step_id`, `message_id`, `message_type`, `message`)
VALUES
    (616646015120462,2503924196379227,1584982039136,1584982040232,NULL,NULL,NULL,'postback','Get Started'),
    (616646015120462,2503924196379227,1584982386400,1584982387189,NULL,NULL,'m_9X57kg_kRU4_WNXOFvt2QMglfcLimk7CpqvcjtCEbgZYdyYCx-Q5Z4OD_0XmRQzDCshgWcqGBHu7gt_aEhZRmw','text','thank you Bill you are doing the right thing. thank you.  please please lets stop these public gatherings NOW like Barnry Fife would say lets nip it in the bud  so far we been doing pretty good. lets step up our protection this is a must to defeat and win this war with the invisible enemy. i am seeing some school students treating this like  a vacation. they think they are invincible i guess as we did at that age. but somehow this needs enforcing maybe curfew will handle it.  lets stand proud and be protective please'),
    (85825440048,2503924196379227,1584983030585,1584983031341,NULL,NULL,NULL,'postback','Where are you located?'),
    (85825440048,2503924196379227,1584983047452,1584983048370,NULL,NULL,'m_xmK7lKY_86EIlS8dIQuRWMglfcLimk7CpqvcjtCEbgYaKd5ju8Nxscj78YjQWMUMFQkDZ161flNUZkLzZcDLgQ','text','moultrie ga'),
    (85825440048,2612891932156066,1586146320864,1586146321550,NULL,NULL,'m_4SNxwBOETFo3AEkxrRI_aNp0bwZ0kZj3tHm2Pjjq-Pk0SlZiIJrBlXMzdWPdpF80V2Bxw5rn9W34Yihs5oUUrA','text','could be useful'),
    (616646015120462,2792591144203564,1586896951790,1586896953965,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3024889994213410,1587234572314,1587234572945,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3278699712163817,1590010576412,1590010577520,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3300581309974788,1587015278753,1587015279577,NULL,NULL,NULL,'postback','Get Started');

    CREATE TABLE `conversation_facebook_page_sent` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned DEFAULT NULL,
  `flow_id` int(10) unsigned DEFAULT NULL,
  `step_id` tinyint(3) unsigned DEFAULT NULL,
  `send_time` bigint(20) unsigned DEFAULT NULL,
  `delivery_time` bigint(20) unsigned DEFAULT NULL,
  `read_time` bigint(20) unsigned DEFAULT NULL,
  `click_time` bigint(20) unsigned DEFAULT NULL,
  `message_id` varchar(100) DEFAULT NULL,
  `messaging_type` varchar(11) DEFAULT NULL,
  `tag` varchar(24) DEFAULT NULL,
  `message` text,
  KEY `flow_id` (`flow_id`),
  KEY `message_id` (`message_id`,`delivery_time`),
  KEY `account_id` (`account_id`,`uid`,`send_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `conversation_facebook_page_sent` (`account_id`, `uid`, `flow_id`, `step_id`, `send_time`, `delivery_time`, `read_time`, `click_time`, `message_id`, `messaging_type`, `tag`, `message`)
VALUES
  (616646015120462,2560048887396840,311,12,1583776598792,NULL,NULL,NULL,'k9Y48rBJ00rEt3pQB6WKnYcxhm_DmqdBwkxj2rJUQw1qrWJ2mTK676jH4oKNxSYR_SaK9caNXgXmebCkMGWucA','RESPONSE',NULL,'{\"attachment\":{\"type\":\"template\",\"payload\":{\"template_type\":\"button\",\"text\":\"Enter the BarkyPark  Sweepstakes and answer a simple 1 question survey to win a prize for you and your dog!\\nAre you ready to get started?\",\"buttons\":[{\"type\":\"postback\",\"title\":\"Get Me Started!\",\"payload\":\"254f28383a5e5d495561414039273859\"}]}}}'),
  (135613753250298,2681220635324690,382,2,1583778926385,1585072862424,NULL,NULL,'3lVOWAVsPtPrezfsfVmp4KLNDO4MLhlMAzKDQb7H-NqsvIZNa218b4nbGFUW-UfIcmYYXRTpZYsYpYJ0q68P8A','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2681220635324690,382,5,1583778959043,1585072862424,NULL,NULL,'RvG5e69Dz6tamKYfRS6G5KLNDO4MLhlMAzKDQb7H-Nq8BA4sTK9Fooj1-hKb57SGSa7kYfpHEq68lP4Y4f6iYQ','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (616646015120462,3452199664850501,382,2,1583780413556,NULL,NULL,NULL,'TyMsC7YuxGm1rj7Bsm_wCldXcpdYq_M6kRBtcWnLwNnv3xNUd7-WC0ANAebVwnD14ld3vagpMiSmoKgbAwvPMw','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,3452199664850501,382,5,1583780425764,NULL,NULL,NULL,'fArsI5l7Kfi_ys7KteuCcldXcpdYq_M6kRBtcWnLwNkQ58PZesvFKfYoaIpXoIH6qQR-RmVmd4LZ8Sm3KYHjOw','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (616646015120462,2916614285069775,382,2,1583782640116,NULL,NULL,NULL,'XqLkq7eq2-eorH0YUNOb1PQyrq48TiSkKb3IzaXtI_MoMC2zRu1Y8g7wdX21vyobi8Xqs5UxRMCwS4HsPzjpPg','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2916614285069775,382,5,1583782662572,NULL,NULL,NULL,'5sfoANj_a6wY9r6uBcGq9PQyrq48TiSkKb3IzaXtI_NnU8xY5WCqGUxzwgPGAbNCRjeXCFaS1ROO89rPSARkQw','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (135613753250298,2573340896108684,382,2,1583783558954,NULL,NULL,NULL,'2OtlgFNwcp7kXDD2fOneT9LlZVZ3bAkwnrR1oPc20zX1psaItIi2yt9HaXYXFQJgC_eZ68Rm-0uKPtW-cQKLSA','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2573340896108684,382,5,1583783573525,NULL,NULL,NULL,'bdqtU1VsVI6RmDgshnN-dtLlZVZ3bAkwnrR1oPc20zWrEU-LOyW7WnoraCIlC6UgiMP9Nces8R1UEjJk-E4J5g','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}');


  CREATE TABLE `conversation_facebook_page_subscribers` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `first_name` varchar(60) DEFAULT NULL,
  `last_name` varchar(60) DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `profile_pic` varchar(255) DEFAULT NULL,
  `subscribed` tinyint(1) unsigned DEFAULT '1',
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_contact` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `unsubscribe` datetime DEFAULT NULL,
  PRIMARY KEY (`account_id`,`uid`),
  KEY `account_id` (`account_id`,`last_contact`,`subscribed`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `conversation_facebook_page_subscribers` (`account_id`, `uid`, `name`, `first_name`, `last_name`, `email`, `phone_number`, `profile_pic`, `subscribed`, `added`, `last_contact`, `unsubscribe`)
VALUES
  (616646015120462,1162227020568661,'My Name','My','Name',NULL,NULL,NULL,1,'2019-10-06 11:04:35','2019-10-06 11:34:45',NULL),
  (616646015120462,1964363690254660,'Some Name','Some','Name',NULL,NULL,NULL,1,'2019-03-26 21:53:56','2019-05-01 15:25:34',NULL),
  (616646015120462,2118450464865475,'John Doe','John','Doe',NULL,NULL,NULL,1,'2019-01-16 20:04:25','2019-05-01 15:25:34',NULL),
  (85825440048,2170134986379371,'Andrew Stokes','Andrew','Stokes',NULL,NULL,NULL,1,'2019-01-22 02:22:41','2019-05-01 15:25:34',NULL),
  (616646015120462,2198986756859748,'Frank Kroger','Frank','Kroger',NULL,NULL,NULL,1,'2019-05-08 00:47:13','2019-12-24 06:45:09',NULL),
  (85825440048,2274994739293368,'Paul Briggs','Paul','Briggs',NULL,NULL,NULL,1,'2019-10-16 22:34:28','2019-10-16 22:37:08',NULL),
  (616646015120462,2485007991552080,'Pawan Bhamu','Pawan','Bhamu',NULL,NULL,NULL,1,'2019-05-20 22:42:49','2019-05-20 23:07:53',NULL),
  (85825440048,2503924196379227,'Scott Pettyjohn','Scott','Pettyjohn',NULL,NULL,NULL,1,'2020-03-23 16:47:20','2020-03-23 17:04:08',NULL),
  (85825440048,2530973936973839,'Firas M. Alameh','Firas','Alameh',NULL,NULL,NULL,1,'2019-03-11 07:21:03','2019-05-01 15:25:34',NULL);

此外，我还在db-fiddle.com上创建了一个小提琴。

fiddle的URL是https://www.db-fiddle.com/f/gqjbr8u3ziik4rxryghpvf/1。 在fiddle中，我只有10行，但是如果你运行fiddle，它会返回45个总用户，2个唯一用户，45条消息等等，这些数字是不可能正确的。 有人能告诉我我做错了什么吗？ 我想理解SQL，但显然我不理解它，否则我就不会得到如此扭曲的数字。 提前感谢你能给我的任何帮助。 请向我解释我做错了什么。