大家好。
现在我的MYSQL有一个表,结构为:
tb(name, content)
name和content均为text型,content中出现name的次数大于等于0次,其中的记录摘录如下:("zhangsan", "Good morning, this is zhangsan speaking");
("lisi", "Good morning, zhangsan said hello to everybody I know");
("wanglaowu", "It has already been afternoon now, why zhangsan and lisi said 'good morning'?");
("whoever", "I met a son of a bxxch yesterday, that made me angry");
我有一个这样的查询需求:
从tb中查询出所有的name,这些name值不出现在任何的content中求问各路神明这该如何做到呢,数据很多,不能写像:select count(distinct name) cnt
from tb
where content like '%name1%'这样的语句一条一条查count吧T_T… (name1 is only one of the elements in tb :(
现在我的MYSQL有一个表,结构为:
tb(name, content)
name和content均为text型,content中出现name的次数大于等于0次,其中的记录摘录如下:("zhangsan", "Good morning, this is zhangsan speaking");
("lisi", "Good morning, zhangsan said hello to everybody I know");
("wanglaowu", "It has already been afternoon now, why zhangsan and lisi said 'good morning'?");
("whoever", "I met a son of a bxxch yesterday, that made me angry");
我有一个这样的查询需求:
从tb中查询出所有的name,这些name值不出现在任何的content中求问各路神明这该如何做到呢,数据很多,不能写像:select count(distinct name) cnt
from tb
where content like '%name1%'这样的语句一条一条查count吧T_T… (name1 is only one of the elements in tb :(
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
我的MYSQL 5有这样一个表:tb(name, content)
create table tb (
name text,
content text
)表中有这样的数据:insert into tb values ("zhangsan", "Good morning, this is <strong>zhangsan</strong> speaking")
insert into tb values ("lisi", "Good morning, <strong>zhangsan</strong> said hello to everybody I know")
insert into tb values ("wanglaowu", "It has already been afternoon now, why <strong>zhangsan</strong> and <strong>lisi</strong> said 'good morning'?")
insert into tb values ("whoever", "I met a son of a bxxch yesterday, that made me angry")我想得到这样的结果:从tb中查询出所有的name,这些name值不出现在任何的content中
如下:name
"wanglaowu"
"whoever"
求教各位了!
from tb t
where not exists (select 1 from tb where content like concat('%',t.name,'%'))