SQL难题求解

[chx007]

已知表t 两个字段 id, val
记录列表
id val
1 a
1 b
2 a
2 b
3 a
3 c
4 a
4 c
要求通过sql语句得出val都相同的id序列
结果集为
id_list
1,2
3,4
有点像关系运算里的除法运算，当结果可以其他形式给出，只要能分别得出 a,b 与 a,c 各自对应的id序列就行

[chx007]

to: lsxaa(小李铅笔刀) (
我用以下语句可以得出
1 a,b
2 a,b
3 a,c
4 a,c

create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+',' + ltrim( rtrim( val ) ) from t where id=@id order by val
return (substring(@str,2,len(@str)))
end

select id,dbo.f_str(id) as col1
from t
group by id
order by col1


但你的第二句中select @str=@str+','+val from groupaa where id=@id order by val
from groupaa是指什么？？

[lsxaa]

把id等于@id值的val 做一个字符串连接,结果赋值给@str变量

[chx007]

但你的第二句中
select @str=@str+','+val from groupaa where id=@id order by val
from groupaa是指什么？？ ~~~~~~~
要改为才能运行
select @str=@str+','+val from t where id=@id order by val

如果要显示成楼主要效果，可以按照上面的方法，再对结果集进行筛选
结果集是否要存入临时表???
是否可以不用函数？？

[lsxaa]

呵呵 groupaa 是我自己建的表做的测试
可以用临时表
要建一个功能相似的函数，只不过这个累加的是id 而传入的值是val

[chx007]

create function dbo.f_str(@id char)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+',' + ltrim( rtrim( val ) )
from t where id=@id
order by val
return (substring(@str,2,len(@str)))
end

create function dbo.f_id_str(@vals char )
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str = @str + ',' + ltrim( rtrim( [id] ) )
from
(
select [id], dbo.f_str( id ) as vals
from t
group by [id]
) a
where vals=@vals
order by is
return (substring(@str,2,len(@str)))
end

select vals, dbo.f_id_str( vals ) as ids
from
(
select id, dbo.f_str( id ) as vals
from t
group by id
) as x
group by vals

结果:
vals ids
a,b
a,c

为何ids都是为空?? 一定要用临时表吗?

[flyorgao]

select id, sum(1) as count from tb group by id

[chx007]

搞定！
将dbo.f_str(@id char) 改为 dbo.f_str(@id varchar( 500 ))
dbo.f_id_str(@vals char) 改为 dbo.f_id_str(@vals varchar( 500 ) )
即可
结果:
vals ids
a,b 1,2
a,c 3,4


create function dbo.f_str(@id varchar( 500 ))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+',' + ltrim( rtrim( val ) )
from t where id=@id
order by val
return (substring(@str,2,len(@str)))
end

create function dbo.f_id_str(@vals varchar( 500 ) )
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str = @str + ',' + ltrim( rtrim( [id] ) )
from
(
select [id], dbo.f_str( id ) as vals
from t
group by [id]
) a
where vals=@vals
order by is
return (substring(@str,2,len(@str)))
end

select vals, dbo.f_id_str( vals ) as ids
from
(
select id, dbo.f_str( id ) as vals
from t
group by id
) as x
group by vals

[lsxaa]

长度不够

[chx007]

这是另一解:

select lid,rid
from
(
select a.id as lid, b.id as rid, count(b.id) as number
from t as a inner join t as b
on a.val = b.val
group by a.id,b.id

) result

inner join

(select id,count(id) as number from t group by id) as lidnumber

on result.lid = lidnumber.id and result.number = lidnumber.number

inner join

(select id,count(id) as number from t group by id) as ridnumber

on result.rid = ridnumber.id and result.number = ridnumber.number
group by


lid rid

1 1
1 2
2 1
2 2
3 3
3 4
4 3
4 4

列表得出的是各个val都相同的id对应关系

[Andy__Huang]

select id,count(*) as val from tb group by id

[lsxaa]

没测试，楼主自己测一下 这样可以把相同值的显示在一起
create function f_str(@id int)
resturns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=@str+','+val from t where id=@id order by val
return(substring(@str,2,len(@str))
end

select id,dbo.f_str(id) as col1
from t
group by id
order by col1

[lsxaa]

上面有错误，改一下
create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+val from groupaa where id=@id order by val
return (substring(@str,2,len(@str)))
end

select id,dbo.f_str(id) as col1
from t
group by id
order by col1

结果是这样的
1 a,b
2 a,b
3 a,c
4 a,c

[lsxaa]

如果要显示成楼主要效果，可以按照上面的方法，再对结果集进行筛选