MyException - 我的异常网
当前位置:我的异常网» C# » 这个SQL语句该如何写

这个SQL语句该如何写(2)

www.MyException.Cn  网友分享于:2013-12-30  浏览:11次


--方法二
select * from abc t where c in (select max(c) from abc group by a)


--方法三
select * from abc t where c =(select max(c) from abc where a=t.a)

------解决方案--------------------
select a,max(c) from abc where b = (select max(b) from abc ) group by a
------解决方案--------------------
km_yu(雪上吹风,光着身子淋暴雨) ( ) 信誉:100
这个是正确的

wyhao() ( ) 信誉:100 2007-09-06 18:18:06 得分: 0


select a,max(c) from abc where b = (select max(b) from abc ) group by a


这个应该不能用=,要用IN
------解决方案--------------------
create table tb
(
[a] nvarchar(64) null,
[b] int null,
[c] int null
)

insert tb
select 'ggg ',1,1 union all
select 'ggg ',2,2 union all
select 'ggg ',2,3 union all
select 'hhh ',1,1 union all
select 'hhh ',2,2
go

--select * from tb

select t1.a,max(t1.c) as c from tb as t1 inner join (
select a,max(b) as b from tb group by a) as t2 on t1.a=t2.a
group by t1.a

drop table tb
------解决方案--------------------
declare @abc table(a varchar(11),b int ,c int)
insert into @abc select 'ggg ',1,1
insert into @abc select 'ggg ',2,2
insert into @abc select 'ggg ',2,3
insert into @abc select 'hhh ',1,1
insert into @abc select 'hhh ',2,2

select
t.*
from
@abc t
where
not exists(select 1 from @abc where a=t.a and (b> t.b or c> t.c))

/*
a b c
----------- ----------- -----------
ggg 2 3
hhh 2 2
*/
------解决方案--------------------
SELECT A.a, B.c
FROM
--按a分组取b最大的值
(SELECT a, MAX(b) b FROM abc GROUP BY a) A

LEFT OUTER JOIN
--按a、b分组取c最大的值
(SELECT a, b, MAX(c) c FROM abc GROUP BY a, b) B

ON A.a = B.a AND A.b = B.b
------解决方案--------------------
u can not write it in a simple sql :

select x.a, max(x.c)
from
abc as x,
( select a,max(b) as b from abc group by a ) as y
where x.a = y.a and x.b = y.b
group by x.a

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有