比如一个表
chapterid chapter
1 a
1 b
1 c
2 d
2 e
3 f
1 g
2 h
2 i
1 g
chapterid字段 1 重复了5次
2重复了4次
3出现了1次
我现在想让重复超过3次的 显示3次(而且要求每次执行select,显示的这3条记录是不同的,既从M条记录中随机抽取的3条)
没超过的就全部显示 请注意,是要每次查询的结果都不相同
每次从M条记录中选择的那n条记录都不一样
比如显示成
chapterid chapter
1 a
1 b
1 c
2 d
2 e
2 h
3 f
chapterid chapter
1 a
1 b
1 c
2 d
2 e
3 f
1 g
2 h
2 i
1 g
chapterid字段 1 重复了5次
2重复了4次
3出现了1次
我现在想让重复超过3次的 显示3次(而且要求每次执行select,显示的这3条记录是不同的,既从M条记录中随机抽取的3条)
没超过的就全部显示 请注意,是要每次查询的结果都不相同
每次从M条记录中选择的那n条记录都不一样
比如显示成
chapterid chapter
1 a
1 b
1 c
2 d
2 e
2 h
3 f
解决方案 »
- 无法多线程调用SQLExecDirect(hstmt,"select * from UserTable",SQL_NTS)
- sql explain语句
- 一个查询中怎么样区分0和-0
- PHP 程序找错(处理冗沉重复信息)
- c或者c++语言操作mysql,能不能建立永久连接?
- 关于sql的查询 及 书籍推荐 ???
- 如何使 mysql bind-address 多ip 的实现
- MYSQL查询EMAIL的一点疑惑
- navicat 能不能给mysql的用户针对不同的表设置不同的权限?
- 创建数据表时出现提示:语法错误
- datetime类型数据查询问题
- postgresql如何设置类似MYSQL的自增属性auto_increment??
left join tt b on a.chapterid=b.chapterid and a.id<=b.id
group by a.chapterid,a.chapter having count(b.chapterid) <=3
order by rand()
特别是你还有两条
1 g
是完全相同的,如果不同用一句SQL尚有可能。
mysql> create table tt(
-> chapterid int,
-> chapter varchar(5)
-> );
Query OK, 0 rows affected (0.07 sec)mysql> insert into tt values (1,'a');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (1,'b');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (1,'c');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (2,'d');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (2,'e');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (3,'f');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (1,'g');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (2,'h');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (2,'i');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (1,'j');
Query OK, 1 row affected (0.00 sec)mysql> insert into tt values (1,'k');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+-----------+---------+
| chapterid | chapter |
+-----------+---------+
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | d |
| 2 | e |
| 3 | f |
| 1 | g |
| 2 | h |
| 2 | i |
| 1 | j |
| 1 | k |
+-----------+---------+
11 rows in set (0.00 sec)
mysql> select a.chapterid,a.chapter
-> from (select chapterid,chapter ,round(sin(ASCII(chapter)*FLOOR(TIME_TO_SEC(CURTIME())/10))*10000) as sno from tt) a
-> inner join (select chapterid,chapter ,round(sin(ASCII(chapter)*FLOOR(TIME_TO_SEC(CURTIME())/10))*10000) as sno from tt) b
-> on a.chapterid=b.chapterid and a.sno>=b.sno
-> group by a.chapterid,a.chapter
-> having count(*) <=3 ;
+-----------+---------+
| chapterid | chapter |
+-----------+---------+
| 1 | a |
| 1 | j |
| 1 | k |
| 2 | d |
| 2 | e |
| 2 | i |
| 3 | f |
+-----------+---------+
7 rows in set (0.00 sec)mysql> select a.chapterid,a.chapter from (select chapterid,chapter ,round(sin(ASCII(chapter)*FLOOR(TIME_TO_SEC(CURTIME())/10))*10000) as sno from tt) a inner join (select chapterid,chapter ,round(sin(ASCII(chapter)*FLOOR(TIME_TO_SEC(CURTIME())/10))*10000) as sno from tt) b on a.chapterid=b.chapterid and a.sno>=b.sno group by a.chapterid,a.chapter having count(*) <=3;
+-----------+---------+
| chapterid | chapter |
+-----------+---------+
| 1 | b |
| 1 | j |
| 1 | k |
| 2 | d |
| 2 | e |
| 2 | h |
| 3 | f |
+-----------+---------+
7 rows in set (0.00 sec)
算法如下。自己用你的宿主语言来实现吧select distinct chapterid from tt
{
for each id
{
select * from tt where chapterid = id order by rand() limit 3
}
}