如果把一篇文章用分词函数 分成很多个单一的词组,记录到一个表中
结构如下
ID 词 文章ID
---------------------
1 分词1 1
2 分词2 1
3 分词1 2
4 分词3 2
N ......现在要用SQL语句把 同一文章中包含 分词1 AND 分词2 的 文章ID取得不重复
注意 这样是错误的 Select DISTINCT 文章ID FORM TABLE_NAME WHERE 词='分词1' or 词='分词2' 请问这样的SQL语句要如何写 ??
结构如下
ID 词 文章ID
---------------------
1 分词1 1
2 分词2 1
3 分词1 2
4 分词3 2
N ......现在要用SQL语句把 同一文章中包含 分词1 AND 分词2 的 文章ID取得不重复
注意 这样是错误的 Select DISTINCT 文章ID FORM TABLE_NAME WHERE 词='分词1' or 词='分词2' 请问这样的SQL语句要如何写 ??
t.文章ID
from
表 t
where
t.词='分词1'
and
exists(select 1 from 表 where 文章ID=t.文章ID and 词='分词2')
group by id
(
select distinct 词,文章ID from tb where 词 = '分词1'
union all
select distinct 词,文章ID from tb where 词 = '分词2'
) t
group by 词,文章ID
having count(*) = 2
(
select distinct 词,文章ID from tb where 词 = '分词1'
union all
select distinct 词,文章ID from tb where 词 = '分词2'
union all
select distinct 词,文章ID from tb where 词 = '分词3'
) t
group by 词,文章ID
having count(*) = 3
a.文章ID
from
(select 文章ID from 表 where 词='分词1') a,
(select 文章ID from 表 where 词='分词2') b
where
a.文章ID=b.文章ID
(select 文章ID from TABLE_NAME WHERE 词='分词1') as T1 join
(select 文章ID from TABLE_NAME WHERE 词='分词2') as T2 on T1.文章ID = T2.文章ID
------------------
那楼主要的是什么结果啊?要的是这样?????文章ID
1
如要查询4个关键字??
(select 文章ID from TABLE_NAME WHERE 词='分词1') as T1 join
(select 文章ID from TABLE_NAME WHERE 词='分词2') as T2 on T1.文章ID = T2.文章ID
inner join (select id,词 from #t where 词='分词2') b on a.id=b.id
inner join (select id,词 from #t where 词='分词3') c on a.id=c.id
inner join (select id,词 from #t where 词='分词4') d on a.id=d.id
如要查询4个关键字??
-----------------------
select DISTINCT T1.文章ID from
(select 文章ID from TABLE_NAME WHERE 词='分词1') as T1
join (select 文章ID from TABLE_NAME WHERE 词='分词2') as T2 on T1.文章ID = T2.文章ID
join (select 文章ID from TABLE_NAME WHERE 词='分词3') as T3 on T1.文章ID = T3.文章ID
join (select 文章ID from TABLE_NAME WHERE 词='分词4') as T4 on T1.文章ID = T4.文章ID
...
where a.文章ID in (select 文章ID from TABLE_NAME group by 文章ID
having sum(case 词 when ‘分词1’then 1 else 0 end)>0 and
sum(case 词 when ‘分词2’then 1 else 0 end)>0)
http://community.csdn.net/Expert/topic/5746/5746398.xml?temp=.399563
group by 文章ID如果数据量大的话,建议建立统计表
where 词='分词1' and 文章id=a.文章id) and exists(select * from tablename
where 词='分词2' and 文章id=a.文章id)
FORM TABLE_NAME t1 ,TABLE_NAME t2 WHERE t1.词='分词1'
and t2.词='分词2'
and t1.文章ID =t2.文章ID