简单的mysql查询问题

概述这是mysql上的“ msg”表sent_to customer msg read ------- -------- ------ ----- 45 3 bla 0 34 4 bla 1 34 6 bla

这是mysql上的“ msg”表

sent_to  customer   msg      read-------  --------  ------   -----45          3       bla       034          4        bla       134          6        bla       045          3        bla       056          7        bla       145          8        bla       0

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

我希望他看到这个,

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

像新闻提要

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

谢谢

最佳答案您可能要使用以下查询.

SELECT   CONCAT('You have ',COUNT(`read`),' unread msg to your number ',customer,' customer') AS newsFROM     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)

总结

以上是内存溢出为你收集整理的简单的mysql查询问题 全部内容，希望文章能够帮你解决简单的mysql查询问题 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。