我有几个表:表B是记录收藏A1,A2表的信息,表:A1
id name Rating
1 a aa
2 b bb
3 c cc
4 d dd
5 e ee 表:A2id name Rating
1 a1 aa1
2 b1 bb1
3 c1 cc1
4 d1 dd1
5 e1 ee1表:Bid Type Sid
1 A1 1
2 A2 2
3 A2 4
4 A1 5
5 A2 1
6 A1 3如何得出结果:
id Type Sid Rating
1 A1 1 aa
2 A2 2 bb1
3 A2 4 dd1
4 A1 5 ee
5 A2 1 aa1
6 A1 3 cc请帮我看看
id name Rating
1 a aa
2 b bb
3 c cc
4 d dd
5 e ee 表:A2id name Rating
1 a1 aa1
2 b1 bb1
3 c1 cc1
4 d1 dd1
5 e1 ee1表:Bid Type Sid
1 A1 1
2 A2 2
3 A2 4
4 A1 5
5 A2 1
6 A1 3如何得出结果:
id Type Sid Rating
1 A1 1 aa
2 A2 2 bb1
3 A2 4 dd1
4 A1 5 ee
5 A2 1 aa1
6 A1 3 cc请帮我看看
union all
select b.*,a2.rating from b,a2 where b.type='a2' and b.sid=a2.id
create table A1(id int,name varchar(3),Rating varchar(8));
go
insert a1 select 1,'a','aa'
insert a1 select 2,'b','bb'
insert a1 select 3,'c','cc'
insert a1 select 4,'d','dd'
insert a1 select 5,'e','ee'create table A2(id int,name varchar(3),Rating varchar(8));
go
insert a2 select 1,'a1','aa1'
insert a2 select 2,'b1','bb1'
insert a2 select 3,'c1','cc1'
insert a2 select 4,'d1','dd1'
insert a2 select 5,'e1','ee1'
go
create table B(id int,Type varchar(2),Sid int);
go
insert b select 1,'A1',1
insert b select 2,'A2',2
insert b select 3,'A2',4
insert b select 4,'A1',5
insert b select 5,'A2',1
insert b select 6,'A1',3
go
select b.*,a1.rating from b,a1 where b.type='a1' and b.sid=a1.id
union all
select b.*,a2.rating from b,a2 where b.type='a2' and b.sid=a2.id
order by b.id
go
drop table a1,a2,b;
go
/*结果
id Type Sid rating
----------- ---- ----------- --------
1 A1 1 aa
2 A2 2 bb1
3 A2 4 dd1
4 A1 5 ee
5 A2 1 aa1
6 A1 3 cc(6 行受影响)*/
--try:
select id,Type ,Sid ,
Rating =case Type when 'A1' then (select Rating from a1 where id=t.Sid)
when 'a2' then (select Rating from a2 where id=t.Sid) end,
from B t
--多了个逗号:select id,Type,Sid,
Rating =case Type when 'A1' then (select Rating from a1 where id=t.Sid) when 'a2' then (select Rating from a2 where id=t.Sid) end
from B t
select b.*
,CASE b.Type
WHEN 'A1' THEN (select rating from A1 Where A1.id = b.sid)
WHEN 'A2' THEN (select rating from A2 Where A2.id = b.sid)
END AS Rating
from B b
INSERT INTO #A SELECT 1,'A','AA'
INSERT INTO #A SELECT 2,'B','BB'
INSERT INTO #A SELECT 3,'C','CC'
INSERT INTO #A SELECT 4,'D','DD'
INSERT INTO #A SELECT 5,'E','EE'CREATE TABLE #B (ID VARCHAR(10),S_NAME VARCHAR(10),RATING VARCHAR(10))
INSERT INTO #B SELECT 1,'A1','AA1'
INSERT INTO #B SELECT 2,'B1','BB1'
INSERT INTO #B SELECT 3,'C1','CC1'
INSERT INTO #B SELECT 4,'D1','DD1'
INSERT INTO #B SELECT 5,'E1','EE1'CREATE TABLE #C (ID VARCHAR(10),TYPE VARCHAR(10),SID VARCHAR(10))
INSERT INTO #C SELECT 1,'A1',1
INSERT INTO #C SELECT 2,'A2',2
INSERT INTO #C SELECT 3,'A2',4
INSERT INTO #C SELECT 4,'A1',5
INSERT INTO #C SELECT 5,'A2',1
INSERT INTO #C SELECT 6,'A1',3SELECT C.ID,C.TYPE,C.SID,(CASE WHEN TYPE='A1' THEN A.RATING ELSE B.RATING END )RATING FROM #C C
LEFT JOIN #B B ON B.ID=C.SID
LEFT JOIN #A A ON A.ID=C.SIDDROP TABLE #A,#B,#C
/**1 A1 1 AA
2 A2 2 BB1
3 A2 4 DD1
4 A1 5 EE
5 A2 1 AA1
6 A1 3 CC
from b join a1
on b.sid=a1.id
join a2
on b.sid=a2.id
order by b.id
union all
select id , Type , Sid , a2.Rating from b , a2 where b.sid = a2.id and b.type = 'A2'
order by id
declare @a1 table(id int,name varchar(6),rating varchar(8))insert into @a1 values(1,'a','aa')
insert into @a1 values(2,'b','bb')
insert into @a1 values(3,'c','cc')
insert into @a1 values(4,'d','dd')
insert into @a1 values(5,'e','ee')declare @a2 table(id int,name varchar(6),rating varchar(8))insert into @a2 values(1,'a1','aa1')
insert into @a2 values(2,'b1','bb1')
insert into @a2 values(3,'c1','cc1')
insert into @a2 values(4,'d1','dd1')
insert into @a2 values(5,'e1','ee1')declare @b table(id int,type varchar(8),sid int)insert into @b values(1,'a1',1)
insert into @b values(2,'a2',2)
insert into @b values(3,'a2',4)
insert into @b values(4,'a1',5)
insert into @b values(5,'a2',1)
insert into @b values(6,'a1',3)
select tbl1.id,tbl1.type,tbl1.sid,tbl2.rating from @b tbl1
inner join (
select id,name,rating,'a1' tblname from @a1
union all
select id,name,rating,'a2' tblname from @a2
) tbl2 on tbl1.type=tbl2.tblname and tbl1.sid=tbl2.id---------------------------------------------------------
1 a1 1 aa
2 a2 2 bb1
3 a2 4 dd1
4 a1 5 ee
5 a2 1 aa1
6 a1 3 cc
go
insert a1 select 1,'a','aa'
insert a1 select 2,'b','bb'
insert a1 select 3,'c','cc'
insert a1 select 4,'d','dd'
insert a1 select 5,'e','ee'create table A2(id int,name varchar(3),Rating varchar(8));
go
insert a2 select 1,'a1','aa1'
insert a2 select 2,'b1','bb1'
insert a2 select 3,'c1','cc1'
insert a2 select 4,'d1','dd1'
insert a2 select 5,'e1','ee1'
go
create table B(id int,Type varchar(2),Sid int);
go
insert b select 1,'A1',1
insert b select 2,'A2',2
insert b select 3,'A2',4
insert b select 4,'A1',5
insert b select 5,'A2',1
insert b select 6,'A1',3
go
select * from
(
select b.id , Type , Sid , a1.Rating from b , a1 where b.sid = a1.id and b.type = 'A1'
union all
select b.id , Type , Sid , a2.Rating from b , a2 where b.sid = a2.id and b.type = 'A2'
) t
order by id
drop table a1,a2,b/*
id Type Sid Rating
----------- ---- ----------- --------
1 A1 1 aa
2 A2 2 bb1
3 A2 4 dd1
4 A1 5 ee
5 A2 1 aa1
6 A1 3 cc(所影响的行数为 6 行)
*/
Declare @A1 Table(ID Int, Name Varchar(10), Rating Varchar(10))
Insert @A1
Select 1, 'a', 'aa'
Union
Select 2, 'b', 'bb'
Union
Select 3, 'c', 'cc'
Union
Select 4, 'd', 'dd'
Union
Select 5, 'e', 'ee'Declare @A2 Table(ID Int, Name Varchar(10), Rating Varchar(10))
Insert @A2
Select 1, 'a1', 'aa1'
Union
Select 2, 'b1', 'bb1'
Union
Select 3, 'c1', 'cc1'
Union
Select 4, 'd1', 'dd1'
Union
Select 5, 'e1', 'ee1'Declare @B Table(ID Int, Type Varchar(10), Sid Int)
Insert @b
Select 1, 'A1', 1
Union
Select 2, 'A2', 2
Union
Select 3, 'A2', 4
Union
Select 4, 'A1', 5
Union
Select 5, 'A2', 1
Union
Select 6, 'A1', 3
Select T.*
From (
Select B.ID, B.Type, B.Sid, A1.Rating
From @A1 A1, @B B
Where B.Type = 'A1' And A1.ID = B.Sid
Union All
Select B.ID, B.Type, B.Sid, A2.Rating
From @A2 A2, @B B
Where B.Type = 'A2' And A2.ID = B.Sid ) T
Order By T.ID
union all
select b.*,a2.rating from b,a2 where b.type='a2' and b.sid=a2.id
order by b.id
(select b.*,a1.rating from b,a1 where b.type='a1' and b.sid=a1.id
union all
select b.*,a2.rating from b,a2 where b.type='a2' and b.sid=a2.id) t
order by 排序列表
(id int,
name varchar(10),
Rating varchar(20))
insert #A1
select 1 , 'a' , 'aa'union all
select 2 , 'b' , 'bb'union all
select 3 , 'c' , 'cc'union all
select 4 , 'd' , 'dd'union all
select 5 , 'e' , 'ee'create table #A2
(id int,
name varchar(10),
Rating varchar(20))
insert #A2
select 1 , 'a1' , 'aa1'union all
select 2 , 'b1' , 'bb1'union all
select 3 , 'c1' , 'cc1'union all
select 4 , 'd1' , 'dd1'union all
select 5 , 'e1' , 'ee1'create table #B
(id int,
Type varchar(10),
sid int)
insert #B
select 1 , 'A1' , 1 union all
select 2 , 'A2' , 2 union all
select 3 , 'A2' , 4 union all
select 4 , 'A1' , 5 union all
select 5 , 'A2' , 1 union all
select 6 , 'A1' , 3select id = a.id,
Type = a.Type,
sid = a.sid,
Rating = b.Rating into #Result
from #B a , #A1 b
where a.Type = 'A1'
and a.sid = b.id
insert #Result
select id = a.id,
Type = a.Type,
sid = a.sid,
Rating = b.Rating
from #B a , #A2 b
where a.Type = 'A2'
and a.sid = b.id
select * from #Result
order by id drop table #A1
drop table #A2
drop table #B
drop table #Result
sid
id
上建索引
(case Type when 'A1' then (select Rating from a1 where id=t1.Sid) when 'a2' then (select Rating from a2 where id=t1.Sid) end ) as Rating
from B t1
------------------------------------
-- Author: struthibernate
-- Date:2008-10-31 12:41:13
-------------------------------------- Test Data: A1
IF OBJECT_ID('A1') IS NOT NULL
DROP TABLE A1
Go
create table A1(id int,name varchar(3),Rating varchar(8));
go
insert a1 select 1,'a','aa'
insert a1 select 2,'b','bb'
insert a1 select 3,'c','cc'
insert a1 select 4,'d','dd'
insert a1 select 5,'e','ee'
-- Test Data: A2
IF OBJECT_ID('A2') IS NOT NULL
DROP TABLE A2
Go
create table A2(id int,name varchar(3),Rating varchar(8));
go
insert a2 select 1,'a1','aa1'
insert a2 select 2,'b1','bb1'
insert a2 select 3,'c1','cc1'
insert a2 select 4,'d1','dd1'
insert a2 select 5,'e1','ee1'
go-- Test Data: B
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
Go
create table B(id int,Type varchar(2),Sid int);
go
insert b select 1,'A1',1
insert b select 2,'A2',2
insert b select 3,'A2',4
insert b select 4,'A1',5
insert b select 5,'A2',1
insert b select 6,'A1',3
go
--Start
select b.*,case type when 'a1' then a1.rating when 'a2' then a2.rating end as rating
from b join a1
on b.sid=a1.id
join a2
on b.sid=a2.id
order by b.id
--end--drop table
DROP TABLE A1,A2,B--result
id type sid rating
--------------------------------
1 A1 1 aa
2 A2 2 bb1
3 A2 4 dd1
4 A1 5 ee
5 A2 1 aa1
6 A1 3 cc--END