请问如何将下面表中的数据选择出来,把7月的数据放在显示的左边8月的数据放在右边?businessarea tablename rowscount createdate
kwt tb1 690 20100702
kwt tb2 890 20100702
kwt tb3 1870 20100702
kwt tb4 8370 20100702
kwt tb5 8770 20100702
kwt tb6 8870 20100702
kwt tb7 3870 20100702
hwl tb1 690 20100702
hwl tb2 890 20100702
hwl tb3 1870 20100702
hwl tb4 8370 20100702
hwl tb5 8770 20100702
hwl tb6 8870 20100702
hwl tb7 3870 20100702
kwt tb1 690 20100802
kwt tb2 890 20100802
kwt tb3 1870 20100802
kwt tb4 8370 20100802
kwt tb5 8770 20100802
kwt tb6 8870 20100802
kwt tb7 3870 20100802
hwl tb1 690 20100802
hwl tb2 890 20100802
hwl tb3 1870 20100802
hwl tb4 8370 20100802
hwl tb5 8770 20100802
hwl tb6 8870 20100802
hwl tb7 3870 20100802
kwt tb1 690 20100702
kwt tb2 890 20100702
kwt tb3 1870 20100702
kwt tb4 8370 20100702
kwt tb5 8770 20100702
kwt tb6 8870 20100702
kwt tb7 3870 20100702
hwl tb1 690 20100702
hwl tb2 890 20100702
hwl tb3 1870 20100702
hwl tb4 8370 20100702
hwl tb5 8770 20100702
hwl tb6 8870 20100702
hwl tb7 3870 20100702
kwt tb1 690 20100802
kwt tb2 890 20100802
kwt tb3 1870 20100802
kwt tb4 8370 20100802
kwt tb5 8770 20100802
kwt tb6 8870 20100802
kwt tb7 3870 20100802
hwl tb1 690 20100802
hwl tb2 890 20100802
hwl tb3 1870 20100802
hwl tb4 8370 20100802
hwl tb5 8770 20100802
hwl tb6 8870 20100802
hwl tb7 3870 20100802
if object_id('tempdb.dbo.#') is not null drop table #
create table #(businessarea varchar(8), tablename varchar(8), rowscount int, createdate datetime)
insert into #
select 'kwt', 'tb1', 690, '20100702' union all
select 'kwt', 'tb2', 890, '20100702' union all
select 'kwt', 'tb3', 1870, '20100702' union all
select 'kwt', 'tb4', 8370, '20100702' union all
select 'kwt', 'tb5', 8770, '20100702' union all
select 'kwt', 'tb6', 8870, '20100702' union all
select 'kwt', 'tb7', 3870, '20100702' union all
select 'hwl', 'tb1', 690, '20100702' union all
select 'hwl', 'tb2', 890, '20100702' union all
select 'hwl', 'tb3', 1870, '20100702' union all
select 'hwl', 'tb4', 8370, '20100702' union all
select 'hwl', 'tb5', 8770, '20100702' union all
select 'hwl', 'tb6', 8870, '20100702' union all
select 'hwl', 'tb7', 3870, '20100702' union all
select 'kwt', 'tb1', 690, '20100802' union all
select 'kwt', 'tb2', 890, '20100802' union all
select 'kwt', 'tb3', 1870, '20100802' union all
select 'kwt', 'tb4', 8370, '20100802' union all
select 'kwt', 'tb5', 8770, '20100802' union all
select 'kwt', 'tb6', 8870, '20100802' union all
select 'kwt', 'tb7', 3870, '20100802' union all
select 'hwl', 'tb1', 690, '20100802' union all
select 'hwl', 'tb2', 890, '20100802' union all
select 'hwl', 'tb3', 1870, '20100802' union all
select 'hwl', 'tb4', 8370, '20100802' union all
select 'hwl', 'tb5', 8770, '20100802' union all
select 'hwl', 'tb6', 8870, '20100802' union all
select 'hwl', 'tb7', 3870, '20100802';with t1 as
(
select rn=row_number()over( order by getdate()), * from # where month(createdate)=7
),
t2 as
(
select rn=row_number()over( order by getdate()), * from # where month(createdate)=8
)
select * from t1 a full join t2 b on a.rn=b.rn
/*
rn businessarea tablename rowscount createdate rn businessarea tablename rowscount createdate
-------------------- ------------ --------- ----------- ----------------------- -------------------- ------------ --------- ----------- -----------------------
1 kwt tb1 690 2010-07-02 00:00:00.000 1 kwt tb1 690 2010-08-02 00:00:00.000
2 kwt tb2 890 2010-07-02 00:00:00.000 2 kwt tb2 890 2010-08-02 00:00:00.000
3 kwt tb3 1870 2010-07-02 00:00:00.000 3 kwt tb3 1870 2010-08-02 00:00:00.000
4 kwt tb4 8370 2010-07-02 00:00:00.000 4 kwt tb4 8370 2010-08-02 00:00:00.000
5 kwt tb5 8770 2010-07-02 00:00:00.000 5 kwt tb5 8770 2010-08-02 00:00:00.000
6 kwt tb6 8870 2010-07-02 00:00:00.000 6 kwt tb6 8870 2010-08-02 00:00:00.000
7 kwt tb7 3870 2010-07-02 00:00:00.000 7 kwt tb7 3870 2010-08-02 00:00:00.000
8 hwl tb1 690 2010-07-02 00:00:00.000 8 hwl tb1 690 2010-08-02 00:00:00.000
9 hwl tb2 890 2010-07-02 00:00:00.000 9 hwl tb2 890 2010-08-02 00:00:00.000
10 hwl tb3 1870 2010-07-02 00:00:00.000 10 hwl tb3 1870 2010-08-02 00:00:00.000
11 hwl tb4 8370 2010-07-02 00:00:00.000 11 hwl tb4 8370 2010-08-02 00:00:00.000
12 hwl tb5 8770 2010-07-02 00:00:00.000 12 hwl tb5 8770 2010-08-02 00:00:00.000
13 hwl tb6 8870 2010-07-02 00:00:00.000 13 hwl tb6 8870 2010-08-02 00:00:00.000
14 hwl tb7 3870 2010-07-02 00:00:00.000 14 hwl tb7 3870 2010-08-02 00:00:00.000
*/
select * from tb where left(createdate,6) = '201007') as a,
(select * from tb where left(createdate,6)= '201008') as b
服务器: 消息 156,级别 15,状态 1,行 33
在关键字 'with' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 35
'row_number' 不是可以识别的 函数名。
服务器: 消息 195,级别 15,状态 1,行 39
'row_number' 不是可以识别的 函数名。