select a.* from B表 as a inner join A表 as b on a.编号=b.编号 and b.属性=2
select a.* from B表 as a inner join A表 as b on a.编号=b.编号 and b.属性=2 where a.项目值>100
Select * from B where 编号=2 and convert(int, 项目值) > 100你试试看吧。
这样的表结构存储信息实在是有点怪异:(。现在想到的是用临时表的方法 create table a ( Id int not null ,name varchar(20) not null ,property int not null) insert a (id,name,property) select 1,'aaa',1 union all select 2,'bbb',2 union all select 3,'ccc',3select * from a select * from b create table b ( Id int not null ,value varchar(100) not null) insert into b select 1,'2004-1-1' union all select 1,'2003-4-1' union all select 2,'50' union all select 2,'120' union all select 2,'130' union all select 3,'dddd' union all select 3,'dddd'set nocount on create table #tmp (id int not null ,value varchar(100) not null) insert into #tmp select b.id,b.value from b inner join a on a.id=b.id and a.property=2 set nocount off select * from #tmp where cast(value as int)>100---查询B表中项目值大于100的记录 drop table #tmp 仓促写成,应该还可以改进的,比如用函数实现
select a.* from B inner join A on A.编号=B.编号 where case A.属性 when 1 then /*日期转换*/ when 2 then convert(int, 项目值) > 100 when 3 then end and A.属性=2
where a.项目值>100
create table a ( Id int not null ,name varchar(20) not null ,property int not null)
insert a (id,name,property)
select 1,'aaa',1
union all
select 2,'bbb',2
union all
select 3,'ccc',3select * from a
select * from b
create table b ( Id int not null ,value varchar(100) not null)
insert into b
select 1,'2004-1-1'
union all
select 1,'2003-4-1'
union all
select 2,'50'
union all
select 2,'120'
union all
select 2,'130'
union all
select 3,'dddd'
union all
select 3,'dddd'set nocount on
create table #tmp (id int not null ,value varchar(100) not null)
insert into #tmp
select b.id,b.value
from b inner join a on a.id=b.id and a.property=2
set nocount off
select * from #tmp where cast(value as int)>100---查询B表中项目值大于100的记录
drop table #tmp 仓促写成,应该还可以改进的,比如用函数实现
where case A.属性
when 1 then /*日期转换*/
when 2 then convert(int, 项目值) > 100
when 3 then
end
and A.属性=2