34,588
社区成员
发帖
与我相关
我的任务
分享
--如果TEXT類型不超過8000 轉換下 cast()
if object_id('Items')iS not null drop table Items
go
create table Items(itemid int, itemName ntext)
insert Items select 1, N'名称1'
insert Items select 2, N'名称2'
insert Items select 3, N'名称3'
if object_id('Result ')iS not null drop table Result
go
CREATE TABLE Result (resultid INT, answer ntext)
INSERT Result SELECT 1 , '1,3'
INSERT Result SELECT 2 , '3'
INSERT Result SELECT 3, '2,3'
INSERT Result SELECT 4, '1,2'
SELECT I.itemid, I.itemName,(SELECT COUNT(*) FROM Result R WHERE CHARINDEX(','+RTRIM(I.ITEMID)+',',','+cast(R.answer as nvarchar(4000))+',')>0) FROM ITEMS I
/*itemid itemName
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1 名称1 2
2 名称2 2
3 名称3 3
*/
--text-》varchar
select
itemid,
itemName ,
num =(select count(1) my_count from Result where charindex(','+cast(answer as varchar(8000)) + ',',','+itemid+ ',') > 0 )
from Items
if object_id('Items')iS not null drop table Items
go
create table Items(itemid int, itemName nvarchar(10))
insert Items select 1, N'名称1'
insert Items select 2, N'名称2'
insert Items select 3, N'名称3'
if object_id('Result ')iS not null drop table Result
go
CREATE TABLE Result (resultid INT, answer VARCHAR(10))
INSERT Result SELECT 1 , '1,3'
INSERT Result SELECT 2 , '3'
INSERT Result SELECT 3, '2,3'
INSERT Result SELECT 4, '1,2'
SELECT I.itemid, I.itemName,(SELECT COUNT(*) FROM Result R WHERE CHARINDEX(','+RTRIM(I.ITEMID)+',',','+R.answer+',')>0) FROM ITEMS I
/*itemid itemName
----------- ---------- -----------
1 名称1 2
2 名称2 2
3 名称3 3*/
select
itemid,
itemName ,
num =(select count(1) my_count from Result where charindex(','+answer + ',',','+itemid+ ',') > 0 )
from Items
select
itemid,
itemName ,
num =(select count(1) my_count from Result where charindex(','+answer + ',',itemid) > 0 )
from Items