table1
id itemno care
1 2 3
2 3 4
3 2 3
4 4 5table2
Grp cwe
9 cw1
10 cw2两表需要关联。需要得到的结果如果id item care Grp cwe
1 2 3 9 cw1
2 3 4 10 cw2
3 2 3 9 cw1
4 4 5 9 cw1就是通过tabl1的itemno 和table2的Grp进行关联。如果table1的itemno=2或者4的话。那么tabl2的grp=9,如果tabl1的itemno=3,那么table2的grp=10,请问应该怎么做。谢谢!!
id itemno care
1 2 3
2 3 4
3 2 3
4 4 5table2
Grp cwe
9 cw1
10 cw2两表需要关联。需要得到的结果如果id item care Grp cwe
1 2 3 9 cw1
2 3 4 10 cw2
3 2 3 9 cw1
4 4 5 9 cw1就是通过tabl1的itemno 和table2的Grp进行关联。如果table1的itemno=2或者4的话。那么tabl2的grp=9,如果tabl1的itemno=3,那么table2的grp=10,请问应该怎么做。谢谢!!
a.*,b.*
from
table1 a,
table2 b
where
b.Grp=(case when a.itemno=3 then 10 else 9 end)
insert into table1 select 1,2,3
insert into table1 select 2,3,4
insert into table1 select 3,2,3
insert into table1 select 4,4,5create table table2(Grp int,cwe varchar(8))
insert into table2 select 9,'cw1'
insert into table2 select 10,'cw2'select
a.*,b.*
from
table1 a,
table2 b
where
b.Grp=(case when a.itemno=3 then 10 else 9 end)/*
id itemno care Grp cwe
----------- ----------- ----------- ----------- --------
1 2 3 9 cw1
2 3 4 10 cw2
3 2 3 9 cw1
4 4 5 9 cw1
*/drop table table1,table2
select * from
(select *,cid=case when care=2 or care=4 then 9
when care=3 then 10 end
from table1)a,table2 b
where a.cid=b.Grp
这样也可以
insert @table1
select 1, 2, 3 union all
select 2, 3, 4 union all
select 3, 2, 3 union all
select 4, 4, 5
declare @table2 table(Grp int,cwe varchar(10))
insert @table2
select 9, 'cw1' union all
select 10, 'cw2'select * from @table1 as a left join @table2 as b
on (a.itemno in(2,4) and b.grp = 9) OR (a.itemno = 3 and b.grp = 10)/*结果
id itemno care Grp cwe
----------- ----------- ----------- ----------- ----------
1 2 3 9 cw1
2 3 4 10 cw2
3 2 3 9 cw1
4 4 5 9 cw1
*/