把查询结果横排显示

[asp6152642]

select readingdata from appdata where devid = 001 order by receivetime desc
select readingdata from appdata where devid = 002 order by receivetime desc
现在查询的结果是这样
readingdata
2
2
2
2
-------------------
readingdata
2
2
2
2
我想让这两条结果横排显示 方便我对比观察
readingdata readingdata
2 2
2 2
2 2
2 2

[AcHerat]

select max(case when devid = '001' then readingdata end) c1,
       max(case when devid = '002' then readingdata end) c2
from appdata 
group by receivetime 
order by receivetime desc

[dawugui]

--sql 2005

select m.readingdata_001 , n.readingdata_002 from
(select readingdata_001 , row_number() over(order by  receivetime desc) px from appdata where devid = '001') m
full join
(select readingdata_002 , row_number() over(order by  receivetime desc) px from appdata where devid = '002') n
on m.px = n.px

[中国风]

SELECT a.readingdata,b.readingdata
FROM 
(select readingdata ,row=ROW_NUMBER()OVER(order by receivetime desc  )from appdata where devid = 001)a
FULL JOIN 
(select readingdata ,row=ROW_NUMBER()OVER(order by receivetime desc  ) from appdata where devid = 002 )b
ON a.row=b.row

[-晴天]

打开两个查询窗口就行了.不要最大化.

[--小F--]

select
 max(case devid when  '001' then readingdata end) c1,
 max(case devid when  '002' then readingdata end) c2
from
 appdata 
group by
 receivetime 
order by
 receivetime desc

[AcHerat]

理解错了。。。

[AcHerat]

楼主测试看下吧！

[gw6328]

select sum(case when devid='001' then readingdata else 0 end) as readingdatea1,
SUM(case when  devid='002' then readingdata else 0 end ) as readingdatea2
group by id --这里就写你的主键吧。
-- 查看行转列方面的贴子。

[dawugui]

--sql 2000
select m.readingdata_001 , n.readingdata_002 from
(select readingdata_001 , (select count(1) from appdata where devid = '001' and receivetime > t.receivetime) px from appdata t where devid = '001') m
full join
(select readingdata_002 , (select count(1) from appdata where devid = '002' and receivetime > t.receivetime) px from appdata t where devid = '002') n
on m.px = n.px

[asp6152642]

这个搞定了 非常感谢[Quote=引用 3 楼 roy_88 的回复:]

SQL code
SELECT a.readingdata,b.readingdata
FROM
(select readingdata ,row=ROW_NUMBER()OVER(order by receivetime desc )from appdata where devid = 001)a
FULL JOIN
(select readingdata ,row=ROW_NUMBE……
[/Quote]

[asp6152642]

结果是NULL值[Quote=引用 7 楼 acherat 的回复:]

楼主测试看下吧！
[/Quote]

[pengxuan]

像这样，楼主改改吧

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int
)
go
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 9
go
select a.id1,b.id1 from (
select row=row_number() over(order by case when id<5 then id end),
       id1=case when id<5 then id end from tb 
where case when id<5 then id end is not null
) a left join (
select row=row_number() over(order by case when id>5 then id end),
       id1=case when id>5 then id end from tb 
where case when id>5 then id end is not null
) b on a.row=b.row
/*
id1         id1
----------- -----------
1           7
2           8
3           9
4           NULL

(4 行受影响)

*/

[dawugui]

对于本帖,我很郁闷.

[pengxuan]

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int
)
go
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 9
go
select a.id1,b.id1 from (
select row=row_number() over(order by case when id<5 then id end),
       id1=case when id<5 then id end from tb 
where case when id<5 then id end is not null
) a left join (
select row=row_number() over(order by case when id>5 then id end),
       id1=case when id>5 then id end from tb 
where case when id>5 then id end is not null
) b on a.row=b.row
/*
id1         id1
----------- -----------
1           7
2           8
3           9
4           NULL

(4 行受影响)

*/

[pengxuan]

结贴了也要贴上来