把查询结果横排显示

asp6152642 2011-11-17 11:09:40
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

...全文
183 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
pengxuan 2011-11-17
  • 打赏
  • 举报
回复
结贴了也要贴上来
pengxuan 2011-11-17
  • 打赏
  • 举报
回复

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 2011-11-17
  • 打赏
  • 举报
回复
对于本帖,我很郁闷.
pengxuan 2011-11-17
  • 打赏
  • 举报
回复
像这样,楼主改改吧

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 行受影响)

*/
asp6152642 2011-11-17
  • 打赏
  • 举报
回复
结果是NULL值[Quote=引用 7 楼 acherat 的回复:]

楼主测试看下吧!
[/Quote]
asp6152642 2011-11-17
  • 打赏
  • 举报
回复
这个搞定了 非常感谢[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]
dawugui 2011-11-17
  • 打赏
  • 举报
回复
--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
gw6328 2011-11-17
  • 打赏
  • 举报
回复

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 --这里就写你的主键吧。
-- 查看行转列方面的贴子。
AcHerat 元老 2011-11-17
  • 打赏
  • 举报
回复
楼主测试看下吧!
AcHerat 元老 2011-11-17
  • 打赏
  • 举报
回复
理解错了。。。
--小F-- 2011-11-17
  • 打赏
  • 举报
回复
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
-晴天 2011-11-17
  • 打赏
  • 举报
回复
打开两个查询窗口就行了.不要最大化.
中国风 2011-11-17
  • 打赏
  • 举报
回复
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
dawugui 2011-11-17
  • 打赏
  • 举报
回复
--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
AcHerat 元老 2011-11-17
  • 打赏
  • 举报
回复

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧