select * from 数据表
where charindex(obj1,obj2 )>0
解决方案 »
- SQL 郁闷的问题。。。
- 如何把SQL2000里的表结构复制到SQL2005里 ???
- 求一个sql语句,取历史表上最新的价格和所在的日期
- 各位大哥,我的数据库还有救吗
- 请问如何实现该效果,谢谢!
- MSDE代理使用问题?发生错误-5(拒绝访问.),此时正在SQLServerAgent服务上执行该服务操作.
- SqlServer2000的排序规则冲突问题
- 用sp_spaceused检测表的空间误差
- 在制作安装程序的时候如何使计算机注册ocx类库?
- 初学者问题之二:ODBC添加数据源问题.
- 如何用osql实用工具来操纵SQL server 2000数据库呢?
- SQLServer表的调度为什么用不了呢?
create table 数据表(obj1 char(1),obj2 varchar(10),data1 int,data2 int)
insert 数据表 select 'a','a1',0,1
union all select 'a','a2',0,2
union all select 'a','a3',0,3
union all select 'a','b1',0,1
union all select 'a','b2',0,2
union all select 'a','b3',0,3
union all select 'a','c1',0,1
union all select 'a','c2',0,2
union all select 'b','a1',0,1
union all select 'b','a2',0,2
union all select 'b','a3',0,3
union all select 'b','b1',0,1
union all select 'b','b2',0,2
union all select 'b','b3',0,3
union all select 'b','c1',0,1
union all select 'b','c2',0,2
union all select 'c','a1',0,1
union all select 'c','a2',0,2
union all select 'c','a3',0,3
union all select 'c','b1',0,1
union all select 'c','b2',0,2
union all select 'c','b3',0,3
union all select 'c','c1',0,1
union all select 'c','c2',0,2
go--查询
select * from 数据表
where charindex(obj1,obj2 )>0
go--删除测试
drop table 数据表/*--测试结果obj1 obj2 data1 data2
---- ---------- ----------- -----------
a a1 0 1
a a2 0 2
a a3 0 3
b b1 0 1
b b2 0 2
b b3 0 3
c c1 0 1
c c2 0 2(所影响的行数为 8 行)
--*/
where charindex(rtrim(obj1),obj2 )>0
表obj1
CREATE TABLE [obj1] (
[obj1] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[obj2] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[data] [int] NULL ,
CONSTRAINT [PK_obj1] PRIMARY KEY CLUSTERED
(
[obj1]
) ON [PRIMARY]
) ON [PRIMARY]
GO表factor
CREATE TABLE [Factor] (
[fobj1] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[fobj2] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[data1] [int] NULL ,
CONSTRAINT [PK_Factor] PRIMARY KEY CLUSTERED
(
[fobj1]
) ON [PRIMARY]
) ON [PRIMARY]
GO视图代码如下
CREATE VIEW dbo.matchview
AS
SELECT a.obj1, b.fobj1, a.data,b.data1
FROM dbo.Factor b CROSS JOIN
dbo.obj1 a
CREATE VIEW dbo.matchview
AS
SELECT a.obj1, b.fobj1, a.data,b.data1
FROM dbo.Factor b CROSS JOIN
dbo.obj1 a
where charindex(rtrim(a.obj1),b.fobj1)