有表H及表D(主從表關系),如何通過D表中非主鍵字段作為查詢條件,查詢出H表信息,如:
H表:
K H1 H2 --此行為字段,其下為記錄
a a1 a2
b b1 b2D表:
K D1 D2 --此行為字段,其下為記錄
a 123 a22
a 456 a22
b 45 a12如何通過查詢條件 D1 like '%45%',查詢出H表的K值為a,b的記錄。謝謝!
H表:
K H1 H2 --此行為字段,其下為記錄
a a1 a2
b b1 b2D表:
K D1 D2 --此行為字段,其下為記錄
a 123 a22
a 456 a22
b 45 a12如何通過查詢條件 D1 like '%45%',查詢出H表的K值為a,b的記錄。謝謝!
select H.* from H join D on H.K = D.K where D.D1 like '%45%'
where H.K = D.K and D.D1 like '%45%'
declare @H table(K varchar(10),H1 varchar(10),H2 varchar(10))
insert into @H
select 'a', 'a1', 'a2' union all
select 'b', 'b1', 'b2' declare @D table(K varchar(10),D1 varchar(10),D2 varchar(10))
insert into @D
select 'a', '123', 'a22' union all
select 'a', '456', 'a22' union all
select 'b', '45', 'a12' select a.* from @H a ,@D b where a.K = b.k and b.D1 like '%45%'
select H.* from H join D on H.K = D.K where D.D1 like '%45%'
select H.* from H,D
where H.K = D.K and
charindex('45',D.D1)>0
select H.* from H,D
where H.K = D.K and D.D1 like '%45%'
declare @H table(K varchar(10) primary key,H1 varchar(10),H2 varchar(10))--插入数据
insert into @H
select 'a','a1','a2' union all
select 'b','b1','b2'--测试表2
declare @D table(K varchar(10),D1 varchar(10),D2 varchar(10))--插入数据
insert into @D
select 'a','123','a22' union all
select 'a','456','a22' union all
select 'b','45','a12'--查询表中数据
select * from @H
select * from @D--所求结果
select H.* from @H H,@D D
where H.K = D.K and D.D1 like '%45%'
/*所影响的行数为 2 行)
(所影响的行数为 3 行)K H1 H2
---------- ---------- ----------
a a1 a2
b b1 b2(所影响的行数为 2 行)K D1 D2
---------- ---------- ----------
a 123 a22
a 456 a22
b 45 a12(所影响的行数为 3 行)K H1 H2
---------- ---------- ----------
a a1 a2
b b1 b2(所影响的行数为 2 行)
*/
insert into @H
select 'a', 'a1', 'a2' union all
select 'b', 'b1', 'b2' declare @D table(K varchar(10),D1 varchar(10),D2 varchar(10))
insert into @D
select 'a', '423', 'a22' union all
select 'a', '456', 'a22' union all
select 'b', '45', 'a12' select a.* from @H a ,@D b where a.K = b.k and b.D1 like '%4%'
insert H
select 'a', 'a1', 'a2' union all
select 'b', 'b1', 'b2'create table D(K varchar(10),D1 varchar(10),D2 varchar(10))
insert D
select 'a', '123', 'a22' union all
select 'a', '456', 'a22' union all
select 'b', '45', 'a12'select D.*
from H join D on H.K=D.K
where D1 like '%45%'K D1 D2
---------- ---------- ----------
a 456 a22
b 45 a12(2 行受影响)