我在A表中一字段 记录了B表中的主键集合
Id TypeIDS
1 1,3,5
B表中有以下记录
TypeIDS TypeName
1 杭州
2 上海
3 北京
4 武汉
5 郑州求一sql语句,取A表中Id为1的 得到的结果为:
Id TypeIDS TypeName
1 1 杭州
1 3 北京
1 5 郑州谢谢大家!
Id TypeIDS
1 1,3,5
B表中有以下记录
TypeIDS TypeName
1 杭州
2 上海
3 北京
4 武汉
5 郑州求一sql语句,取A表中Id为1的 得到的结果为:
Id TypeIDS TypeName
1 1 杭州
1 3 北京
1 5 郑州谢谢大家!
解决方案 »
- vc 连接msde 数据库
- 求sql语句
- 记录多时字段多和少查询时速度上有没有区别?谁精通
- 如何将oracle的日期类型转换为sql server2005的类型(谢谢)
- Sql server 2000中什么关键字标志着处理的结束。(在线急等)
- SQL server交叉表查询结果能否以表格或是View的形式保存下来
- 一个简单存储过程为什么不对呢,把一个表中的所有null替换成'',或者0
- SQL中的NF
- 將整型轉為字串的問題,如將12轉為'0012' ,convert(varchar(4),12)不起作用?得到'12'
- 怎样把只有.mdf和.ldf的文件恢复到数据库中
- 求一条SQL语句 谢谢
- 菜鸟请教:求一SQL语句,关于数量的合计。。。
where charindex(','+TypeIDS+',' ,(select ID from A where ID=1))>0
where charindex(','+TypeIDS+',' ,(select ','+ID+',' from A where ID=1))>0
where charindex(','+ltrim(B.TypeIDS)+',' ,','+A.TypeIDs+',')>0
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-02 14:55:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[TypeIDS] varchar(5))
insert [a]
select 1,'1,3,5'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([TypeIDS] int,[TypeName] varchar(4))
insert [b]
select 1,'杭州' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'武汉' union all
select 5,'郑州'
--------------开始查询--------------------------
;with f as
(
Select
a.id,TypeIDS=substring(a.TypeIDS,b.number,charindex(',',a.TypeIDS+',',b.number)-b.number)
from
a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.TypeIDS)
where
substring(','+a.TypeIDS,b.number,1)=','
)select f.*,b.TypeName from f,b where f.id=b.TypeIDS
----------------结果----------------------------
/* id TypeIDS TypeName
----------- ------- --------
1 1 杭州
1 3 杭州
1 5 杭州(3 行受影响)*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-02 14:55:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[TypeIDS] varchar(5))
insert [a]
select 1,'1,3,5'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([TypeIDS] int,[TypeName] varchar(4))
insert [b]
select 1,'杭州' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'武汉' union all
select 5,'郑州'
--------------开始查询--------------------------
select
Id,B.TypeIDS ,TypeName
from
A,B
where
charindex(','+ltrim(B.TypeIDS)+',' ,','+A.TypeIDs+',')>0
----------------结果----------------------------
/* id TypeIDS TypeName
----------- ------- --------
1 1 杭州
1 3 杭州
1 5 杭州(3 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-02 14:55:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[TypeIDS] varchar(5))
insert [a]
select 1,'1,3,5'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([TypeIDS] int,[TypeName] varchar(4))
insert [b]
select 1,'杭州' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'武汉' union all
select 5,'郑州'
--------------开始查询--------------------------
;with f as
(
Select
a.id,TypeIDS=substring(a.TypeIDS,b.number,charindex(',',a.TypeIDS+',',b.number)-b.number)
from
a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.TypeIDS)
where
substring(','+a.TypeIDS,b.number,1)=','
)select f.*,b.TypeName from f,b where f.TypeIDS=b.TypeIDS---方法2
select
Id,B.TypeIDS ,TypeName
from
A,B
where
charindex(','+ltrim(B.TypeIDS)+',' ,','+A.TypeIDs+',')>0 ----------------结果----------------------------
/* id TypeIDS TypeName
----------- ------- --------
1 1 杭州
1 3 北京
1 5 郑州(3 行受影响)*/
--1 1,3,5
--B表中有以下记录
--TypeIDS TypeName
-- 1 杭州
-- 2 上海
-- 3 北京
-- 4 武汉
-- 5 郑州 --求一sql语句,取A表中Id为1的 得到的结果为:
--Id TypeIDS TypeName
--1 1 杭州
--1 3 北京
--1 5 郑州
DROP function split
GO
create function split(@TypeIDs varchar(30))
returns @tmpTB table(TypeIDS tinyint)
as
begin
declare @substr varchar(30)
while(charindex(',',@TypeIDs)>0)
begin
select @substr=subString(@TypeIDs,0,charindex(',',@TypeIDs))
insert into @tmpTB select @substr
select @TypeIDs=subString(@TypeIDs,charindex(',',@TypeIDs)+1,len(@TypeIDs)-charindex(',',@TypeIDs))
end
insert into @tmpTB select @TypeIDs
return
end
go--创建表添加数据
if object_id('A') is not null drop table A
go
create table A(Id tinyint,TypeIDS varchar(30))
insert into A select 1,'1,3,5'select * from A
go
if object_id('B') is not null drop table B
go
create table B(TypeIDS tinyint,TypeName varchar(30))
insert into B select 1,'杭州' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'武汉' union all
select 5,'郑州'go
select * from B where TypeIDS in(select TypeIDS from A where Id=1)
declare @str varchar(30)--字符
select @str=TypeIDS from A where id=1select * from B where TypeIDS in(select * from split(@str))