求一条SQL语句

[juner77617]

NAME BOOK
张三 A
李四 A
李四 B
王五 A
王五 B
希望得到的结果：
李四,王五

也就是说，希望得到同时有两本书的人员名单列表

谢谢大家，没分了。

[duoxu1983]

select distinct a.name from table a
where exists (select 1 from table b where a.name=b.name and a.book<>b.book)

[jxjdzwang5555]

sele NAME,count(book) from tt group by NAME having count(book)>=2?

但不能区分2本相同的书？

[xuam]

select [name] from Table group by book having count(1)=2  

[jxjdzwang5555]

[Quote=引用 3 楼 xuam 的回复:]
SQL code
select [name] from Table group by book having count(1)=2
[/Quote]
count(1)这里的1是指什么？谢谢。

[fengzheng1987]

select [Name] from Te group by [Name] having count(*)=2

[juner77617]

先谢谢各位！题目没有表达有点出入，希望得到的应该是以逗号分开的人员名单字符串。并不是列表。抱歉。

[xuam]

[Quote=引用 4 楼 jxjdzwang5555 的回复:]
引用 3 楼 xuam 的回复:
SQL code
select [name] from Table group by book having count(1)=2

count(1)这里的1是指什么？谢谢。
[/Quote]

select [name] from T group by [name] having count(1)=2

1和* 一样，就是计数而已.

[rubingwoxin]

select distinct a.name from test a
inner join test b on a.name=b.name and a.book<>b.book

[geniuswjt]

搜索：合并列值

[juner77617]

SELECT [VALUE]= 
STUFF((
SELECT ','+ A.NAME from TABLE A group by A.NAME having count(1)=2 FOR XML PATH('')),1,1,'')

参照大家的提供的语句，根据需求自己写了一个。 也不知道是不是已经足够的简单。
谢谢大家。

[fengzheng1987]

你可以先求出列表，然后把列表再拼成字符串，用逗号隔开

[pengxuan]

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 name varchar(10),
 book varchar(10)
)
go
insert into tb
select '张三','' union all
select '李四','A' union all
select '李四','B' union all
select '王五','A' union all
select '王五','B'
go
;with cte (f1) as
(
 select ','+name from tb group by name having count(1)=2 for xml path('')
)
select stuff(f1,1,1,'') from cte
go

[chtzhking]

[Quote=引用 10 楼 juner77617 的回复:]

SQL code
SELECT [VALUE]=
STUFF((
SELECT ','+ A.NAME from TABLE A group by A.NAME having count(1)=2 FOR XML PATH('')),1,1,'')


参照大家的提供的语句，根据需求自己写了一个。 也不知道是不是已经足够的简单。
谢谢大家。
[/Quote]+1

[juner77617]

再次谢谢大家！~