如何获取一个表的ID
select * from PropertyValues where Id in (select Valuelist from ProductPropertys where Valuelist is not null)
"select Valuelist from ProductPropertys where Valuelist is not null"
ValueList存的Nvarchare是4,6,7,104,122
报错：在将 nvarchar 值 '4,6,7,104,122' 转换成数据类型 int 时失败。 sqlserver
[解决办法]
这个查询写的相当烂
[解决办法]
自己改改吧，看上去设计也很烂
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
id int,
name varchar(10),
[key] varchar(20)
)
go
insert test
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go
select
id,
a.name,
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<len([key])
and type='p'
and substring(','+[key],number,1)=','
/*
id name key
-----------------------------
1 lisa li
1 lisa is
1 lisa sa
2 sophia ab
2 sophia cd
2 sophia ef
3 lori 12
3 lori 34
3 lori 23
*/
--很少见到有人写这样的方法 试了一下 可以实现select *
from PropertyValues T1
where EXISTS(
select 1 from ProductPropertys T2
where ','+T2.Valuelist+',' LIKE ','+LTRIM(T1.ID)+','
)
select a.* from PropertyValues,ProductPropertys
where charindex(id,Valuelist)!=0