sql server命令数据 [英] sql server ordering the data
本文介绍了sql server命令数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有一张桌子像
创建 表#table1(id int ,name varchar ( 40 ))
insert into #table1
选择 15 id,< span class =code-string>' 全局'名称
union
选择 16 id,' Liberty'名称
union
选择 17 id,' Noble' 名称
我还有另一张桌子像
创建 table #table2(number int ,id int ,name varchar ( 50 ))
插入 进入#table2
选择 number = 1,id = 15,' James Monroe'名称
union
select number = 2,id = 15,' Cornell'名称
union
select number = 3,id = 15,' Abraham Lincoln'名称
u nion
选择 number = 4,id = 16,' James K. Polk'名称
union
select number = 5,id = 16,' Roosevelt' name
< span class =code-keyword> union
select number = 6,id = 16,' George Washington'名称
union
select number = 7,id = 17,' Woodrow Wilson'名称
union
select number = 8,id = 17,' Andrew Jackson'名称
联盟
选择数字= 9,id = 17,' John Adams'姓名
i需要打印这样的数据
15 ,' 全球'
3 ,' Abraham Lincoln'
2 ,' 康奈尔'
1 ,' James Monroe'
16 ,' Liberty'
6 ,' George Washington'
4 ,' James K. Polk'
5 ,' Roosevelt'
17 ,' Noble'
8 ,< span class =code-comment>' Andrew Jackson'
9 ,' John Adams'
7 ,' Woodrow Wilson'
解决方案
创建 表 #res( number int ,id int ,name varchar ( 40 ))
插入 进入 #res
选择 null as number,id,name from #table1
union
select number,id,name 来自#table2
选择
case 当 number null 然后 id else number end ,
name
from #res
order by id,number
这也可以:
选择 t2.number,t2.name 来自#table1 t1 left join
(
select id,name, 0 as o,id as number 来自#table1 union 选择 id,name, 1 as o,number 来自#table2
)
t2 on t1.id = t2.id
order by t1.id,o,number desc
Hi all,
I have one table like
create table #table1(id int,name varchar(40))
insert into #table1
select 15 id,'Global' name
union
select 16 id,'Liberty' name
union
select 17 id,'Noble' name
and i have another table like
create table #table2(number int,id int,name varchar(50))
insert into #table2
select number=1,id=15,'James Monroe'name
union
select number=2,id=15,'Cornell'name
union
select number=3,id=15,'Abraham Lincoln'name
union
select number=4,id=16,'James K. Polk'name
union
select number=5,id=16,'Roosevelt'name
union
select number=6,id=16,'George Washington'name
union
select number=7,id=17,'Woodrow Wilson'name
union
select number=8,id=17,'Andrew Jackson'name
union
select number=9,id=17,'John Adams'name
i need to print the data like this
15,'Global'
3,'Abraham Lincoln'
2,'Cornell'
1,'James Monroe'
16 ,'Liberty'
6,'George Washington'
4,'James K. Polk'
5,'Roosevelt'
17,'Noble'
8,'Andrew Jackson'
9,'John Adams'
7,'Woodrow Wilson'
解决方案
create table #res(number int, id int,name varchar(40)) insert into #res select null as number, id, name from #table1 union select number, id, name from #table2 select case when number is null then id else number end, name from #res order by id, number
This will also work:
select t2.number, t2.name from #table1 t1 left join ( select id, name, 0 as o, id as number from #table1 union select id, name, 1 as o, number from #table2 ) t2 on t1.id=t2.id order by t1.id, o, number desc
这篇关于sql server命令数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文