请教,这条SQL语句怎么写?总体上说就是主表按某个字段值的不同,去不同的分表里取值举例如下:两个分商品明细表如下方[R],[S]肉类表 [R]
sid name
1 猪肉
2 牛肉
水果表 [S]
sid name
1 苹果
2 香蕉一个商品销售表[A]
销售表 [A]
id sort sid
1 1 1
2 2 1
3 1 2其中[A]中的[sort]代表分表类型,如:
当sort=1时,该记录的sid为肉类表[R]的sid,
当sort=2时,该记录的sid为水果表[S]的sid,我想求一条语句能查询出下列结果,id sort sid name
1 1 1 猪肉
2 2 1 苹果
3 1 2 牛肉并且这条语句也可以带查询条件,比如像只查询肉类的时候只显示 sort=1的记录非常感谢
sid name
1 猪肉
2 牛肉
水果表 [S]
sid name
1 苹果
2 香蕉一个商品销售表[A]
销售表 [A]
id sort sid
1 1 1
2 2 1
3 1 2其中[A]中的[sort]代表分表类型,如:
当sort=1时,该记录的sid为肉类表[R]的sid,
当sort=2时,该记录的sid为水果表[S]的sid,我想求一条语句能查询出下列结果,id sort sid name
1 1 1 猪肉
2 2 1 苹果
3 1 2 牛肉并且这条语句也可以带查询条件,比如像只查询肉类的时候只显示 sort=1的记录非常感谢
declare @R table
(
sid int
,name varchar(10)
);
insert into @R
select 1, '猪肉' union all
select 2, '牛肉'
;
declare @S table
(
sid int
,name varchar(10)
);
insert into @S
select 1, '苹果' union all
select 2, '香蕉'
;
declare @A table
(
id int
,sort int
,sid int
);
insert into @A
select 1, 1, 1 union all
select 2, 2, 1 union all
select 3, 1, 2
;
--第一种
select id,sort,a.sid,ISNULL(r.Name,s.Name) as Name
from @A a
LEFT JOIN @R r ON a.sort=1 and a.sid=r.sid
LEFT JOIN @S s ON a.sort=2 and a.sid=s.sid
;
--第二种
select id,a.sort,a.sid,s.Name
from @A a
JOIN (select 1 as Sort,* from @R union all select 2,* from @S) s
ON a.sort=s.Sort and a.sid=s.sid
;
--第三种
select id,sort,a.sid,r.Name
from @A a
JOIN @R r ON a.sort=1 and a.sid=r.sid
union all
select id,sort,a.sid,s.Name
from @A a
JOIN @S s ON a.sort=2 and a.sid=s.sid
;
--sqlserver 2005级以上
;WITH CTE1 AS
(SELECT '1' AS SORT,SID,NAME FROM [R]
UNION
SELECT '2' AS SORT,SID,NAME FROM [S]),
CTE2 AS
(SELECT ID,SORT,SID FROM [A]) SELECT CTE2.ID,CTE2.SORT,CTE2.SID,CTE1.NAME
FROM CTE1,CTE2
WHERE CTE1.SORT = CTE2.SORT AND CTE1.SID=CTE2.SID
create table t1
(
tid int,
name varchar(10)
)
insert into t1
select 1, '猪肉' union all
select 2, '牛肉'
create table t2
(
tid int,
name varchar(10)
)
insert into t2
select 1, '苹果' union all
select 2, '香蕉'
create table t3
(
id int,
sort int,
tid int
)
insert into t3
select 1, 1, 1 union all
select 2, 2, 1 union all
select 3, 1, 2
select * from t1
select * from t2
select * from t3;with aaa as
(select t3.*,t1.tid as tid1,t1.name as name1,t2.tid as tid2,t2.name as name2
from t3 inner join t1 on t3.tid=t1.tid inner join t2 on t3.tid=t2.tid)
--select * from aaa
select id,sort,case when sort=1 then tid1 else tid2 end as tid,
case when sort=1 then name1 else name2 end as name
from aaa
(select t3.*,t1.tid as tid1,t1.name as name1,t2.tid as tid2,t2.name as name2
from t3 inner join t1 on t3.tid=t1.tid inner join t2 on t3.tid=t2.tid)
--select * from aaa
select id,sort,case when sort=1 then tid1 else tid2 end as tid,
case when sort=1 then name1 else name2 end as name
from aaa where sort=1另外看楼主的表格设计,肉类表和水果表如果数据量不大,完全可以合并为一张表,然后增加一个字段:类型。
同时可以另设一表:类型表,里面存放诸如:水果、蔬菜、肉类、干货等。
create view vw_test_f
as
select sid ,name,1 as sort from 肉类表
union
select sid ,name,2 as sort from 水果表
goselect a.*,f.name
一个商品销售表[A] a,vw_test_f f
where a.sort=f.sort and a.sid=f.sid
create table #肉类表
(sid int,name nvarchar(10))
insert #肉类表
select 1 ,'猪肉' union all
select 2 ,'牛肉'create table #水果表
(sid int,name nvarchar(10))
insert #水果表
select 1,'苹果' union all
select 2,'香蕉'create table #销售表
(id int,sort int,sid int)
insert #销售表
select 1 ,1 ,1 union all
select 2 ,2 ,1 union all
select 3 ,1 ,2select a.* ,
(case when sort=1 then r.name else s.name end) as name
from #销售表 as a
Left join #肉类表 as r on a.sort=1 and a.sid=r.sid
Left join #水果表 as s on a.sort=2 and a.sid=s.sid--id sort sid name
------------- ----------- ----------- ----------
--1 1 1 猪肉
--2 2 1 苹果
--3 1 2 牛肉
when a.sort=2 then c.sname end as sname
from A a
inner hash join R b
on a.sid=b.sid
inner hash join S c
on a.sid=c.sid