错误 msg 获取子查询返回了超过 1 个值. 此查询中的错误是什么

Question

INSERT INTO [BOOnboardingStatus]
(BusinessID,
SectionID,
SectionName,
SubSectionID,
SubSectionName,
status,
createdby,
modifieddate,
modifiedby,
source,
createddate)
SELECT contentid,
2,
'Additional Info',
'22',
'Add keywords',
(SELECT CASE
WHEN categoryname IS NOT NULL THEN 1
ELSE 0
END
FROM   businesscategory
WHERE  businessid IN(SELECT contentid
FROM   businessmaster
WHERE  isclaim = 1)
AND [primary] = 1),
1,
Getdate(),
0,
'BOD',
Getdate()
FROM   businessmaster
WHERE  isclaim = 1 

Answer 1

大概，你需要一个相关的子查询：

SELECT contentid, 2, 'Additional Info', '22', 'Add keywords',
(SELECT CASE WHEN categoryname IS NOT NULL THEN 1
ELSE 0
END
FROM businesscategory bc
WHERE bc.businessid = bm.contentid AND
bc.[primary] = 1
),
1, Getdate(), 0, 'BOD', Getdate()
FROM businessmaster bm
WHERE isclaim = 1 ;

编辑：

也许EXISTS做你想做的事。 它肯定会摆脱错误：

SELECT contentid, 2, 'Additional Info', '22', 'Add keywords',
(CASE WHEN EXISTS (SELECT 1
FROM businesscategory bc
WHERE bc.businessid = bm.contentid AND
bc.[primary] = 1
)
THEN 1 ELSE 0
END),
1, Getdate(), 0, 'BOD', Getdate()
FROM businessmaster bm
WHERE isclaim = 1 ;