表1
a b
1 1,3,5表2
a1 b1
1 2,3,
2 5,6,8,11,22
3 11,33现在需要用表1中的b列去筛选表2中的b1列,要避免搜1时把11也搜出来的情况。
最终结果为:
表2
a1 b1
1 2,3,
2 5,6,8,11,22我想到的办法:
select * from 表2 where ','+b1+',' like '%,1,%' or ','+b1+',' like '%,3,%' or ','+b1+',' like '%,5,%' 但这个办法应该不是最优方法,因为如果表1的b列中有很多个数字,那我的SQL就要写N个like
不知道各位高手有什么好的解决方法吗?
a b
1 1,3,5表2
a1 b1
1 2,3,
2 5,6,8,11,22
3 11,33现在需要用表1中的b列去筛选表2中的b1列,要避免搜1时把11也搜出来的情况。
最终结果为:
表2
a1 b1
1 2,3,
2 5,6,8,11,22我想到的办法:
select * from 表2 where ','+b1+',' like '%,1,%' or ','+b1+',' like '%,3,%' or ','+b1+',' like '%,5,%' 但这个办法应该不是最优方法,因为如果表1的b列中有很多个数字,那我的SQL就要写N个like
不知道各位高手有什么好的解决方法吗?
解决方案 »
- 参数的问题
- 数据库的日志问题
- sql server 2005 里面我在哪个库的哪个表里能看到像2K里面的所有对象信息??
- 这样怎么编号?
- 一个视图中包含多个视图和多个表,在提取数据的时候非常慢,里面的视图和表已经不能再优化。请问还有什么好的方法啊,高手指点,不惜发言啊
- asp.net连接不上MSSQL2005,连接出现错误。求远程协助。全分送上
- 我想插入varchar类型字段如下的值'601090','6010901','6010902'(注意这是一个完整的字符串)请问SQL应该如何写?
- 如何优化这个十几万数据执行8分钟的sql查询语句
- COMPUTE 如何得到其返回值
- 如何用SQLSERVER6.5打开从SQLSERVER6.0中拷贝来的数据库设备文件(dat)
- 同一用户,一分钟之内可操作数据库十几次,是否正常?
- sql server 2005 导出数据库的mdf文件和ldf文件到目标文件夹
select k.*
from 表2 k
where charindex(
(Select
a.a,b=substring(a.b,b.number,charindex(',',a.b+',',b.number)-b.number)
from
表1 a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.b)
where
substring(','+a.b,b.number,1)=','),b1)<0
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-02 23:06:05
---------------------------------
--> 生成测试数据表:t1If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([a] int,[b] nvarchar(5))
Insert t1
Select 1,'1,3,5'
Go
--Select * from t1--> 生成测试数据表:t2If not object_id('[t2]') is null
Drop table [t2]
Go
Create table [t2]([a1] int,[b1] nvarchar(11))
Insert t2
Select 1,'2,3,' union all
Select 2,'5,6,8,11,22' union all
Select 3,'11,33'
Go
--Select * from t2-->SQL查询如下:
select *
from t2 a
join (
select
a.a,
b=substring(a.b, b.number, charindex(',', a.b + ',', b.number) - b.number)
from t1 a,master..spt_values b
where b.type='p'
and substring(',' + a.b,b.number,1) = ','
) b
on charindex(','+ltrim(b.b)+',',','+a.b1+',')>0
/*
a1 b1
----------- -----------
1 2,3,
2 5,6,8,11,22(2 行受影响)
*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('表1') IS NOT NULL
DROP TABLE 表1
GO
CREATE TABLE 表1 ( a int, b varchar(10))
go
insert 表1 SELECT
1, '1,3,5'
go
IF OBJECT_ID('表2') IS NOT NULL
DROP TABLE 表2
GO
CREATE TABLE 表2 ( a int, b1 varchar(20))
go
insert 表2 SELECT
1 , '2,3' UNION ALL SELECT
2 , '5,6,8,11,22'UNION ALL SELECT
3 , '11,33'
go
select *
from 表2 k
join
(Select
b=substring(a.b,b.number,charindex(',',a.b+',',b.number)-b.number)
from
表1 a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.b)
where
substring(','+a.b,b.number,1)=',') t on CHARINDEX(','+t.b+',',','+b1+',')>0
go
a b1 b
----------- -------------------- ----------
1 2,3 3
2 5,6,8,11,22 5
from t2 a
join (
select
a.a,
b=substring(a.b, b.number, charindex(',', a.b + ',', b.number) - b.number)
from t1 a,master..spt_values b
where b.type='p'
and substring(',' + a.b,b.number,1) = ','
) b
on charindex(','+ltrim(b.b)+',',','+a.b1+',')>0
-----------------------------------------------------------------
我要给
select *
from t2 a
加一个where条件放在什么位置啊?
我的SQL语句
select * from
(SELECT dbo.COMM_RESOURCE_COMMEND.RESOURCE_NUM, dbo.COMM_RESOURCE_COMMEND.RESOURCE_CLASS,
dbo.COMM_CENTER_COMMEND.COMMEND_USER_TYPE, dbo.COMM_CENTER_COMMEND.CENT_ORG_ID,
dbo.COMM_CENTER_COMMEND.CREATE_DATE
FROM dbo.COMM_RESOURCE_COMMEND INNER JOIN
dbo.COMM_CENTER_COMMEND ON dbo.COMM_RESOURCE_COMMEND.COMMEND_ID = dbo.COMM_CENTER_COMMEND.COMMEND_IDs
where COMM_CENTER_COMMEND.CENT_ORG_ID='00000000-0000-0000-0000-000000000000') a
join(select a.USER_ID, a.ATTENTION, b=substring(a.ATTENTION,b.number,charindex(',',a.ATTENTION+',',b.number)-b.number)
from COMM_HUMAN_EMPLOYEE a,master..spt_values b where b.type='p' and substring(',' + a.ATTENTION,b.number,1) = ','
and a.USER_ID='E4A659DA-A55B-4D9C-8608-9FDE9E2D38E1'
) b on charindex(','+ltrim(b.b)+',',','+a.COMMEND_USER_TYPE+',')>0
报错呀!
from (select * from 表2 where 条件) k
join
(Select
b=substring(a.b,b.number,charindex(',',a.b+',',b.number)-b.number)
from
表1 a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.b)
where
substring(','+a.b,b.number,1)=',') t on CHARINDEX(','+t.b+',',','+b1+',')>0
go试试吧