MySQL-查询未读消息和邮件消息 [英] MySQL - Querying for unread messages along with mail messages

问题描述

我需要在一个查询中使用用户名和未读消息的数量来检索消息.正在使用的查询是下面的查询，将检索用户名以及剩下的内容以获取未读邮件的数量.如何将未读邮件从EMAIL_MESSAGE(MESSAGE_STATUS ='U')发送到此查询，以便我获得信息以及其他详细信息.

I am in need to retrieve the messages in one query with name of users and in addition count of unread messages. The query being used is the one below and the name of user is retrieved and what is remaining to get count of unread messages. How can I get the unread messages from EMAIL_MESSAGE (MESSAGE_STATUS='U') to this query so that I have the information alongside with other details.

SELECT e.MAIL_NO, e.BIZ_ID, e.FROM_ADD, e.TO_ADD, e.EMAIL_SUBJECT,  
      DATE_FORMAT(e.UPDATED_DATE,'%d %b %y, %I:%i %p') AS DATE, e.MAIL_STATUS, 
      CONCAT(ufrom.USER_FIRST_NAME,' ',ufrom.USER_LAST_NAME) AS U_NAME FROM EMAIL e  
LEFT JOIN USER_CONFIG ufrom
     ON ufrom.USER_ID = e.FROM_ADD 
LEFT JOIN USER_CONFIG uto
     ON uto.USER_ID = e.TO_ADD
WHERE 
     e.FROM_ADD=: e.FROM_ADD  
ORDER BY MAX(e.UPDATED_DATE) DESC, ufrom.USER_FIRST_NAME DESC 
LIMIT 0, 10 

表格

CREATE TABLE IF NOT EXISTS USER_CONFIG(
  USER_ID INT UNSIGNED NOT NULL,
  USER_FIRST_NAME VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL, 
  USER_LAST_NAME VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL, 
  PRIMARY KEY (USER_ID),
  INDEX idx_USER_CONFIG_id1 (USER_ID ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS EMAIL (
 MAIL_NO INT 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,
 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 INT 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', 
  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)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

如果我不得不自己查询表，我将使用

If I were have to query the table on its own, I would use

SELECT COUNT(*) AS UNREAD_MESSAGE_COUNT FROM EMAIL_MESSAGE 
  WHERE BIZ_ID= :BIZID AND MESSAGE_STATUS = 'U';

但是我正在努力将其合并到上面给出的组合查询中.你能帮我吗?

But I am struggling to incorporate to the combined query as given above. Could you help me on this?

推荐答案

收到最后一封电子邮件和未读计数(收到).

Lasted email recieved and count of unread (recieved).

  SELECT e.MAIL_NO, e.BIZ_ID, e.FROM_ADD, e.TO_ADD, e.EMAIL_SUBJECT,  
  DATE_FORMAT(e.UPDATED_DATE,'%d %b %y, %I:%i %p') AS DATE, e.MAIL_STATUS, 
  CONCAT(ufrom.USER_FIRST_NAME,' ',ufrom.USER_LAST_NAME) AS U_NAME,if(UNREAD_MESSAGE_COUNT is null,0,UNREAD_MESSAGE_COUNT) FROM EMAIL e  
LEFT JOIN USER_CONFIG ufrom
 ON ufrom.USER_ID = e.TO_ADD
left join (SELECT COUNT(*) AS UNREAD_MESSAGE_COUNT,TO_ADD FROM EMAIL_MESSAGE         inner join EMAIL using (BIZ_ID)
WHERE MESSAGE_STATUS = 'U' group by TO_ADD) t on t.TO_ADD= e.TO_ADD
WHERE 
 e.TO_ADD=:TO_ADD
ORDER BY MAX(e.UPDATED_DATE) DESC, ufrom.USER_FIRST_NAME DESC 
LIMIT 0, 10 

已发送的电子邮件和未读的已发送邮件的数量:

Emails sent and count of unread sent messages:

  SELECT e.MAIL_NO, e.BIZ_ID, e.FROM_ADD, e.TO_ADD, e.EMAIL_SUBJECT,  
  DATE_FORMAT(e.UPDATED_DATE,'%d %b %y, %I:%i %p') AS DATE, e.MAIL_STATUS, 
  CONCAT(ufrom.USER_FIRST_NAME,' ',ufrom.USER_LAST_NAME) AS U_NAME,if(UNREAD_MESSAGE_COUNT is null,0,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,FROM_ADD FROM EMAIL_MESSAGE         inner join EMAIL using (BIZ_ID)
WHERE MESSAGE_STATUS = 'U' group by FROM_ADD) t on t.FROM_ADD= e.FROM_ADD
WHERE 
 e.FROM_ADD=:FROM_ADD
ORDER BY MAX(e.UPDATED_DATE) DESC, ufrom.USER_FIRST_NAME DESC 
LIMIT 0, 10 

-添加了如果count = null为0，并添加了左连接以确保显示结果.

Edited: - Added If count=null to be 0, and added left join to ensure results show up.

edit 2:将子查询更改为仅计算发送给该用户的未读内容.

edit 2: Changed the subquery to only count unread sent to this user.

根据所需结果，更新了第一查询并添加了第二查询.这应该可以解决问题.您可能想要第一个.

Edit 3: Updated 1st query and Added 2nd query depending on required results. This should do the trick. You probably want the first one.

这篇关于MySQL-查询未读消息和邮件消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！