表:
CREATE TABLE bbbb (id int(11) NOT NULL,class varchar(50) NOT NULL);
id class
1 7,4
2 7,2,6
3 1,2
4 2,4
5 6
6 6,10
7 1,6
8 2
数据:
insert into bbbb
values (1,'7,4'),(2,'7,2,6'),(3,'1,2'),(4,'2,4'),(5,'6'),(6,'6,10'),(7,'1,6'),(8,'2');想得到的结果是:
class count(*)
1 2 --1出现2次
2 4 --2出现4次
4 2 ...
6 4
7 2
10 1
就是对class列的值分出来并且进行统计.(感觉就是原表就是被group_concat()函数得来的结果,现在想得到的是被group_concat()之前的class列的统计)
CREATE TABLE bbbb (id int(11) NOT NULL,class varchar(50) NOT NULL);
id class
1 7,4
2 7,2,6
3 1,2
4 2,4
5 6
6 6,10
7 1,6
8 2
数据:
insert into bbbb
values (1,'7,4'),(2,'7,2,6'),(3,'1,2'),(4,'2,4'),(5,'6'),(6,'6,10'),(7,'1,6'),(8,'2');想得到的结果是:
class count(*)
1 2 --1出现2次
2 4 --2出现4次
4 2 ...
6 4
7 2
10 1
就是对class列的值分出来并且进行统计.(感觉就是原表就是被group_concat()函数得来的结果,现在想得到的是被group_concat()之前的class列的统计)
select count(*) from bbbb where class like '%1,%';
SELECT a.*,MID(CONCAT(',',a.class,','),b.id,1),
CONCAT(',',a.class,','),
REPLACE(
MID(CONCAT(',',a.class,','),b.id,
INSTR(MID(CONCAT(a.class,','),b.id,LENGTH(CONCAT(a.class,','))),',')+1),',','') AS aa1
FROM bbbb a LEFT JOIN lsb1 b
ON LENGTH(a.class)+2>=b.id
WHERE MID(CONCAT(',',a.class,','),b.id,1)=','
AND
LENGTH(MID(CONCAT(',',a.class,','),b.id,
INSTR(MID(CONCAT(a.class,','),b.id,LENGTH(CONCAT(a.class,','))),',')+1))>=2
ORDER BY a.id) qq GROUP BY aa1lsb1:字段ID,内容1-10000
SELECT * FROM (
select 1 AS class,count(*) from bbbb where find_in_set(1,class) >0
union all
select 2 AS class, count(*) from bbbb where find_in_set(2,class) >0
union all
select 3 AS class, count(*) from bbbb where find_in_set(3,class)>0
union all
select 4 AS class, count(*) from bbbb where find_in_set(4,class)>0
union all
select 5 AS class, count(*) from bbbb where find_in_set(5,class)>0
union all
select 6 AS class, count(*) from bbbb where find_in_set(6,class)>0
union all
select 7 AS class, count(*) from bbbb where find_in_set(7,class)>0
union all
select 8 AS class, count(*) from bbbb where find_in_set(8,class)>0
union all
select 9 AS class, count(*) from bbbb where find_in_set(9,class)>0
union all
select 10 AS class, count(*) from bbbb where find_in_set(10,class)>0 ) AS ttt
mysql> select id ,count(1) from (select a.id from a,bbbb b where instr(concat(b.class,','),concat(a.id,','))) a group by id;
+------+----------+
| id | count(1) |
+------+----------+
| 1 | 2 |
| 2 | 4 |
| 4 | 2 |
| 6 | 4 |
| 7 | 2 |
| 10 | 1 |
+------+----------+
6 rows in set (0.00 sec)
修改成
where concat(',',class,',') like ',10,')
在CLASS上建立索引,看看速度如何