有数据表结构如下:
id re
1 1
1 2
1 3
2 1
2 2
3 1
4 5
4 6
re中的值也可以用时间来代替,想要的结果是:
id re1 re1次数 re2
1 3 2 1
2 2 1 1
3 NULL 0 1
4 6 1 5
注:re1是按id分组后取最大的,re1次数是分组后除去re中最小的,re2就是re中最小的那条数据
谢谢大侠了啊,分数只有这么多了,不好意思!
id re
1 1
1 2
1 3
2 1
2 2
3 1
4 5
4 6
re中的值也可以用时间来代替,想要的结果是:
id re1 re1次数 re2
1 3 2 1
2 2 1 1
3 NULL 0 1
4 6 1 5
注:re1是按id分组后取最大的,re1次数是分组后除去re中最小的,re2就是re中最小的那条数据
谢谢大侠了啊,分数只有这么多了,不好意思!
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[re] int)
insert [tb]
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 3,1 union all
select 4,5 union all
select 4,6select [id],
case count(1) when 1 then null else max(re) end as re1,
count(1)-1 as re1次数,
min(re) as re2
from [tb]
group by [id]
---------------------1 3 2 1
2 2 1 1
3 NULL 0 1
4 6 1 5
create table tab(id int,re int);
insert into tab values(1,1),(1,2),(1,3),
(2,1),(2,2),(3,1),(4,5),(4,6);select id,MAX(case when re1>re2 then re1 else null end) re1,
SUM(case when re1=re2 then 0 when re1=re then 1 else 0 end) re1_cnt,
MIN(re2) re2
from (select *,re1=MAX(re) over (partition by id),
re2=min(re) over (partition by id) from tab) t
group by id
/*
1 3 1 1
2 2 1 1
3 NULL 0 1
4 6 1 5
*/
奇怪哈
按這個最後的結果是這樣哦1 3 1 1
2 2 1 1
3 NULL 0 1
4 6 1 5
max(re) re1,
count(1) - 1 re1次数
min(re) re2
from tb
group by id
insert into tb values(1 ,1)
insert into tb values(1 ,2)
insert into tb values(1 ,3)
insert into tb values(2 ,1)
insert into tb values(2 ,2)
insert into tb values(3 ,1)
insert into tb values(4 ,5)
insert into tb values(4 ,6)
go
select id ,
max(re) re1,
count(1) - 1 re1次数,
min(re) re2
from tb
group by iddrop table tb/*
id re1 re1次数 re2
----------- ----------- ----------- -----------
1 3 2 1
2 2 1 1
3 1 0 1
4 6 1 5(所影响的行数为 4 行)
*/
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 3,1 union all
select 4,5 union all
select 4,6
select id,(case when max(re)=min(re) then null else max(re) end) as re1,count(re)-1 as re1次数 ,min(re) as re2 from test group by id输出:id re1 re1次数 re2
----------- ----------- ----------- -----------
1 3 2 1
2 2 1 1
3 NULL 0 1
4 6 1 5
1 1 aaa
1 2 1
1 3 NULL
2 1 bbb
2 2 NULL
3 1 ccc
4 5 ddd
4 6 eee
如果我按照上面的方法取到,增加列flag,需要显示结果为
id flag re1 re1次数 re2
----------- ----------- ----------- -----------
1 NULL 3 2 1
2 NULL 2 1 1
3 ccc 1 0 1
4 eee 6 1 5
即我需要再取到re1相对应的行的数据不是取MAX()
from table
group by id