php - MYSQL - 查找电子邮件中用户的未读消息

Question

我有一个带有两个表的简单电子邮件数据库。

电子邮件和 EMAIL_MESSAGES

    CREATE TABLE IF NOT EXISTS EMAIL (
      MAIL_NO TINYINT UNSIGNED NOT NULL, 
      BIZ_ID VARCHAR(35) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NOT NULL, 
      FROM_ADD INT UNSIGNED NOT NULL,  -- FOR EMAIL_TYPE EUCP, THIS WILL BE AGENT ID
      TO_ADD INT UNSIGNED DEFAULT NULL, -- FOR EMAIL_TYPE EUCP, THIS WILL BE CANDIDATE_ID
      EMAIL_SUBJECT VARCHAR(75) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL,
      MAIL_STATUS CHAR(1) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT 'A',
      UPDATED_DATE DATETIME ON UPDATE CURRENT_TIMESTAMP,
      EMAIL_TYPE VARCHAR(10) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
      PRIMARY KEY (MAIL_NO,BIZ_ID),
      INDEX idx_EMAIL_id1 (FROM_ADD ASC),
      INDEX idx_EMAIL_id2 (TO_ADD ASC),
      INDEX idx_EMAIL_id3 (BIZ_ID ASC),
      INDEX idx_EMAIL_id4 (MAIL_STATUS ASC),
      INDEX idx_EMAIL_id5 (EMAIL_SUBJECT ASC), 
      INDEX idx_EMAIL_id6 (UPDATED_DATE ASC)  
    )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS EMAIL_MESSAGE (
  MESSAGE_NO TINYINT UNSIGNED NOT NULL, 
  BIZ_ID VARCHAR(35) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NOT NULL, 
  SENDER INT UNSIGNED DEFAULT NULL, 
  MESSAGE TEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL, 
  ATTACHMENT VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
  MESSAGE_STATUS CHAR(1) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT 'U', 
  SEND_LOCATION VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
  READ_DATE DATETIME DEFAULT NULL, 
  SEND_DATE DATETIME DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (MESSAGE_NO,BIZ_ID),
  INDEX idx_EMAIL_MESSAGE_id1 (SENDER ASC),
  INDEX idx_EMAIL_MESSAGE_id2 (MESSAGE_STATUS ASC),
  CONSTRAINT fk_EMAIL_MESSAGE_C1
    FOREIGN KEY (BIZ_ID)
    REFERENCES EMAIL (BIZ_ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

填充数据

INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10001-10001',10001,10110,'Pacific','A','2015-09-24 10:12:29','EUCP');
INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10111-10003',10111,10110,'Atlantic','A','2015-09-24 09:48:05','EUCP');
INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10112-10004',10112,10110,'Artic','A','2015-09-24 09:54:43','EUCP');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10001-10001',10001,'I need an Apple',NULL,'U','TBD',NULL,'2015-09-24 10:12:29');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10111-10003',10111,'I have a product that you might be keen to have a look at>',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 09:48:05');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10112-10004',10112,'New products have been launched. Thought you would be interested',NULL,'R','TBD','2015-09-24 10:09:53','2015-09-24 09:54:43');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (2,'10033-10111-10003',10111,'where is this based',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 10:09:02');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (2,'10033-10112-10004',10110,'Thanks',NULL,'R','TBD','2015-09-24 10:09:53','2015-09-24 10:08:43');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (3,'10033-10111-10003',10110,'You mean me?',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 10:09:26');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (3,'10033-10112-10004',10110,'Any update?',NULL,'U','TBD',NULL,'2015-09-24 10:10:33');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (4,'10033-10111-10003',10111,'Yes',NULL,'U','TBD',NULL,'2015-09-24 10:10:51');

我已经尝试过以下方法，但它仅在有一条消息时才有效，并且每隔一段时间就会失败。你能找出问题出在哪里吗？

电子邮件发件人

Select e.MAIL_NO, e.BIZ_ID, e.From_ADD AS AGENT_ID, e.TO_ADD AS CAndIDATE_ID, e.EMAIL_SUBJECT,  e.MAIL_STATUS,
  em.SENDER, ConCAT(uto.USER_FIRST_NAME,' ',uto.USER_LAST_NAME) as USER_NAME,
  if(em.UNREAD_MESSAGE_COUNT is NULL,0,em.UNREAD_MESSAGE_COUNT) as UNREAD_MESSAGE_COUNT From EMAIL e Left Join 
USER_ConFIG uto on uto.USER_ID = e.TO_ADD Left Join 
      (Select COUNT(*) AS UNREAD_MESSAGE_COUNT, SENDER, BIZ_ID From EMAIL_MESSAGE Inner Join EMAIL USING (BIZ_ID)
        Where MESSAGE_STATUS = 'U' And SENDER NOT IN ('10001') GROUP BY SENDER) em
    on em.BIZ_ID = e.BIZ_ID Where 
    e.From_ADD='10111' ;

电子邮件接收器

Select e.MAIL_NO, e.BIZ_ID, e.From_ADD AS AGENT_ID, e.TO_ADD AS CAndIDATE_ID, e.EMAIL_SUBJECT,  e.MAIL_STATUS,
  em.SENDER,  ConCAT(uFrom.USER_FIRST_NAME,' ',uFrom.USER_LAST_NAME) AS USER_NAME,
  IF(em.UNREAD_MESSAGE_COUNT IS NULL,0,em.UNREAD_MESSAGE_COUNT) AS UNREAD_MESSAGE_COUNT From EMAIL e Left Join 
USER_ConFIG uFrom 
on uFrom.USER_ID = e.From_ADD 
Left Join 
      (Select COUNT(*) AS UNREAD_MESSAGE_COUNT, SENDER, BIZ_ID From EMAIL_MESSAGE Inner Join EMAIL USING (BIZ_ID)
        Where MESSAGE_STATUS = 'U' And SENDER NOT IN ('10002') ) em
    on e.BIZ_ID = em.BIZ_ID 
Where 
    e.TO_ADD="10110" 

Answer 1

我不太确定我是否准确理解您要对第一个查询执行的操作，但子查询结果可能有点奇怪。您似乎还获得了每个具有 BIZ_ID 的发件人的未读消息计数。但是您没有加入 BIZ_ID，也没有指定要带回哪个 BIZ_ID(从哪个表或这些表的哪一行)。

但是不确定子查询是否必要，并认为可以通过连接来完成:-

Select e.MAIL_NO, 
        e.BIZ_ID, 
        e.From_ADD AS AGENT_ID, 
        e.TO_ADD AS CAndIDATE_ID, 
        e.EMAIL_SUBJECT,  
        e.MAIL_STATUS,
        em.SENDER, 
        ConCAT(uto.USER_FIRST_NAME,' ',uto.USER_LAST_NAME) as USER_NAME,
        COUNT(em.MESSAGE_NO) as UNREAD_MESSAGE_COUNT 
FROM EMAIL e 
INNER JOIN USER_ConFIG uto ON uto.USER_ID = e.TO_ADD 
LEFT OUTER JOIN EMAIL_MESSAGE em ON e.BIZ_ID = em.BIZ_ID AND em.MESSAGE_STATUS = 'U' AND em.SENDER NOT IN ('10001') 
WHERE e.From_ADD='10111' 
GROUP BY e.MAIL_NO, 
        e.BIZ_ID, 
        e.From_ADD AS AGENT_ID, 
        e.TO_ADD AS CAndIDATE_ID, 
        e.EMAIL_SUBJECT,  
        e.MAIL_STATUS,
        em.SENDER, 
        USER_NAME;