这是mysql上的“ msg”表
@H_502_5@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
我希望他看到这个,
@H_502_5@you have 2 unread msg to your "number 3" customer
you have 1 unread msg to your "number 8" customer
像新闻提要
我应该为此使用什么查询？
谢谢
最佳答案
您可能要使用以下查询.
@H_502_5@
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)
测试用例：
@H_502_5@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);
查询结果：
@H_502_5@+-------------------------------------------------+
| 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)