SQL 多表联合查询解决方案

SQL 多表联合查询
如例：
  表1：A002 201211 2012-11-15
  A002 201211 2012-11-30
  表2：A002 201211 2012-11-11
  A002 201211 2012-11-28

要得到结果：
  A002 201211 2012-11-15 2012-11-11
  A002 201211 2012-11-30 2012-11-28
怎么办呢？在线苦等！！！！



[解决办法]

SQL code

create table t1(a varchar(6),b varchar(8),c date)create table t2(a varchar(6),b varchar(8),c date)insert into t1select 'A002', '201211', '2012-11-15' union allselect 'A002', '201211', '2012-11-30'insert into t2select 'A002', '201211', '2012-11-11' union allselect 'A002', '201211', '2012-11-28'select x.a,x.b,x.c,y.cfrom(select a,b,c,row_number() over(order by getdate()) 'rn' from t1) xinner join(select a,b,c,row_number() over(order by getdate()) 'rn' from t2) yon x.rn=y.rn/*a      b        c          c------ -------- ---------- ----------A002   201211   2012-11-15 2012-11-11A002   201211   2012-11-30 2012-11-28(2 row(s) affected)*/
[解决办法]
楼主是想按照时间排序，表一的第一条和表二的第一条合并，第二条和第二条合并。。。这样的话二楼的应该可以了。

感觉应该不是这样简单的需求吧
[解决办法]
探讨

如果是：
  表1：A002 201211 2012-11-15
  A002 201211 2012-11-30
  A003 201211 2012-11-25
  A003 201211 2012-11-29
  A004 201212 2012-12-01
  表2：A002 201211 2012-11-11
  A002 201211 2012-11-28
  A003……

[解决办法]
SQL code
--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([a] VARCHAR(4),[b] INT,[c] DATETIME)INSERT [ta]SELECT 'A002',201211,'2012-11-15' UNION ALLSELECT 'A002',201211,'2012-11-30' UNION ALLSELECT 'A003',201211,'2012-11-25' UNION ALLSELECT 'A003',201211,'2012-11-29' UNION ALLSELECT 'A004',201212,'2012-12-01'--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([a] VARCHAR(4),[b] INT,[c] DATETIME)INSERT [tb]SELECT 'A002',201211,'2012-11-11' UNION ALLSELECT 'A002',201211,'2012-11-28' UNION ALLSELECT 'A003',201211,'2012-11-25'--------------开始查询--------------------------SELECT  x.a , x.b , x.c , y.cFROM    (         SELECT a , b , c , row_id= ROW_NUMBER () OVER (ORDER BY GETDATE ()) FROM ta        ) xFULL JOIN /*或者换成left join*/           (           SELECT a , b , c , row_id= ROW_NUMBER () OVER (ORDER BY GETDATE ()) FROM tb          ) yON      x.row_id = y.row_id----------------结果----------------------------/* a    b    c    cA002    201211    2012-11-15 00:00:00.000    2012-11-11 00:00:00.000A002    201211    2012-11-30 00:00:00.000    2012-11-28 00:00:00.000A003    201211    2012-11-25 00:00:00.000    2012-11-25 00:00:00.000A003    201211    2012-11-29 00:00:00.000    NULLA004    201212    2012-12-01 00:00:00.000    NULL*/
[解决办法]
select a,b,max(c),max(d)
from (
select a,b,c,null as d
from TB1
union all
select a,b,null as c,c as d
from TB1) as T
group by a,b