mysql 过滤有关问题

2012-12-15

mysql 过滤问题这是我的数据库表，我通过以下sql可以过滤数据select*from chat a whereoId(SELECT MAX(oId

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)
[其他解释]

引用:

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 ……

怎么给你这个sql语句加一个 where isRead=1 谢谢大神

[其他解释]

引用:

SQL code?123456SELECT *,b.sendid FROM chat a,(SELECT COUNT(sendid)sendid,sendname FROM chat GROUP BY sendname )b WHERE oId = ( SELECT MAX(oId) FROM chat b ……

你这个语句重复字段好多
[其他解释]
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
[其他解释]

引用:

引用:SQL code?123456SELECT *,b.sendid FROM chat a,(SELECT COUNT(sendid)sendid,sendname FROM chat GROUP BY sendname )b WHERE oId = ( SELECT MAX(oId) ……

我主要是不想造数据，你贴数据我还能正一些，图片太。。。
[其他解释]

引用:

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.sendI……

后边加 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
               )

[其他解释]

引用:

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.sen……

是按照isread=1了，但是cout的结果是全部的，不对
[其他解释]

引用:

SQL code?12345678910111213SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , COUNT(*) AS count FROM chat ……

怎么把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
               )

[其他解释]

引用:

没有你的数据很难测SQL code?1234567891011121314SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , COUNT(*) AS count F……

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');
[其他解释]

引用:

没有你的数据很难测SQL code?1234567891011121314SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , COUNT(*) AS count F……

你写了2个where 换成and就好了，谢谢
[其他解释]

引用:

没有你的数据很难测SQL code?1234567891011121314SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , COUNT(*) AS count F……

你看我这个sql语句可以吗
select * from (select count(oId) as 'count',a.* from chat a where isRead=1 group by sendId order by time desc) chat
[其他解释]

引用:

谢谢，这个行不？SQL code?1234567select * from chat a INNER JOIN ( SELECT sendid , COUNT(*) AS count FROM chat WHERE i……

改成and就可以了
[其他解释]

引用:

引用:没有你的数据很难测SQL code?1234567891011121314SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , COUNT(*) AS coun……

你这个会报错哦。
[其他解释]
那现在好了没?主要是不知道你的isread是不是希望一开始就筛选掉。
[其他解释]

引用:

引用:引用:没有你的数据很难测SQL code?1234567891011121314SELECT a.* , x.count FROM chat a INNER JOIN ( SELECT sendid , ……

他里边写了2个where 把最后一个改成and就可以了
[其他解释]
那就结贴睡觉，另外，这里是SQLServer专区，以后最好到mysql专区，问题解决的更快
[其他解释]

引用:

那现在好了没?主要是不知道你的isread是不是希望一开始就筛选掉。

好了，谢谢你们大家的帮忙

热点排行

SQL Server

mysql 过滤有关问题