--> Test Data: @ta
declare @ta table ([id] int,[name] varchar(4),[mdate] datetime,[qkje] int,[jsje] int)
insert into @ta
select 1,'jack','2007-01-08',1000,560 union all
select 2,'mike','2008-01-12',600,300 union all
select 3,'tom','2008-01-15',900,450 union all
select 1,'jack','2007-02-08',600,400
--> Test Data: @tb
declare @tb table ([id] int,[name] varchar(4),[qkje] int,[jsje] int)
insert into @tb
select 1,'jack',600,260 union all
select 3,'tom',900,450--select * from @ta
--select * from @tb
--Code
select * from @ta
union all
select id,name,null,qkje,jsje from @tb
order by id,mdate--Result
/*
id name mdate qkje jsje
----------- ---- ----------------------- ----------- -----------
1 jack NULL 600 260
1 jack 2007-01-08 00:00:00.000 1000 560
1 jack 2007-02-08 00:00:00.000 600 400
2 mike 2008-01-12 00:00:00.000 600 300
3 tom NULL 900 450
3 tom 2008-01-15 00:00:00.000 900 450
*/
declare @ta table ([id] int,[name] varchar(4),[mdate] datetime,[qkje] int,[jsje] int)
insert into @ta
select 1,'jack','2007-01-08',1000,560 union all
select 2,'mike','2008-01-12',600,300 union all
select 3,'tom','2008-01-15',900,450 union all
select 1,'jack','2007-02-08',600,400
--> Test Data: @tb
declare @tb table ([id] int,[name] varchar(4),[qkje] int,[jsje] int)
insert into @tb
select 1,'jack',600,260 union all
select 3,'tom',900,450--select * from @ta
--select * from @tb
--Code
select * from @ta
union all
select id,name,null,qkje,jsje from @tb
order by id,mdate--Result
/*
id name mdate qkje jsje
----------- ---- ----------------------- ----------- -----------
1 jack NULL 600 260
1 jack 2007-01-08 00:00:00.000 1000 560
1 jack 2007-02-08 00:00:00.000 600 400
2 mike 2008-01-12 00:00:00.000 600 300
3 tom NULL 900 450
3 tom 2008-01-15 00:00:00.000 900 450
*/
解决方案 »
- 请教,存储过程出错
- 请教:sql server 2005连接字符串无效
- 哎..,简单的排序问题....,
- 怎样将从表中的内容更新到主表中
- select * 和 select 列名,后者可以使查询时尽量少的数据行扫描吗?
- sql2000插入超过8000字节的字段值。
- between 与 not between的问题
- 为什么显示”无法访问数据库 AdventureWorks。 (ObjectExplorer)“
- 请问有没有这样的一本书?
- 求救,我吧sql server的管理员删除了,现在怎么办
- 请问一条SQL语句如何写,困扰了我好几天,在线等!谢谢
- ***access sql查询的棘手问题(以多个同类型字段的最大值作为查询条件!)
select id ,name,mdate,qkje,jsje
from tba
union all
select id,name,null,qkje,jsje
from tbb
order by id结果:
1 jack 2007-01-08 00:00:00.000 1000 560
1 jack 2007-02-08 00:00:00.000 600 400
1 jack NULL 600 260
2 mike 2008-01-12 00:00:00.000 600 300
3 tom 2008-01-15 00:00:00.000 900 450
3 tom NULL 900 450
insert into @ta select 1,'jack','2007-01-08',1000,560
insert into @ta select 2,'mike','2008-01-12',600,300
insert into @ta select 3,'tom','2008-01-15',900,450
insert into @ta select 1,'jack','2007-02-08',600,400
declare @tb table(id int,name varchar(50),qkje int,jsje int)
insert into @tb select 1,'jack',600,260
insert into @tb select 3,'tom',900,450;
with tb as
(
select id,name,'0' as mdate,qkje,jsje,1 as oid from @tb
union all
select id,name,convert(varchar(10),mdate,120) as mdate,qkje,jsje,2 as oid from @ta
)
select id,name,mdate,qkje,jsje from tb t
order by (select count(1) from tb where id=t.id) desc,oidid name mdate qkje jsje
1 jack 0 600 260
1 jack 2007-01-08 1000 560
1 jack 2007-02-08 600 400
3 tom 0 900 450
3 tom 2008-01-15 900 450
2 mike 2008-01-12 600 300
mdate为datetime类型,则联合时该列用NULL,如为varchar,则用'0'
在关键字 'with' 附近有语法错误。数据库是SQL2000出现上面错误
insert into @ta select 1,'jack','2007-01-08',1000,560
insert into @ta select 2,'mike','2008-01-12',600,300
insert into @ta select 3,'tom','2008-01-15',900,450
insert into @ta select 1,'jack','2007-02-08',600,400
declare @tb table(id int,name varchar(50),qkje int,jsje int)
insert into @tb select 1,'jack',600,260
insert into @tb select 3,'tom',900,450;select id,name,mdate,qkje,jsje from (
select id,name,'0' as mdate,qkje,jsje,1 as oid from @tb
union all
select id,name,convert(varchar(10),mdate,120) as mdate,qkje,jsje,2 as oid from @ta
)t order by (select count(1) from @ta where id=t.id)+(select count(1) from @tb where id=t.id) desc,oidid name mdate qkje jsje
1 jack 0 600 260
1 jack 2007-01-08 1000 560
1 jack 2007-02-08 600 400
3 tom 0 900 450
3 tom 2008-01-15 900 450
2 mike 2008-01-12 600 300