有下列两个表
Table1 Table2
FA1 FB1 FA2 FB2
a 1 a 1
b 2 c 3如何合并成下列这样一个表
FA1 FB1 FA2 FB2
a 1 a 1
b 2 - -
- - c 3我是用来对比两个表的数据,无论哪个表都可能有多余的数据~
Table1 Table2
FA1 FB1 FA2 FB2
a 1 a 1
b 2 c 3如何合并成下列这样一个表
FA1 FB1 FA2 FB2
a 1 a 1
b 2 - -
- - c 3我是用来对比两个表的数据,无论哪个表都可能有多余的数据~
from table1 t1 left join table2 t2
on t1.fa1=t2.fa2
union
select t1.fa1,t1.fb1,t2.fa2,t2.fb2
from table2 t2 left join table1 t1
on t1.fa1=t2.fa2
where t1.fa1 is null
fa1= isnull(fa1,'-')
,fb1= isnull(fb1,'-')
,fa2= isnull(fa2,'-')
,fb2= isnull(fb2,'-')
from fa a full join fb b on a.fa1=b.fa2
insert into t1 values('a' , '1')
insert into t1 values('b' , '2')
create table t2(FA2 varchar(10), FB2 varchar(10))
insert into t2 values('a' , '1')
insert into t2 values('c' , '3')
goselect isnull(t1.FA1 , '-') FA1,
isnull(t1.FB1 , '-') FB1,
isnull(t2.FA2 , '-') FA2,
isnull(t2.FB2 , '-') FB2
from t1 full join t2 on t1.FA1 = t2.FA2
order by case when t1.FA1 <> '-' then 1 else 2 enddrop table t1,t2/*
FA1 FB1 FA2 FB2
---------- ---------- ---------- ----------
a 1 a 1
b 2 - -
- - c 3(所影响的行数为 3 行)
*/
from tab1 full join tab2 on tab1.FA1 =tab2.FA2 and tab1.FB1 =tab2.FB2
select isnull(t1.FA1 , '-') FA1,
isnull(t1.FB1 , '-') FB1,
isnull(t2.FA2 , '-') FA2,
isnull(t2.FB2 , '-') FB2
from t1 full join t2 on t1.FA1 = t2.FA2
order by case when t1.FA1 <> '-' then 1 else 2 endorder by ????? shuo shuo
select isnull(t1.FA1 , '-') FA1,
isnull(t1.FB1 , '-') FB1,
isnull(t2.FA2 , '-') FA2,
isnull(t2.FB2 , '-') FB2
from t1 full join t2 on t1.FA1 = t2.FA2
order by case when t1.FA1 <> '-' then 1 else 2 end请大乌龟 说说 order by 的意义 哈
如何让ORDER BY按指定的顺序排序
表a里有个列叫Type,是商品类别,就3种情况:S,A,B,如下:
id name type
1 一班 S
2 五班 A
3 三班 B
4 四班 B
5 二班 A
6 六班 S
现在我需要按照‘S’,‘A’,‘B’的顺序排序,如下:
1 一班 S
6 六班 S
5 二班 A
2 五班 A
3 三班 B
4 四班 B SELECT *
FROM tbl_test
ORDER BY "@#$$%#$%@$@#$@$@#@$这里应该咋写?"
select * from a where type='S' union all
select * from a where type='A' union all
select * from a where type='B'
select id , name ,type
from a
order by case type when 'S' then 1
when 'A' then 2 when 'B' then 3 else 4 end (如果对ID排序,则加最后加,id)
select id , name ,type
from (select *, case type when 'S' then 1 when 'A' then 2 else 3 end as seq from a) X
order by seq
上诉对ID列没有进行排序,如果在上诉基础上对ID再进行排序。
select id , name ,type(假设有列id ,name, type)
from (select *, case type when 'S' then 1 when 'A' then 2 else 3 end as seq from a) X
order by seq,id
1 一班 S
6 六班 S
2 五班 A
5 二班 A
3 三班 B
4 四班 B
(
select isnull(t1.FA1,'-') as FA1,isnull(t1.FB1,'-') as FB1,
isnull(t2.FA2,'-') as FA2,isnull(t2.FB2,'-') as FB2
from t1 left join t2
on t1.FA1=t2.FA2
union
select isnull(t1.FA1,'-') as FA1,isnull(t1.FB1,'-') as FB1,
isnull(t2.FA2,'-') as FA2,isnull(t2.FB2,'-') as FB2
from t2 left join t1
on t1.FA1=t2.FA2
)a
order by case when a.FA1 <> '-' then 1 else 2 end
--
接分
FA1 FB1 FA2 FB2
a 1 a 1
b 2 - -
- - c 3create table a
( FA1 NVARCHAR(2),
FB1 INT,
FA2 NVARCHAR(2),
FB2 INT
)
insert into a select a.FA1,a.FB1,b.FA2,b.FB2 from a left join b on a.FA1 =b.FA1
union all
select '-','-','c','3' FA1 FB1 FA2 FB2
a 1 a 1
b 2 - -
- - c 3