在线急等,关联查询 速度结贴,该怎么处理
在线急等,关联查询 速度结贴
表Collect
ID Name
1 aa
2 bb
100 ...
200 ...
表Tag
ID collectlist
1 1,2,3,4,,5
根据Tag的ID,查出Collect表
结果 如Tag的ID=1
-------------------------------------
ID Name
1 aa
2 bb
------解决思路----------------------
------解决思路----------------------
有点问题,调整如下
------解决思路----------------------
select a.* from Collect as a where ','+cast(ID as varchar(20)) +',' like '%,'+(select collectlist from Tag where ID=1)+',%'
------解决思路----------------------
select b.* from
(select distinct SUBSTRING(collectlist,number,charindex(',',collectlist+',',number)-number) as col
from tag a,master..spt_values b where number>=1 and number<=LEN(collectlist) and type='P'
and SUBSTRING(','+collectlist,number,1)=',') a,collect b where a.col=b.id
表Collect
ID Name
1 aa
2 bb
100 ...
200 ...
表Tag
ID collectlist
1 1,2,3,4,,5
根据Tag的ID,查出Collect表
结果 如Tag的ID=1
-------------------------------------
ID Name
1 aa
2 bb
------解决思路----------------------
select B.* from Tag A LEFT JOIN Collect B ON CHARINDEX(','+CAST(B.ID AS VARCHAR)+',',A.collectlist)>0 WHERE A.ID=1
------解决思路----------------------
有点问题,调整如下
select B.* from Tag A LEFT JOIN Collect B ON CHARINDEX(','+CAST(B.ID AS VARCHAR)+',',','+A.collectlist+',')>0 WHERE A.ID=1
------解决思路----------------------
select a.* from Collect as a where ','+cast(ID as varchar(20)) +',' like '%,'+(select collectlist from Tag where ID=1)+',%'
------解决思路----------------------
select b.* from
(select distinct SUBSTRING(collectlist,number,charindex(',',collectlist+',',number)-number) as col
from tag a,master..spt_values b where number>=1 and number<=LEN(collectlist) and type='P'
and SUBSTRING(','+collectlist,number,1)=',') a,collect b where a.col=b.id