select b.mc from tb b ,ta a where charindex(','+ltrim(b.mc)+',',','+a.mc+',')>0
select distinct b.mc from b,a where charindex(b.mc,a.mc)
select * from b where exists(select 1 from a where charindex(','+ltrim(b.mc)+',',','+mc+',')>0)
create table #A ( mc nvarchar(50) ) insert into #A select '1,3,4' insert into #A select '7,9,4' create table #B ( mc nvarchar(50) ) insert into #B select '1' insert into #B select '4' insert into #B select '7'select distinct b.mc from #B b,#A a where charindex(b.mc,','+a.mc+',')>0mc -------------------------------------------------- 1 4 7(3 行受影响)
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-16 14:01:28 -- 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)------------------------------------------------------------------*/ --> 生成测试数据表:aIF OBJECT_ID('[a]') IS NOT NULL DROP TABLE [a] GO CREATE TABLE [a]([mc] NVARCHAR(10)) INSERT [a] SELECT '1,3,4' UNION ALL SELECT '7,9,4' GO --SELECT * FROM [a]--> 生成测试数据表:bIF OBJECT_ID('[b]') IS NOT NULL DROP TABLE [b] GO CREATE TABLE [b]([mc] INT) INSERT [b] SELECT 1 UNION ALL SELECT 4 UNION ALL SELECT 7 GO --SELECT * FROM [b]-->SQL查询如下: select * from b where exists(select 1 from a where charindex(','+ltrim(b.mc)+',',','+mc+',')>0) /* mc ----------- 1 4 7(3 行受影响) */1也相同吧
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-16 14:00:08 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) -- May 26 2009 14:24:20 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([mc] varchar(5)) insert [a] select '1,3,4' union all select '7,9,4' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([mc] int) insert [b] select 1 union all select 4 union all select 7 --------------开始查询-------------------------- select distinct b.* from a,b where charindex(','+ltrim(b.mc)+',',','+a.mc+',')>0select * from b where exists(select 1 from a where charindex(','+ltrim(b.mc)+',',','+mc+',')>0) ----------------结果---------------------------- /* mc ----------- 1 4 7*/
select * from b where exists (select * from a where charindex(b.mc,a.mc)>0)
from tb b ,ta a
where charindex(','+ltrim(b.mc)+',',','+a.mc+',')>0
from b,a
where charindex(b.mc,a.mc)
(
mc nvarchar(50)
)
insert into #A select '1,3,4'
insert into #A select '7,9,4'
create table #B
(
mc nvarchar(50)
)
insert into #B select '1'
insert into #B select '4'
insert into #B select '7'select distinct b.mc from #B b,#A a where charindex(b.mc,','+a.mc+',')>0mc
--------------------------------------------------
1
4
7(3 行受影响)
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-16 14:01:28
-- 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)------------------------------------------------------------------*/
--> 生成测试数据表:aIF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([mc] NVARCHAR(10))
INSERT [a]
SELECT '1,3,4' UNION ALL
SELECT '7,9,4'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([mc] INT)
INSERT [b]
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 7
GO
--SELECT * FROM [b]-->SQL查询如下:
select * from b where exists(select 1 from a where charindex(','+ltrim(b.mc)+',',','+mc+',')>0)
/*
mc
-----------
1
4
7(3 行受影响)
*/1也相同吧
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-16 14:00:08
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([mc] varchar(5))
insert [a]
select '1,3,4' union all
select '7,9,4'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([mc] int)
insert [b]
select 1 union all
select 4 union all
select 7
--------------开始查询--------------------------
select distinct b.* from a,b where charindex(','+ltrim(b.mc)+',',','+a.mc+',')>0select * from b where exists(select 1 from a where charindex(','+ltrim(b.mc)+',',','+mc+',')>0)
----------------结果----------------------------
/* mc
-----------
1
4
7*/