表如下
ItemId SpecId Type SpecValue SpecRe1 29 1 d2691e 巧克力色
2 29 2 XS XS
3 30 1 d2691e 巧克力色
4 30 2 M M
5 31 1 bdb76b 深卡其布色
6 31 2 XS XS
7 32 1 bdb76b 深卡其布色
8 32 2 M M
9 33 1 990000 酒红色
10 33 2 XS XS
11 34 1 990000 酒红色
12 34 2 M M
-----------------------根据给的参数(逗号分隔的ItemId)返回它们相同的SpecId,如果参数组合的SpecId不相同,则返回0.举例:参数 1,2 返回 SpecId 29 ; 参数9,10 返回 SpecId 33 ; 参数3,5 返回 SpecId 0 (ItemId3、5的SpecId值不同)这样描述,大家明白吗?
ItemId SpecId Type SpecValue SpecRe1 29 1 d2691e 巧克力色
2 29 2 XS XS
3 30 1 d2691e 巧克力色
4 30 2 M M
5 31 1 bdb76b 深卡其布色
6 31 2 XS XS
7 32 1 bdb76b 深卡其布色
8 32 2 M M
9 33 1 990000 酒红色
10 33 2 XS XS
11 34 1 990000 酒红色
12 34 2 M M
-----------------------根据给的参数(逗号分隔的ItemId)返回它们相同的SpecId,如果参数组合的SpecId不相同,则返回0.举例:参数 1,2 返回 SpecId 29 ; 参数9,10 返回 SpecId 33 ; 参数3,5 返回 SpecId 0 (ItemId3、5的SpecId值不同)这样描述,大家明白吗?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ItemId] int,[SpecId] int,[Type] int,[SpecValue] varchar(6),[SpecRe] varchar(10))
insert [tb]
select 1,29,1,'d2691e','巧克力色' union all
select 2,29,2,'XS','XS' union all
select 3,30,1,'d2691e','巧克力色' union all
select 4,30,2,'M','M' union all
select 5,31,1,'bdb76b','深卡其布色' union all
select 6,31,2,'XS','XS' union all
select 7,32,1,'bdb76b','深卡其布色' union all
select 8,32,2,'M','M' union all
select 9,33,1,'990000','酒红色' union all
select 10,33,2,'XS','XS' union all
select 11,34,1,'990000','酒红色' union all
select 12,34,2,'M','M'
declare @ItemId varchar(50),@SpecId int
set @ItemId='1,2'
set @SpecId=isnull((
select count(distinct specid)
from tb
where charindex(','+ltrim(ItemId)+',',','+@ItemId+',')>0
having count(distinct specid)=1),0)
select @SpecId
GO
create table T(ItemId int, SpecId int, Type int)
insert into T select 1, 29, 1
insert into T select 2, 29, 2
insert into T select 3, 30, 1
insert into T select 4, 30 ,2
insert into T select 5, 31 ,1
insert into T select 6, 31, 2
insert into T select 7, 32, 1
insert into T select 8, 32, 2
insert into T select 9, 33, 1
insert into T select 10 ,33, 2
insert into T select 11, 34, 1
insert into T select 12, 34, 2
GO
Create function dbo.fn_test(@input varchar(20))
returns int
AS
begin
declare @return int
set @return = isnull((select max(specid) from T where charindex(','+rtrim(ItemId)+',' , ','+rtrim(@input)+',')>0 having count(distinct specid)=1),0)return @return
endGOselect dbo.fn_test('1,2'), dbo.fn_test('9,10'),dbo.fn_test('3,5')
/*
29 33 0
*/GO
drop function dbo.fn_test
drop table T
from [Table]
where charindex(','+ltrim(ItemId)+',',','+参数+',')>0
group by SpecId
declare @ItemId varchar(50),@SpecId int
set @ItemId='1,2'
set @SpecId=isnull((
select min(specid)
from tb
where charindex(','+ltrim(ItemId)+',',','+@ItemId+',')>0
having count(distinct specid)=1),0)
select @SpecId
v_ItemId varchar
)
return NUMBER
IS
n_SpecId number;
n_num number;
BEGIN
n_SpecId:= 0;
select count(distinct SpecId) into n_num from 表名
where charindex(','+to_char(ItemId)+',',','+v_ItemId+',')>0;
if n_num =1 then
select distinct SpecId into n_SpecId from 表名
where charindex(','+to_char(ItemId)+',',','+v_ItemId+',')>0;
end if;
return(n_SpecId);
end Get_SpecId;