Question

高分悬赏 mysql多表查询，并统计的问题

数据库用的是 mysql
表1 table1
nameid name
1 张三
2 王二
3 李四
表2 table2
id name
1 张三,李四,王二
2 王二,赵五
3 张三,王二
求助：统计表1里 name字段里的数据 在表2里出现的次数，并得到如下结果
nameid name namecount
1 张三 2
2 王二 3
3 李四 1
请把完整的查询语句写出来。
表结构已经固定了，不能修改。而且只能用使用与mysql的查询语句

Answer 1

本期我们用 MySQL 提供的 DBUG 工具来研究 MySQL 的 SQL 处理流程。

起手先造个实例

这里得稍微改一下实例的启动文件 start，将 CUSTOM_MYSQLD 改为 mysqld-debug：

重启一下实例，加上 debug 参数：

我们来做一两个实验，说明 DBUG 包的作用：

先设置一个简单的调试规则，我们设置了两个调试选项：

• d：开启各个调试点的输出

• O,/tmp/mysqld.trace：将调试结果输出到指定文件



请点击输入图片描述

然后我们创建了一张表，来看一下调试的输出结果：



请点击输入图片描述

可以看到 create table 的过程中，MySQL 的一些细节操作，比如分配内存 alloc_root 等

这样看还不够直观，我们增加一些信息：



请点击输入图片描述

来看看效果：



请点击输入图片描述

可以看到输出变成了调用树的形式，现在就可以分辨出 alloc_root 分配的内存，是为了解析 SQL 时用的（mysql_parse）

我们再增加一些有用的信息：



请点击输入图片描述

可以看到结果中增加了文件名和行号：



请点击输入图片描述

现在我们可以在输出中找一下统计表相关的信息：



请点击输入图片描述

可以看到 MySQL 在这里非常机智，直接执行了一个内置的存储过程来更新统计表。

沿着 que_eval_sql，可以找到其他类似的统计表，比如下面这些：



请点击输入图片描述



请点击输入图片描述

本次实验中，我们借助了 MySQL 的 DBUG 包，来让 MySQL 将处理过程暴露出来。MySQL 中类似的技术还有不少，比如 performance_schema，OPTIMIZER_TRACE 等等。

这些技术将 MySQL 的不同方向的信息暴露出来，方便大家理解其中机制。

Answer 2

   select  nameid,name,COUNT(*) namecount from (
   select a.nameid,a.name,LOCATE(concat(',',a.name,','), concat(',',b.name,',')) from  table1 a ,table2 b where LOCATE(concat(',',a.name,','), concat(',',b.name,','))>0
   ) t group by nameid, name order by nameid

 

Answer 3

表结构不合理，使用SQL查询效率不高；

解决办法有多种，介绍两种：
第1种 ：修改表2的结构
1 张三 1
2 李四 1

3 王二 1
4 王二 2
5 赵五 2
6 张三 3
7 王二 3
查询Sql: select nameid ,name, count(*) from table2 a left join table 1 b on a.name = b.name group by a.nameid

第2种 ： 数据量较多修改数据结构复杂时使用
把数据库数据遍历一遍，生成log文件

第3种： 使用其它搜索引擎，如coreseek

------------第2种方法具体内容----------------------
表结构固定的情况下：

只能扫描table2,把数据一行一行搞出来了；
function getdata($i){
$sql = "select id ,name from table2 where id =".$i;

/*mysql_connect();
mysql_query();

$row = mysql_fetch_array();

此部他请参考手册补全
*/

return $row;

}
$formatRet = array();
for($i = 1; $i< 1000; $i++){ //将1000改成你数据库中最大的ID
$row = getdata($i);

$nameArr = explode(',', $row['name']);
foreach($nameArr as $key => $v) {
$formatRet[$v]++;
}
}
asort($formatRet);
foreach($formatRet as $k => $val) {
echo "\r\n".$k." :".$val;

}

Answer 4

试下这样吧
select nameid, name, (select count(1) from table2 t2 where t2. name like '%' || t1.name || '%') as namecount from table1 t1