我有这样一个表CREATE TABLE `BX-Book-Ratings` (
`User-ID` int(11) NOT NULL default '0',
`ISBN` varchar(13) NOT NULL default '',
`Book-Rating` int(11) NOT NULL default '0',
PRIMARY KEY (`User-ID`,`ISBN`)
) TYPE=MyISAM;现在想统计以下信息:
(1)针对每个`User-ID`字段的取值,对应的`Book-Rating`字段值不为0的记录的数量?
(2)针对每个`User-ID`字段的取值,对应的`Book-Rating`字段值不为0的记录的数量小于5的User-ID的取值有多少?具体就是表中的每条记录表示用户对一本书的打分,打分值为0表示用户没有对该书打分。我想统计(1)分别每个用户对多少本书打过分;(2)打过分的书个数小于5的用户有多少个?
`User-ID` int(11) NOT NULL default '0',
`ISBN` varchar(13) NOT NULL default '',
`Book-Rating` int(11) NOT NULL default '0',
PRIMARY KEY (`User-ID`,`ISBN`)
) TYPE=MyISAM;现在想统计以下信息:
(1)针对每个`User-ID`字段的取值,对应的`Book-Rating`字段值不为0的记录的数量?
(2)针对每个`User-ID`字段的取值,对应的`Book-Rating`字段值不为0的记录的数量小于5的User-ID的取值有多少?具体就是表中的每条记录表示用户对一本书的打分,打分值为0表示用户没有对该书打分。我想统计(1)分别每个用户对多少本书打过分;(2)打过分的书个数小于5的用户有多少个?
解决方案 »
- Linux 下MY-SQL 支持查询文本吗
- mysql命令执行问题!
- 请教一个mysql语句,limit的用法
- mysql扩展哪里添加呢
- MySQL Server and Client与Command-Line Utilities
- 超大量数据处理,高手请进!
- 请问,数据库 id 适合用 varchar类型吗?
- ERROR 1044: Access denied for user: ''@'localhost' to database 'testdb'
- mysql现在普遍使用的版本急?急?
- sql 高手请指点这样的select 如何写,要求见贴内,先谢过!
- 排序难题
- mysql批量删除数据的存储过程怎么写
select `User-ID`,count(*) from tt where `Book-Rating`<>0 group by `User-ID`
2
select count(*) from (
select `User-ID` from tt where `Book-Rating`<>0 group by `User-ID` having count(*)<=5) a
select `User-ID`,count(*)
From `BX-Book-Ratings`
where `Book-Rating` !=0
group by `User-ID`
select `User-ID`,count(*)
From `BX-Book-Ratings`
where `Book-Rating` !=0
group by `User-ID`
having count(*)<5
看看结果,可以用横线
可以执行了,但是没有返回结果
插入记录的sql语句为:
INSERT INTO `BX-Book-Ratings` VALUES (276725,'034545104X',0);
INSERT INTO `BX-Book-Ratings` VALUES (276726,'0155061224',5);
INSERT INTO `BX-Book-Ratings` VALUES (276727,'0446520802',0);
INSERT INTO `BX-Book-Ratings` VALUES (276729,'052165615X',3);
INSERT INTO `BX-Book-Ratings` VALUES (276729,'0521795028',6);
INSERT INTO `BX-Book-Ratings` VALUES (276733,'2080674722',0);
INSERT INTO `BX-Book-Ratings` VALUES (276736,'3257224281',8);
INSERT INTO `BX-Book-Ratings` VALUES (276737,'0600570967',6);
INSERT INTO `BX-Book-Ratings` VALUES (276744,'038550120X',7);
INSERT INTO `BX-Book-Ratings` VALUES (276745,'342310538',10);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'0425115801',0);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'0449006522',0);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'0553561618',0);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'055356451X',0);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'0786013990',0);
INSERT INTO `BX-Book-Ratings` VALUES (276746,'0786014512',0);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0060517794',9);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0451192001',0);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0609801279',0);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0671537458',9);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0679776818',8);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'0943066433',7);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'1570231028',0);
INSERT INTO `BX-Book-Ratings` VALUES (276747,'1885408226',7);
INSERT INTO `BX-Book-Ratings` VALUES (276748,'0747558167',6);
INSERT INTO `BX-Book-Ratings` VALUES (276748,'3442437407',0);
INSERT INTO `BX-Book-Ratings` VALUES (276751,'033390804X',0);
这是一部分,共有100多万条
+---------+------------+-------------+
| User-ID | ISBN | Book-Rating |
+---------+------------+-------------+
| 276725 | 034545104X | 0 |
| 276726 | 0155061224 | 5 |
| 276727 | 0446520802 | 0 |
| 276729 | 052165615X | 3 |
| 276729 | 0521795028 | 6 |
| 276733 | 2080674722 | 0 |
| 276736 | 3257224281 | 8 |
| 276737 | 0600570967 | 6 |
| 276744 | 038550120X | 7 |
| 276745 | 342310538 | 10 |
| 276746 | 0425115801 | 0 |
| 276746 | 0449006522 | 0 |
| 276746 | 0553561618 | 0 |
| 276746 | 055356451X | 0 |
| 276746 | 0786013990 | 0 |
| 276746 | 0786014512 | 0 |
| 276747 | 0060517794 | 9 |
| 276747 | 0451192001 | 0 |
| 276747 | 0609801279 | 0 |
| 276747 | 0671537458 | 9 |
| 276747 | 0679776818 | 8 |
| 276747 | 0943066433 | 7 |
| 276747 | 1570231028 | 0 |
| 276747 | 1885408226 | 7 |
| 276748 | 0747558167 | 6 |
| 276748 | 3442437407 | 0 |
| 276751 | 033390804X | 0 |
+---------+------------+-------------+
27 rows in set (0.02 sec)mysql> SELECT `User-ID`,COUNT(*) FROM `BX-Book-Ratings` WHERE `Book-Rating`<>0 G
ROUP BY `User-ID`
-> ;
+---------+----------+
| User-ID | COUNT(*) |
+---------+----------+
| 276726 | 1 |
| 276729 | 2 |
| 276736 | 1 |
| 276737 | 1 |
| 276744 | 1 |
| 276745 | 1 |
| 276747 | 5 |
| 276748 | 1 |
+---------+----------+
8 rows in set (0.01 sec)mysql>
+---------+------------+-------------+
| User-ID | ISBN | Book-Rating |
+---------+------------+-------------+
| 276725 | 034545104X | 0 |
| 276726 | 0155061224 | 5 |
| 276727 | 0446520802 | 0 |
| 276729 | 052165615X | 3 |
| 276729 | 0521795028 | 6 |
| 276733 | 2080674722 | 0 |
| 276736 | 3257224281 | 8 |
| 276737 | 0600570967 | 6 |
| 276744 | 038550120X | 7 |
| 276745 | 342310538 | 10 |
| 276746 | 0425115801 | 0 |
| 276746 | 0449006522 | 0 |
| 276746 | 0553561618 | 0 |
| 276746 | 055356451X | 0 |
| 276746 | 0786013990 | 0 |
| 276746 | 0786014512 | 0 |
| 276747 | 0060517794 | 9 |
| 276747 | 0451192001 | 0 |
| 276747 | 0609801279 | 0 |
| 276747 | 0671537458 | 9 |
| 276747 | 0679776818 | 8 |
| 276747 | 0943066433 | 7 |
| 276747 | 1570231028 | 0 |
| 276747 | 1885408226 | 7 |
| 276748 | 0747558167 | 6 |
| 276748 | 3442437407 | 0 |
| 276751 | 033390804X | 0 |
+---------+------------+-------------+
27 rows in set (0.02 sec)mysql> SELECT `User-ID`,COUNT(*) FROM `BX-Book-Ratings` WHERE `Book-Rating`<>0
ROUP BY `User-ID`
-> ;
+---------+----------+
| User-ID | COUNT(*) |
+---------+----------+
| 276726 | 1 |
| 276729 | 2 |
| 276736 | 1 |
| 276737 | 1 |
| 276744 | 1 |
| 276745 | 1 |
| 276747 | 5 |
| 276748 | 1 |
+---------+----------+
8 rows in set (0.01 sec)mysql> SELECT COUNT(*) FROM (
-> SELECT `User-ID` FROM `BX-Book-Ratings` WHERE `Book-Rating`<>0 GROUP BY
User-ID` HAVING COUNT(*)<=5) a;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)mysql>