一张表分组后,先判断某个字段a是最大值的有几条记录,如果为一条就选出这一条,如果有多条,就判断哪条的b字段值等于某个值(确定这个值的只会是一条),选出这一条
如表情况是
aa bb cc dd
——————————
1 1 1 1
1 2 2 2
2 2 3 3
2 2 1 4根据 aa列分组 选出bb列是最大值的记录 如果最大值的记录只有一条则选出这条,如果有多条就选出cc列是3的这条记录
结果是
aa bb cc dd
——————————
1 2 2 2
2 2 3 3请教这个sql语句该如何写?
如表情况是
aa bb cc dd
——————————
1 1 1 1
1 2 2 2
2 2 3 3
2 2 1 4根据 aa列分组 选出bb列是最大值的记录 如果最大值的记录只有一条则选出这条,如果有多条就选出cc列是3的这条记录
结果是
aa bb cc dd
——————————
1 2 2 2
2 2 3 3请教这个sql语句该如何写?
--已aa分组,bb,cc降序排列产生一个记录号id,然后过滤id=1
create table T
(aa int, bb int, cc int, dd int)insert into T values(1 , 1 , 1 , 1)
insert into T values(1 , 2 , 2 , 2)
insert into T values(2 , 2 , 3 , 3)
insert into T values(2 , 2 , 1 , 4)select aa,bb,cc,dd from (
select *,ROW_NUMBER() over(partition by aa order by bb desc,cc desc) as id from T
) s
where id=1
-- 如果指定了 3 ,可以使用一个case when 子句,
-- 借楼上的语句。
select aa,bb,cc,dd from (
select *,
ROW_NUMBER() over(partition by aa order by bb desc,case when cc = 3 then 0 else 1 end) as id
from T
) s
where id=1
(aa int, bb int, cc int, dd int)insert into T values(1 , 1 , 1 , 1)
insert into T values(1 , 2 , 2 , 2)
insert into T values(2 , 2 , 3 , 3)
insert into T values(2 , 2 , 1 , 4) with test as
(
select aa,bb,cc,dd,ROW_NUMBER() over(PARTITION by aa order by bb desc) as num from T
)
select aa,bb,cc,dd from test where num=1
如果是最大值,则:
WITH table1(aa,bb,cc,dd) AS (
SELECT 1,1,1,1 UNION ALL
SELECT 1,2,2,2 UNION ALL
SELECT 2,2,3,3 UNION ALL
--SELECT 2,2,4,3 UNION ALL
SELECT 2,2,1,4
)
select aa,bb,cc,dd from
(
select *,ROW_NUMBER() over (partition by aa order by cc desc) as num from
(select a.*,b.maxbb from table1 as a,
(select aa,MAX(bb) as maxbb from table1 group by aa) as b
where a.aa=b.aa and a.bb=b.maxbb) as c
) as t
where num=1
WITH table1(aa,bb,cc,dd) AS (
SELECT 1,1,1,1 UNION ALL
SELECT 1,2,2,2 UNION ALL
SELECT 2,2,3,3 UNION ALL
SELECT 2,2,4,3 UNION ALL
SELECT 2,2,1,4
)
select * from table1,
(select aa,bb,COUNT(cc) as countcc from
(select a.*,b.maxbb from table1 as a,
(select aa,MAX(bb) as maxbb from table1 group by aa) as b
where a.aa=b.aa and a.bb=b.maxbb) as c group by aa,bb) as table2
where table1.aa=table2.aa and table1.bb=table2.bb and ((cc=3 and countcc>1) or countcc=1)