27,580
社区成员
发帖
与我相关
我的任务
分享
select
a.name,count(1) as 次数
from
a,b
where
charindex(','+ltrim(a.id)+',',','+b.col+',')>0
group by
a.name
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-04 10:20:37
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [ta]
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta] ([ID] [int],[Name] [nvarchar](10))
INSERT INTO [ta]
SELECT '1','产品A' UNION ALL
SELECT '2','产品B' UNION ALL
SELECT '3','产品C'
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[col] [nvarchar](20))
INSERT INTO [tb]
SELECT '1','1,2,3,4,20,21,22' UNION ALL
SELECT '2','2,3,4,5,6' UNION ALL
SELECT '3','5,6,7'
--SELECT * FROM [ta]
--SELECT * FROM [tb]
-->SQL查询如下:
select a.name 产品名称,count(1) as 遍历次数
from ta a
join tb b
on charindex(','+ltrim(a.id)+',',','+b.col+',')>0
group by a.name
/*
产品名称 遍历次数
---------- -----------
产品A 1
产品B 2
产品C 2
(3 行受影响)
*/
select a.name,count(1) as 遍历次数
from ta a
join tb b
on charindex(','+ltrim(a.id)+',',','+b.col+',')>0
group by a.name