简单的mysql查询问题

这是mysql上的“ msg”表

sent_to  customer   msg      read
-------  --------  ------   -----
45          3       bla       0
34          4        bla       1
34          6        bla       0
45          3        bla       0
56          7        bla       1
45          8        bla       0

例如ID号为45的用户登录,

我希望他看到这个,

you have 2 unread msg to your "number 3" customer
you have 1 unread msg to your "number 8" customer

像新闻提要

我应该为此使用什么查询？

谢谢

SELECT   CONCAT('You have ',COUNT(`read`),' unread msg to your number ',customer,' customer') AS news
FROM     msg 
WHERE    `read` = '0' AND `sent_to` = '45'
GROUP BY customer;

请注意,read是MySQL中的保留字,因此您必须将其括在反引号中. (Source)

测试用例：

CREATE TABLE msg (
    `sent_to`    int,`customer`   int,`msg`        varchar(10),`read`       int
);

INSERT INTO msg VALUES(45,3,'bla',0);
INSERT INTO msg VALUES(34,4,1);
INSERT INTO msg VALUES(34,6,0);
INSERT INTO msg VALUES(45,0);
INSERT INTO msg VALUES(56,7,1);
INSERT INTO msg VALUES(45,8,0);

查询结果：

+-------------------------------------------------+
| news                                            |
+-------------------------------------------------+
| You have 2 unread msg to your number 3 customer |
| You have 1 unread msg to your number 8 customer |
+-------------------------------------------------+
2 rows in set (0.00 sec)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!