mysql 过滤问题
这是我的数据库表，
我通过以下sql可以过滤数据
select * from chat a where oId=(SELECT MAX(oId) FROM chat b where a.sendId =b.sendId)
但是，我想让得出来的结果后边在增加一列，得出每一个sendId的数量，如下图
[最优解释]
谢谢，这个行不？
select * from chat a INNER JOIN ( SELECT sendid ,
COUNT(*) AS count
FROM chat
WHERE isread = 1
GROUP BY sendid
) AS x ON a.sendid = x.sendid
where oId=(SELECT MAX(oId) FROM chat b where a.sendId =b.sendId AND b.isread=1)
另外，你是想只计算isread=1的数是吧？
[其他解释]
select a.*,x.count
from chat a inner join (select sendid,count(*) as count from chat group by sendid) as x on a.sendid=x.sendid
where oId=(SELECT MAX(oId) FROM chat b where a.sendId =b.sendId and b.isRead=1 )
试试这个
[其他解释]
SELECT *,b.sendid
FROM chat a,(SELECT COUNT(sendid)sendid,sendname FROM chat GROUP BY sendname )b
WHERE oId = ( SELECT MAX(oId)
FROM chat b
WHERE a.sendId = b.sendId
) AND a.sendname=b.sendname
[其他解释]
select a.*,x.count
from chat a inner join (select sendid,count(*) as count from chat group by sendid) as x on a.sendid=x.sendid
where oId=(SELECT MAX(oId) FROM chat b where a.sendId =b.sendId)
[其他解释]
怎么给你这个sql语句加一个 where isRead=1 谢谢大神
[其他解释]
你这个语句重复字段好多
[其他解释]
select a.*,x.count
from chat a inner join (select sendid,count(*) as count from chat group by sendid) as x on a.sendid=x.sendid
where oId=(SELECT MAX(oId) FROM chat b where a.sendId =b.sendId) and isRead=1
[其他解释]
我主要是不想造数据，你贴数据我还能正一些，图片太。。。
[其他解释]
后边加 and isRaed=1 结果是1条数据，本应该是3条数据
[其他解释]
SELECT a.* ,
x.count
FROM chat a
INNER JOIN ( SELECT sendid ,
COUNT(*) AS count
FROM chat
WHERE isread = 1
GROUP BY sendid
) AS x ON a.sendid = x.sendid
WHERE oId = ( SELECT MAX(oId)
FROM chat b
WHERE a.sendId = b.sendId
)
[其他解释]
是按照isread=1了，但是cout的结果是全部的，不对
[其他解释]
怎么把isRead=0的结果也查询出来了
[其他解释]
没有你的数据很难测
SELECT a.* ,
x.count
FROM chat a
INNER JOIN ( SELECT sendid ,
COUNT(*) AS count
FROM chat
WHERE isread = 1
GROUP BY sendid
) AS x ON a.sendid = x.sendid
WHERE oId = ( SELECT MAX(oId)
FROM chat b
WHERE isread = 1
WHERE a.sendId = b.sendId
)
[其他解释]
DROP TABLE IF EXISTS `chat`;
CREATE TABLE `chat` (
`oId` int(11) NOT NULL AUTO_INCREMENT,
`sendId` int(11) DEFAULT NULL,
`receId` int(11) DEFAULT NULL,
`sendName` varchar(255) DEFAULT NULL,
`message` varchar(255) DEFAULT NULL,
`time` varchar(255) DEFAULT NULL,
`source` int(11) DEFAULT NULL,
`isRead` int(11) DEFAULT NULL,
PRIMARY KEY (`oId`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of chat
-- ----------------------------
INSERT INTO `chat` VALUES ('1', '1', '110', '周杰伦111', '测试数据', '1352884243735', '1', '1');
INSERT INTO `chat` VALUES ('2', '1', '110', '周杰伦111', '测试数据222', '1350205843000', '1', '1');
INSERT INTO `chat` VALUES ('3', '1', '110', '周杰伦111', '测试数据333', '1350205843000', '1', '0');
INSERT INTO `chat` VALUES ('4', '2', '110', '刘德华', '刘德华测试数据1', '1352896489375', '1', '0');
INSERT INTO `chat` VALUES ('5', '2', '110', '刘德华', '刘德华测试数据2', '1352903026328', '1', '1');
INSERT INTO `chat` VALUES ('6', '2', '110', '刘德华', '刘德华测试数据3', '1352896606703', '1', '0');
INSERT INTO `chat` VALUES ('7', '2', '110', '刘德华', '刘德华测试数据4', '1352896613203', '1', '0');
INSERT INTO `chat` VALUES ('9', '3', '110', '张学友1', '测试数据222', '1352896699703', '1', '0');
INSERT INTO `chat` VALUES ('10', '3', '110', '张学友', '测试数据333', '1352896705640', '1', '1');
INSERT INTO `chat` VALUES ('11', '3', '110', '张学友', '刘德华测试数据4', '1352896717875', '1', '0');
INSERT INTO `chat` VALUES ('12', '1', '110', '周杰伦111', '测试数据444', '1352896606703', '1', '1');
[其他解释]
你写了2个where 换成and就好了，谢谢
[其他解释]
你看我这个sql语句可以吗
select * from (select count(oId) as 'count',a.* from chat a where isRead=1 group by sendId order by time desc) chat
[其他解释]
改成and就可以了
[其他解释]
你这个会报错哦。
[其他解释]
那现在好了没?主要是不知道你的isread是不是希望一开始就筛选掉。
[其他解释]
他里边写了2个where 把最后一个改成and就可以了
[其他解释]
那就结贴睡觉，另外，这里是SQLServer专区，以后最好到mysql专区，问题解决的更快
[其他解释]
好了，谢谢你们大家的帮忙