--静态sql select numbers, max(case when rn=1 then sdt end) as BS1 max(case when rn=2 then sdt end) as BS2 max(case when rn=3 then sdt end) as BS3 max(case when rn=4 then sdt end) as BS4 max(case when rn=5 then sdt end) as BS5 max(case when rn=6 then sdt end) as BS6(select top 100 percent *, rn=row_number() over(partition by numbers order by sdt) from #test order by numbers,sdt) a group by numbers
--上面的不对,看这个 --静态sql select numbers, max(case when rn=1 then sdt end) as BS1, max(case when rn=2 then sdt end) as BS2, max(case when rn=3 then sdt end) as BS3, max(case when rn=4 then sdt end) as BS4, max(case when rn=5 then sdt end) as BS5, max(case when rn=6 then sdt end) as BS6 from (select top 100 percent *, rn=row_number() over(partition by numbers order by sdt) from #test order by numbers,sdt) a group by numbers
有这个提示"警告: 聚合或其他 SET 操作消除了空值。"这是为什么 还有 max(case when rn=1 then sdt end) as BS1, 这个用法是什么 手册上写 MAX 返回表达式的最大值。 有点看不懂,你这个迷糊
numbers sdt rn -------------------------------------------------- ----------------------- -------------------- 001 2011-05-04 08:11:23.000 1 001 2011-05-04 18:12:23.000 2 001 2011-05-05 08:11:23.000 3 001 2011-05-05 18:12:23.000 4 --这样的结果用你这方法 select numbers, max(case when rn=1 then sdt end) as BS1, max(case when rn=2 then sdt end) as BS2, max(case when rn=3 then sdt end) as BS3, max(case when rn=4 then sdt end) as BS4, max(case when rn=5 then sdt end) as BS5, max(case when rn=6 then sdt end) as BS6 from( select top 100 percent *, rn=row_number() over(partition by numbers,convert(varchar(10),sdt,120) order by sdt) from #test order by numbers,sdt ) a group by sdt,numbers --排成这样了 numbers BS1 BS2 BS3 BS4 BS5 BS6 ---------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- 001 2011-05-04 08:11:23.000 NULL NULL NULL NULL NULL 001 NULL 2011-05-04 18:12:23.000 NULL NULL NULL NULL 001 2011-05-05 08:11:23.000 NULL NULL NULL NULL NULL 001 NULL 2011-05-05 18:12:23.000 NULL NULL NULL NULL
--查询结果 select numbers, max(case when rn=1 then sdt end) as BS1, max(case when rn=2 then sdt end) as BS2, max(case when rn=3 then sdt end) as BS3, max(case when rn=4 then sdt end) as BS4, max(case when rn=5 then sdt end) as BS5, max(case when rn=6 then sdt end) as BS6 from( select top 100 percent *, rn=row_number() over(partition by numbers order by sdt) from #test order by numbers,sdt ) a group by numbers
select numbers,
max(case when rn=1 then sdt end) as BS1
max(case when rn=2 then sdt end) as BS2
max(case when rn=3 then sdt end) as BS3
max(case when rn=4 then sdt end) as BS4
max(case when rn=5 then sdt end) as BS5
max(case when rn=6 then sdt end) as BS6(select top 100 percent *,
rn=row_number() over(partition by numbers order by sdt)
from #test
order by numbers,sdt) a
group by numbers
--静态sql
select numbers,
max(case when rn=1 then sdt end) as BS1,
max(case when rn=2 then sdt end) as BS2,
max(case when rn=3 then sdt end) as BS3,
max(case when rn=4 then sdt end) as BS4,
max(case when rn=5 then sdt end) as BS5,
max(case when rn=6 then sdt end) as BS6
from
(select top 100 percent *,
rn=row_number() over(partition by numbers order by sdt)
from #test
order by numbers,sdt) a
group by numbers
还有
max(case when rn=1 then sdt end) as BS1,
这个用法是什么
手册上写
MAX
返回表达式的最大值。
有点看不懂,你这个迷糊
numbers sdt rn
-------------------------------------------------- ----------------------- --------------------
001 2011-05-04 08:11:23.000 1
001 2011-05-04 18:12:23.000 2
001 2011-05-05 08:11:23.000 3
001 2011-05-05 18:12:23.000 4
--这样的结果用你这方法
select numbers,
max(case when rn=1 then sdt end) as BS1,
max(case when rn=2 then sdt end) as BS2,
max(case when rn=3 then sdt end) as BS3,
max(case when rn=4 then sdt end) as BS4,
max(case when rn=5 then sdt end) as BS5,
max(case when rn=6 then sdt end) as BS6
from(
select top 100 percent *,
rn=row_number() over(partition by numbers,convert(varchar(10),sdt,120) order by sdt)
from #test
order by numbers,sdt ) a
group by sdt,numbers
--排成这样了
numbers BS1 BS2 BS3 BS4 BS5 BS6
---------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
001 2011-05-04 08:11:23.000 NULL NULL NULL NULL NULL
001 NULL 2011-05-04 18:12:23.000 NULL NULL NULL NULL
001 2011-05-05 08:11:23.000 NULL NULL NULL NULL NULL
001 NULL 2011-05-05 18:12:23.000 NULL NULL NULL NULL
select numbers,
max(case when rn=1 then sdt end) as BS1,
max(case when rn=2 then sdt end) as BS2,
max(case when rn=3 then sdt end) as BS3,
max(case when rn=4 then sdt end) as BS4,
max(case when rn=5 then sdt end) as BS5,
max(case when rn=6 then sdt end) as BS6
from(
select top 100 percent *,
rn=row_number() over(partition by numbers order by sdt)
from #test
order by numbers,sdt ) a
group by numbers