mysql> select * from test; +-----------+ | typenum | +-----------+ | 1,6,15,12 | | 1,30,15 | | 32,2,6 | | 6,15,11 | +-----------+ 4 rows in set (0.00 sec)mysql> select n from ( -> select SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',1) as n from test -> union all -> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',2),',',-1) from test -> union all -> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',3),',',-1) from test -> union all -> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',4),',',-1) from test -> ) a -> Where n!='' -> Group by n -> order by Count(*) desc -> limit 10; +------+ | n | +------+ | 6 | | 15 | | 1 | | 12 | | 30 | | 2 | | 32 | | 11 | +------+ 8 rows in set (0.00 sec)mysql>
SELECT ss,COUNT(*) FROM (SELECT *,CONCAT(',',A.typenum,','),MID(CONCAT(',',A.typenum,','),B.ID+1), INSTR(MID(CONCAT(',',A.typenum,','),B.ID+1),','), MID(MID(CONCAT(',',A.typenum,','),B.ID+1),1,INSTR(MID(CONCAT(',',A.typenum,','),B.ID+1),',')-1) AS ss FROM TESTF A LEFT JOIN ZZ.lsb1 B ON LENGTH(CONCAT(',',A.typenum,','))>=B.id WHERE MID(CONCAT(',',A.typenum,','),B.id,1)=',' ORDER BY A.typenum,ID ) aa WHERE ss<>'' GROUP BY ss ORDER BY 2 DESC LIMIT 10建立LSB1,字段ID,内容1-10000
+-----------+
| typenum |
+-----------+
| 1,6,15,12 |
| 1,30,15 |
| 32,2,6 |
| 6,15,11 |
+-----------+
4 rows in set (0.00 sec)mysql> select n from (
-> select SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',1) as n from test
-> union all
-> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',2),',',-1) from test
-> union all
-> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',3),',',-1) from test
-> union all
-> select SUBSTRING_INDEX(SUBSTRING_INDEX(concat(typenum,',,,,,,,,'),',',4),',',-1) from test
-> ) a
-> Where n!=''
-> Group by n
-> order by Count(*) desc
-> limit 10;
+------+
| n |
+------+
| 6 |
| 15 |
| 1 |
| 12 |
| 30 |
| 2 |
| 32 |
| 11 |
+------+
8 rows in set (0.00 sec)mysql>
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
INSTR(MID(CONCAT(',',A.typenum,','),B.ID+1),','),
MID(MID(CONCAT(',',A.typenum,','),B.ID+1),1,INSTR(MID(CONCAT(',',A.typenum,','),B.ID+1),',')-1) AS ss
FROM TESTF A LEFT JOIN ZZ.lsb1 B ON LENGTH(CONCAT(',',A.typenum,','))>=B.id
WHERE MID(CONCAT(',',A.typenum,','),B.id,1)=',' ORDER BY A.typenum,ID ) aa
WHERE ss<>''
GROUP BY ss ORDER BY 2 DESC
LIMIT 10建立LSB1,字段ID,内容1-10000