求SQL语句(50分)

--create table #A(Item Int,value varchar(50))
--create table #B(Item int,Number varchar(50))
TRUNCATE TABLE #a
TRUNCATE TABLE #b
insert into #A values (1,'1-2')
insert into #A values (2,'1-2-3')
insert into #A values (3,'1-2-3-4')
insert into #A values (4,'1-2-3-4-5')
insert into #A values (5,'2-3')
insert into #A values (6,'2-3-4')
insert into #A values (7,'2-3-4-5')
insert into #A values (8,'3-4')
insert into #A values (9,'3-4-5')
insert into #A values (10,'4-5')
INSERT INTO #B values (1,'1-2-3-5')
insert into #b values (2,'1-2-4-5')
--select #A.Item,#A.value,count(*) as countA From #A,#B where #B.Number like '%'+#A.Value+'%'
group by #A.Item,#A.value
用SQL语句查询出Number字段中包含最完整的Value字段，个数。也就是查询出来的结果是
Item Values CountA
1 '1-2-3' 1
2 '1-2 4-5' 2

楼主，不明白你描述的是什么意思，'1-2-3'怎么会是1次，应该是3次吧，
不然你'1-2-3-4' 2次是怎么来的

select A.Item,A.values,count(*) as countA From,B where B.Number like '%'+A.Value+'%'
group by A.Item,A.values

to xiongw:最后一项的'1-2 3-4'写错了,是'1-2 4-5'
to xinjinren:我先试试

to xinjinren:得到的结果不是我想要的.假如Number值是'1-2-3-5'的时候,得到的结果是
1 1-2 1
2 1-2-3 1
5 2-3 1
我想得到的结果仅第二行,这才是完全匹配的值.
假如Number值是'1-2-3-5'和'1-2-4-5'时,得到的结果是
1 1-2 2
2 1-2-3 1
5 2-3 1
10 4-5 1
还是多出了刚才所指的.

--create table #A(Item Int,value varchar(50))
--create table #B(Item int,Number varchar(50))
TRUNCATE TABLE #a
TRUNCATE TABLE #b
insert into #A values (1,'1-2')
insert into #A values (2,'1-2-3')
insert into #A values (3,'1-2-3-4')
insert into #A values (4,'1-2-3-4-5')
insert into #A values (5,'2-3')
insert into #A values (6,'2-3-4')
insert into #A values (7,'2-3-4-5')
insert into #A values (8,'3-4')
insert into #A values (9,'3-4-5')
insert into #A values (10,'4-5')
INSERT INTO #B values (1,'1-2-3-5')
insert into #B values (2,'1-2-4-5')
select x.Item,x.value,x.countA from
(select A.Item,A.value,B.Number,max(len(value)) as lenv,count(*) as countA From #A A,#B B where B.Number like '%'+A.Value+'%'
group by A.Item,A.value,B.Number)as x inner join
(select B.Number,max(len(value)) as lenv From #A A,#B B where B.Number like '%'+A.Value+'%'
group by B.Number)as y ON x.Number=y.Number And x.Lenv=y.Lenv
order by x.Item
可以得到结果 不过如果你要将相同满足率的合并成一行 需要建立一个自定义函数
类似这样：
CREATE function F_tmp (@F varchar(10))
returns varchar(100)
as

begin

declare
@s varchar(100)
select @s=''
select @s=@s+' '+rtrim(isnull(字段２,'')) from 表 where 字段１=@F
return @s
end

try

值基本上已经OK,我再细看一下.