表 A
tid id value
a 1 10
a 2 20
b 3 10
c 4 30
c 5 40
d 6 10
e 7 0
f 8 10转换成
tid value1 value2
a 10 20
b 10 0
c 30 40
d 10 0
e 0 10value1的值取同tid中id小的value值 id的值是自增的 SQL要满足更多记录的情况非常感谢大家!!!!
tid id value
a 1 10
a 2 20
b 3 10
c 4 30
c 5 40
d 6 10
e 7 0
f 8 10转换成
tid value1 value2
a 10 20
b 10 0
c 30 40
d 10 0
e 0 10value1的值取同tid中id小的value值 id的值是自增的 SQL要满足更多记录的情况非常感谢大家!!!!
as(
select 'a',1,10 union all
select 'a',2,20 union all
select 'b',3,10 union all
select 'c',4,30 union all
select 'c',5,40 union all
select 'd',6,10 union all
select 'e',7,0 union all
select 'e',8,10
)
select tid,min(value) value1,(case when (select count(1) from tb tb2 where tb2.tid=tb1.tid)>1 then max(value) else 0 end) value2 from tb tb1 group by tid
上面SQL 是根据VALUE 的值来做判断的小的放value1 值大的放value2 不满足
value1的值取 同tid中id小的value值
select 'a', 1, 10 union all
select 'a', 2, 20 union all
select 'b', 3, 10 union all
select 'c', 4, 30 union all
select 'c', 5, 40 union all
select 'd', 6, 10 union all
select 'e', 7, 0 union all
select 'f', 8, 10)
select tid,max(value) value2,
(case when (select count(1) from tb tb2 where tb2.tid=tb1.tid)>1 then min(value) else 0 end)
value1 from tb tb1 group by tid
(case when (select count(1) from tb tb2 where tb2.tid=tb1.tid)>1 then
(select top 1 value from tb tb2 where tb1.tid=tb2.tid order by id desc)
else 0 end) value2 from tb tb1 group by tid
select a.tid,b.value value1,isnull(e.value2,0) value2 from
(select tid,min(id) id from tb) a join tb b on a.tid=b.tid and a.id=b.id
left join
(select c.tid,d.value2 from (select tid,max(id) id from tb group by tid having count(*)>1) c join tb d on c.tid=d.tid and c.id=d.id) e on b.tid=e.tid
可能没说清楚 再补充下
例如 在上面再补充几行
tid id value
g 10 50
g 11 5
h 12 10
h 13 20 结果要是
tid value1 value2
g 50 5
h 10 20 结果 要根据id 的值来
value1 的值 是id 小的值 那行
value2是id 值大的那行的value值
with tb(tid, id, value)as (
select 'a', 1, 10 union all
select 'a', 2, 20 union all
select 'b', 3, 10 union all
select 'c', 4, 30 union all
select 'c', 5, 40 union all
select 'd', 6, 10 union all
select 'e', 7, 0 union all
select 'f', 8, 10 union all
select 'g', 10, 50 union all
select 'g', 11, 5 union all
select 'h', 12, 10 union all
select 'h', 13, 20
),
tbb as(
select row_number()over(partition by tid order by id)number,* from tb)
select a.tid,a.value as value1,isnull(b.value,0) as value2 from (
select tid,value from tbb a where not exists (select 1 from tbb where a.tid=tid and a.number>number))a
left join
(select tid,value from tbb where id not in
(select id from tbb a where not exists
(select 1 from tbb where a.tid=tid and a.number>number))) b
on a.tid=b.tid