mysql - 一起使用三个表的 SQL 查询没有返回正确的结果？

Question

我有 3 个表，其中 tbl_user_signup_info、tbl_main_lead_info 和 tbl_campaign_info，我需要一个显示信息的结果，以便每一行显示tbl_user_signup_info 和显示的相应注册用户添加的线索 ID 和事件 ID 应类似于 User_Id、Lead_Id、Campaign_ID。

实际上我想要使用这 3 个表的特定用户使用 Lead Id 添加的潜在客户总数和该用户使用 Campaign_Id 添加的事件总数。

但我缺乏形成 SQL 查询。

我的结果如下，错误的是:

表结构

--
-- Table structure for table `tbl_campaign_info`
--

DROP TABLE IF EXISTS `tbl_campaign_info`;
CREATE TABLE IF NOT EXISTS `tbl_campaign_info` (
  `Campaign_Id` int(100) NOT NULL AUTO_INCREMENT,
  `CampaignName` varchar(200) NOT NULL,
  `CampaignStatus` varchar(200) NOT NULL,
  `CampaignDescription` varchar(200) DEFAULT NULL,
  `CampaignOwnerNotes` varchar(200) DEFAULT NULL,
  `CampaignAdminNotes` varchar(200) DEFAULT NULL,
  `CampaignStartDate` date NOT NULL,
  `CampaignEndDate` date NOT NULL,
  `CampaignLead_Id` int(100) NOT NULL,
  `CampaignAddedBy` int(100) NOT NULL,
  `CampaignAddedOn` date DEFAULT NULL,
  UNIQUE KEY `Campaign_Id` (`Campaign_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Table structure for table `tbl_main_lead_info`
--

DROP TABLE IF EXISTS `tbl_main_lead_info`;
CREATE TABLE IF NOT EXISTS `tbl_main_lead_info` (
  `Lead_Id` int(100) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `Company` varchar(100) DEFAULT 'NA',
  `Website` varchar(100) DEFAULT 'NA',
  `Designation` varchar(100) DEFAULT 'NA',
  `Linkedin` varchar(100) DEFAULT 'NA',
  `Email` varchar(100) DEFAULT NULL,
  `Phone` varchar(100) DEFAULT NULL,
  `State` varchar(100) DEFAULT NULL,
  `Country` varchar(100) DEFAULT NULL,
  `TechArea` varchar(100) DEFAULT NULL,
  `FirmType` varchar(100) DEFAULT NULL,
  `FirmSize` varchar(100) DEFAULT NULL,
  `LastContactDate` date DEFAULT NULL,
  `NextContactDate` date DEFAULT NULL,
  `LeadDescription` varchar(200) DEFAULT NULL,
  `OwnerNotes` varchar(200) DEFAULT NULL,
  `SetReminder` date DEFAULT NULL,
  `AdminNotes` varchar(200) DEFAULT NULL,
  `LeadStatus` varchar(100) DEFAULT NULL,
  `LeadAddedBy` int(100) NOT NULL,
  `LeadAddedOn` datetime DEFAULT NULL,
  PRIMARY KEY (`Lead_Id`),
  UNIQUE KEY `FirstName` (`FirstName`,`LastName`,`Company`,`Website`,`Designation`,`Linkedin`,`Email`,`Phone`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_main_lead_info`
--

INSERT INTO `tbl_main_lead_info` (`Lead_Id`, `FirstName`, `LastName`, `Company`, `Website`, `Designation`, `Linkedin`, `Email`, `Phone`, `State`, `Country`, `TechArea`, `FirmType`, `FirmSize`, `LastContactDate`, `NextContactDate`, `LeadDescription`, `OwnerNotes`, `SetReminder`, `AdminNotes`, `LeadStatus`, `LeadAddedBy`, `LeadAddedOn`) VALUES
(15, 'John', 'Doe', 'test', 'test', 'test', 'test', 'test', 'test', 'Texas', 'USA', 'test', 'Corporate', '11-50', '2020-01-09', '2020-01-10', 'Testing Description of Lead information', NULL, '2020-01-11', 'This need to be confidential by admin', 'Active', 18, '2020-01-09 16:07:17');

--
-- Dumping data for table `tbl_campaign_info`
--

INSERT INTO `tbl_campaign_info` (`Campaign_Id`, `CampaignName`, `CampaignStatus`, `CampaignDescription`, `CampaignOwnerNotes`, `CampaignAdminNotes`, `CampaignStartDate`, `CampaignEndDate`, `CampaignLead_Id`, `CampaignAddedBy`, `CampaignAddedOn`) VALUES
(16, 'Test', 'Active', 'Test', NULL, 'This is admin notes and need to be kept confidential', '2020-01-09', '2020-01-10', 15, 18, '2020-01-09'),
(17, 'Test', 'Active', 'Test ', NULL, 'NA', '2020-01-10', '2020-01-10', 15, 18, '2020-01-09');


--
-- Table structure for table `tbl_user_signup_info`
--

DROP TABLE IF EXISTS `tbl_user_signup_info`;
CREATE TABLE IF NOT EXISTS `tbl_user_signup_info` (
  `User_Id` int(50) NOT NULL AUTO_INCREMENT,
  `UserEmail` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserPassword` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Admin` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`User_Id`) USING BTREE,
  UNIQUE KEY `Email` (`UserEmail`),
  UNIQUE KEY `UserName` (`UserName`),
  UNIQUE KEY `UserEmail` (`UserEmail`,`UserName`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_user_signup_info`
--

INSERT INTO `tbl_user_signup_info` (`User_Id`, `UserEmail`, `UserName`, `UserPassword`, `Admin`) VALUES
(18, 'test@gmail.com', 'test', 'test', 1),
(22, 'test1@gmail.com', 'test1', 'test1', 0),
(23, 'test2@gmail.com', 'test2', 'test2', 0);
COMMIT;

我的 SQL 查询如下:

SELECT User_Id, Campaign_Id, Lead_Id 
FROM   tbl_campaign_info AS C, 
       tbl_main_lead_info AS M,
       tbl_user_signup_info AS U 
WHERE  C.CampaignAddedBy IN ( SELECT User_Id 
                              FROM   tbl_user_signup_info AS U, 
                                     tbl_campaign_info AS C 
                              WHERE  U.User_Id = C.CampaignAddedBy) 
AND    M.LeadAddedBy IN (SELECT User_Id 
                         FROM   tbl_user_signup_info AS U, 
                                tbl_main_lead_info AS M 
                         WHERE  U.User_Id = M.LeadAddedBy )

Answer 1

如果我没理解错的话，您需要的是用户事件计数和每个用户的潜在客户计数。因此，选择事件数量，选择线索数量，加入他们。

为此，您可以使用两个结果的完全外部联接(因此只获取至少拥有一个事件参与者或领导者的那些用户)，但 MySQL 不支持它。不过，我认为 tbl_user_signup_info 包含每个用户一行，因此您可以将其用作基表。以下查询为您提供该表中每个用户的结果。

select
  u.user_id,
  coalesce(c.total, 0) as campaign_count, 
  coalesce(l.total, 0) as lead_count
from tbl_user_signup_info u
left join
(
  select campaignaddedby, count(*) as total
  from tbl_campaign_info
  group by campaignaddedby
) c on c.campaignaddedby = u.user_id
left join
(
  select leadaddedby, count(*) as total
  from tbl_main_lead_info
  group by leadaddedby
) l on l.leadaddedby = u.user_id
order by u.user_id;