--> 生成测试数据表: [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' -->SQL查询如下: SELECT * FROM [t1] UNION ALL SELECT * FROM [t2] ORDER BY 1 /* A B C ----------------------- ---------- ----------- 2010-03-05 00:00:00.000 aaa 1 2010-03-05 00:00:00.000 eee 1 2010-03-06 00:00:00.000 fff 2 2010-03-06 00:00:00.000 bbb 2 2010-03-07 00:00:00.000 ccc 3 2010-03-07 00:00:00.000 ggg 3 2010-03-08 00:00:00.000 hhh 5 2010-03-08 00:00:00.000 ddd 4(8 行受影响) */楼主给的结果不对吧
select * from [t1] union all select * from [t2] order by 1
select * from [t1] union all select * from [t2] order by 1
select * from t1 union all select * from t2
tony哥,就是要那样的排序结果啊,所以我才来请教你们。
lz不知道你给的数据,结果是怎么排序的。 根本不能对应 select * from ( select * from t1 union all select * from t2 ) t order by A,C /* A B C ------------------------------------------------------ ---------- ----------- 2010-03-05 00:00:00.000 aaa 1 2010-03-05 00:00:00.000 eee 1 2010-03-06 00:00:00.000 fff 2 2010-03-06 00:00:00.000 bbb 2 2010-03-07 00:00:00.000 ccc 3 2010-03-07 00:00:00.000 ggg 3 2010-03-08 00:00:00.000 ddd 4 2010-03-08 00:00:00.000 hhh 5(所影响的行数为 8 行)*/
--> 生成测试数据表: [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]
我没表示清楚,再改改: 表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 C和F其实是代表设备号,每个设备号本身是不同的,但是由于建表时没考虑完全,将不同类型的设备号分开存储了,这样两表中代表设备号的C和F就出现了相同值。 但是现在又需要将所有设备统计在一起,按理说将两表合并,并且代表设备号的字段值都不重复就好了,但是现在系统已经运行,没法更改。 排序方式是按照设备号排列的,是事先已经确定好的,哪个设备放在第几行都是固定的。 不知道我说清楚了没有,请大家帮帮忙吧,谢谢了。
--> 生成测试数据表: [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 )goinsert 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,1A 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]
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'
-->SQL查询如下:
SELECT * FROM [t1]
UNION ALL
SELECT * FROM [t2]
ORDER BY 1
/*
A B C
----------------------- ---------- -----------
2010-03-05 00:00:00.000 aaa 1
2010-03-05 00:00:00.000 eee 1
2010-03-06 00:00:00.000 fff 2
2010-03-06 00:00:00.000 bbb 2
2010-03-07 00:00:00.000 ccc 3
2010-03-07 00:00:00.000 ggg 3
2010-03-08 00:00:00.000 hhh 5
2010-03-08 00:00:00.000 ddd 4(8 行受影响)
*/楼主给的结果不对吧
select * from [t2] order by 1
select * from [t2] order by 1
select * from t2
根本不能对应
select * from (
select * from t1 union all
select * from t2 ) t
order by A,C
/*
A B C
------------------------------------------------------ ---------- -----------
2010-03-05 00:00:00.000 aaa 1
2010-03-05 00:00:00.000 eee 1
2010-03-06 00:00:00.000 fff 2
2010-03-06 00:00:00.000 bbb 2
2010-03-07 00:00:00.000 ccc 3
2010-03-07 00:00:00.000 ggg 3
2010-03-08 00:00:00.000 ddd 4
2010-03-08 00:00:00.000 hhh 5(所影响的行数为 8 行)*/
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]
表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 C和F其实是代表设备号,每个设备号本身是不同的,但是由于建表时没考虑完全,将不同类型的设备号分开存储了,这样两表中代表设备号的C和F就出现了相同值。
但是现在又需要将所有设备统计在一起,按理说将两表合并,并且代表设备号的字段值都不重复就好了,但是现在系统已经运行,没法更改。
排序方式是按照设备号排列的,是事先已经确定好的,哪个设备放在第几行都是固定的。
不知道我说清楚了没有,请大家帮帮忙吧,谢谢了。
--> 生成测试数据表: [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
)goinsert 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,1A 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 行)
现在就是给定了一个排序方式,在excel表中第一行放设备C的信息,第二行放设备F的信息......就是说excel中格式就固定那了,就得按照excel里面的设备先后顺序来查询表中的数据,然后导入到excel的指定位置,不知这样说能否明白?
可以把excel模板放到服务器的指定文件夹里,
查询时把excel和t1,t2连接一起查询,试试看