求一条SQL语句

[fadics]

一张表的字段有两项ID、keyword均可重复
想找keyword为k1或k2而ID相同的所有ID列表
也就是既包含k1又包含k2的项的ID
SQL语句应怎么写

[wgzaaa]

--表达就矛盾
select distinct id from 表 a where keyword='k1' and exists(select 0 from 表 where a.id=b.id and keyword='k2')

[dobear_0922]

select ID from tb where keyword='k1'
intersect
select ID from tb where keyword='k2'

[wgzaaa]

sorry,我理解错了，不矛盾

[dobear_0922]

create table tb(ID int, keyword varchar(8))

insert tb select 1, 'k1'
union all select 2, 'k1'
union all select 3, 'k1'
union all select 2, 'k2'
union all select 3, 'k2'
union all select 4, 'k2'

select ID from tb where keyword='k1'
intersect
select ID from tb where keyword='k2'

/*
ID
-----------
2
3

(2 row(s) affected)
*/


drop table tb

[dawugui]

[Quote=引用楼主 fadics 的帖子:]
一张表的字段有两项ID、keyword均可重复
想找keyword为k1或k2而ID相同的所有ID列表
也就是既包含k1又包含k2的项的ID
SQL语句应怎么写
[/Quote]

select id from 
(
  select distinct id from tb where keyword = 'k1'
  union all
  select distinct id from tb where keyword = 'k2'
) t
group by id having count(*) = 2

[fadics]

to dobear_0922
无法执行啊!

在关键字intersect附近有语法错误

[wzy_love_sly]

create table tb(id int,keyword varchar(50))
insert into tb select 1,'k1'
insert into tb select 1,'k2'
insert into tb select 2,'k1'
insert into tb select 2,'k1'
insert into tb select 3,'k2'
insert into tb select 3,'k2'
select * from tb where id in(
select id from (
select distinct * from tb where keyword='k1'
union all
select distinct * from tb where keyword='k2')t
group by id having count(1)>1)

id keyword
1 k1
1 k2

[fadics]

[Quote=引用 5 楼 dawugui 的回复:]
引用楼主 fadics 的帖子:
一张表的字段有两项ID、keyword均可重复
想找keyword为k1或k2而ID相同的所有ID列表
也就是既包含k1又包含k2的项的ID
SQL语句应怎么写




SQL codeselect id from
(
select distinct id from tb where keyword = 'k1'
union all
select distinct id from tb where keyword = 'k2'
) t
group by id having count(*) = 2
[/Quote]
也无法执行

[dreamice01]

这样可以实现吗？

select id from table where keyword='k2' and id in (select id from table where keyword='k1')

[fadics]

[Quote=引用 7 楼 wzy_love_sly 的回复:]
SQL codecreate table tb(id int,keyword varchar(50))
insert into tb select 1,'k1'
insert into tb select 1,'k2'
insert into tb select 2,'k1'
insert into tb select 2,'k1'
insert into tb select 3,'k2'
insert into tb select 3,'k2'
select * from tb where id in(
select id from (
select distinct * from tb where keyword='k1'
union all
select distinct * from tb where keyword='k2')t
group by id…
[/Quote]

是MS SQL不支持union

[fadics]

[Quote=引用 9 楼 dreamice01 的回复:]
这样可以实现吗？

select id from table where keyword='k2' and id in (select id from table where keyword='k1')
[/Quote]
对,这样估计可以,我再试试看,谢谢

[zhiguo2008]

[Quote=引用 4 楼 dobear_0922 的回复:]
SQL codecreate table tb(ID int, keyword varchar(8))

insert tb select 1, 'k1'
union all select 2, 'k1'
union all select 3, 'k1'
union all select 2, 'k2'
union all select 3, 'k2'
union all select 4, 'k2'

select ID from tb where keyword='k1'
intersect
select ID from tb where keyword='k2'

/*
ID
-----------
2
3

(2 row(s) affected)
*/


drop table tb
[/Quote]

支持一下

[dawugui]

[Quote=引用 10 楼 fadics 的回复:]
引用 7 楼 wzy_love_sly 的回复:
SQL codecreate table tb(id int,keyword varchar(50))
insert into tb select 1,'k1'
insert into tb select 1,'k2'
insert into tb select 2,'k1'
insert into tb select 2,'k1'
insert into tb select 3,'k2'
insert into tb select 3,'k2'
select * from tb where id in(
select id from (
select distinct * from tb where keyword='k1'
union all
select distinct …

是MS SQL不支持union
[/Quote]

create table tb(ID int, keyword varchar(8))

insert tb select 1, 'k1'
union all select 2, 'k1'
union all select 3, 'k1'
union all select 2, 'k2'
union all select 3, 'k2'
union all select 4, 'k2'

select id from 
(
  select distinct id from tb where keyword = 'k1'
  union all
  select distinct id from tb where keyword = 'k2'
) t
group by id having count(*) = 2


drop table tb

/*
id          
----------- 
2
3

（所影响的行数为 2 行）
*/

[wzy_love_sly]

ms sql?你说是my sql吧！

[-狙击手-]

[Quote=引用 14 楼 wzy_love_sly 的回复:]
ms sql?你说是my sql吧！
[/Quote]

估计 是

[fadics]

最后用的是
select ID from table_name
where (keyword='k1') or
(keyword='k2')
group by ID
having (count(ID)>=2)

谢谢大家