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
--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
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
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 --这里就写你的主键吧。 -- 查看行转列方面的贴子。
--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
像这样,楼主改改吧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 行受影响)*/
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 行受影响)*/
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
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
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
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
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 --这里就写你的主键吧。
-- 查看行转列方面的贴子。
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
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 行受影响)*/
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 行受影响)*/