34,590
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([A] [datetime],[B] [nvarchar](10),[C] [int])
INSERT INTO [t1]
SELECT '2010-03-05','aaa','1' UNION ALL
SELECT '2010-03-06','bbb','2' UNION ALL
SELECT '2010-03-07','ccc','3' UNION ALL
SELECT '2010-03-08','ddd','4'
--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2] ([D] [datetime],[E] [nvarchar](10),[F] [int])
INSERT INTO [t2]
SELECT '2010-03-05','eee','1' UNION ALL
SELECT '2010-03-06','fff','2' UNION ALL
SELECT '2010-03-07','ggg','3' UNION ALL
SELECT '2010-03-08','hhh','5'
-->生成排序表: [TOrder]
IF OBJECT_ID('[TOrder]') IS NOT NULL
DROP TABLE [TOrder]
Create Table TOrder
(ENO varchar(10) not null,
ECode int not null,
idx int
)
go
insert into Torder
Select 'aaa', 1,1
union select 'bbb', 2,5
union select 'ccc', 3,4
union select 'ddd', 4,7
union select 'eee', 1,3
union select 'fff', 2,6
union select 'ggg', 3,2
union select 'hhh', 5,8
go
-->SQL查询如下:
Select A.* from (SELECT * FROM [t1]
UNION ALL
SELECT * FROM [t2]) A
Left join TOrder I on I.ENo=A.B and I.Ecode=A.C
ORDER BY I.Idx,1
A B C
------------------------------------------------------ ---------- -----------
2010-03-05 00:00:00.000 aaa 1
2010-03-07 00:00:00.000 ggg 3
2010-03-05 00:00:00.000 eee 1
2010-03-07 00:00:00.000 ccc 3
2010-03-06 00:00:00.000 bbb 2
2010-03-06 00:00:00.000 fff 2
2010-03-08 00:00:00.000 ddd 4
2010-03-08 00:00:00.000 hhh 5
(所影响的行数为 8 行)
表t1:
A B C
-------------------------------
2010-03-05 aaa 1
2010-03-05 bbb 2
2010-03-05 ccc 3
2010-03-05 ddd 4
表t2:
D E F
-------------------------------
2010-03-05 eee 1
2010-03-05 fff 2
2010-03-05 ggg 3
2010-03-05 hhh 5
想得到的结果:
A B C
-------------------------------
2010-03-05 aaa 1
2010-03-05 ggg 3
2010-03-05 eee 1
2010-03-05 ccc 3
2010-03-05 bbb 2
2010-03-05 fff 2
2010-03-05 ddd 4
2010-03-05 hhh 5
--> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([A] [datetime],[B] [nvarchar](10),[C] [int])
INSERT INTO [t1]
SELECT '2010-03-05','aaa','1' UNION ALL
SELECT '2010-03-07','bbb','2' UNION ALL
SELECT '2010-03-06','ccc','3' UNION ALL
SELECT '2010-03-08','ddd','4'
--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2] ([D] [datetime],[E] [nvarchar](10),[F] [int])
INSERT INTO [t2]
SELECT '2010-03-06','eee','1' UNION ALL
SELECT '2010-03-07','fff','2' UNION ALL
SELECT '2010-03-05','ggg','3' UNION ALL
SELECT '2010-03-08','hhh','5'
-->SQL查询如下:
SELECT * FROM [t1]
UNION ALL
SELECT * FROM [t2]
ORDER BY A,C,B
[Executed: 10-6-17 上午11时41分34秒 ] [Execution: 0/ms]
4 record(s) affected
A B C
------------------- ---- ----
2010-3-5 上午12:00:00 aaa 1
2010-3-5 上午12:00:00 ggg 3
2010-3-6 上午12:00:00 eee 1
2010-3-6 上午12:00:00 ccc 3
2010-3-7 上午12:00:00 bbb 2
2010-3-7 上午12:00:00 fff 2
2010-3-8 上午12:00:00 ddd 4
2010-3-8 上午12:00:00 hhh 5
8 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 10-6-17 上午11时41分34秒 ] [Execution: 16/ms]