求sql语句，进来看看吧,应该是不难。。。学的不好
有这样两个表，
表一：
datetime temperature
12:21:00 23
12:21:02 25
12:21:04 36
12:21:05 35
12:21:08 35
表二：
datetime temperature
12:21:00 23
12:21:01 25
12:21:03 36
12:21:06 35
12:21:09 35
不过最终生成：

C# code

datetime value1 value212:21:00 23      2312:21:01 (null)  2512:21:02 25     (null)12:21:03 (null)  3612:21:04 36     (null)12:21:05 35     (null)12:21:06 (null)  35

要生成这样的得让两个表做一次交叉之类的吧...有什么好解决方案吗？


[解决办法]

SQL code

--> 测试数据：@表一declare @表一 table([datetime] varchar(8),[temperature] int)insert @表一select '12:21:00',23 union allselect '12:21:02',25 union allselect '12:21:04',36 union allselect '12:21:05',35 union allselect '12:21:08',35declare @表二 table([datetime] varchar(8),[temperature] int)insert @表二select '12:21:00',23 union allselect '12:21:01',25 union allselect '12:21:03',36 union allselect '12:21:06',35 union allselect '12:21:09',35;with maco as (    select * from @表一    union     select * from @表二)select a.datetime,b.temperature,c.temperature from maco a left join @表一 b on a.[datetime]=b.[datetime]left join @表二 c on a.[datetime]=c.[datetime]/*datetime temperature temperature-------- ----------- -----------12:21:00 23          2312:21:01 NULL        2512:21:02 25          NULL12:21:03 NULL        3612:21:04 36          NULL12:21:05 35          NULL12:21:06 NULL        3512:21:08 35          NULL12:21:09 NULL        35*/
[解决办法]
SQL code
if object_id(N'table1') is not null drop table table1gocreate table table1([datetime] varchar(10), [temperature] int)goinsert into table1select '12:21:00', 23 union allselect '12:21:02', 25 union all   select '12:21:04', 36 union all   select '12:21:05', 35 union all   select '12:21:08', 35go if object_id(N'table2') is not null drop table table2gocreate table table2([datetime] varchar(10), [temperature] int)goinsert into table2select '12:21:00', 23 union allselect '12:21:01', 25 union allselect '12:21:03', 36 union allselect '12:21:06', 35 union allselect '12:21:09', 35go;with cte as(    select *,1 as lb from table1    union all     select *,2 from table2)select [datetime],[1] as value1,[2] as value2 from ctepivot(sum([temperature]) for [lb] in([1],[2])) t/*(5 row(s) affected)(5 row(s) affected)datetime   value1      value2---------- ----------- -----------12:21:00   23          2312:21:01   NULL        2512:21:02   25          NULL12:21:03   NULL        3612:21:04   36          NULL12:21:05   35          NULL12:21:06   NULL        3512:21:08   35          NULL12:21:09   NULL        35(9 row(s) affected)*/
[解决办法]
CREATE TABLE ta1(d VARCHAR2(50),n NUMBER );
CREATE TABLE ta2(d VARCHAR2(50),n NUMBER );

INSERT INTO Ta1 VALUES('12:21:00', 23);
INSERT INTO Ta1 VALUES('12:21:02', 25);
INSERT INTO Ta1 VALUES('12:21:04', 36);
INSERT INTO Ta1 VALUES('12:21:05', 35);
INSERT INTO Ta1 VALUES('12:21:08', 35);

INSERT INTO Ta2 VALUES('12:21:00', 23);
INSERT INTO Ta2 VALUES('12:21:01', 25);
INSERT INTO Ta2 VALUES('12:21:03', 36);
INSERT INTO Ta2 VALUES('12:21:06', 35);
INSERT INTO Ta2 VALUES('12:21:09', 35);

COMMIT;

SELECT nvl(a.d,b.d),a.n,b.n FROM   
 
ta1 a FULL JOIN ta2 b ON a.d=b.d
ORDER BY nvl(a.d,b.d)
[解决办法]
SQL code
--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([datetime] DATETIME,[temperature] INT)INSERT [ta]SELECT '12:21:00',23 UNION ALLSELECT '12:21:02',25 UNION ALLSELECT '12:21:04',36 UNION ALLSELECT '12:21:05',35 UNION ALLSELECT '12:21:08',35--------------开始查询----------------------------> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([datetime] DATETIME,[temperature] INT)INSERT [tb]SELECT '12:21:00',23 UNION ALLSELECT '12:21:01',25 UNION ALLSELECT '12:21:03',36 UNION ALLSELECT '12:21:06',35 UNION ALLSELECT '12:21:09',35--------------开始查询--------------------------SELECT  * FROM (SELECT *,1  AS value FROM [ta]UNION ALLSELECT *,2 FROM [tb])aPIVOT (MAX([temperature]) FOR value IN ([1],[2]))b/*datetime                1           2----------------------- ----------- -----------12:21:00  23          2312:21:01  NULL        2512:21:02  25          NULL12:21:03  NULL        3612:21:04  36          NULL12:21:05  35          NULL12:21:06  NULL        3512:21:08  35          NULL12:21:09  NULL        35(9 行受影响)*/