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

这个SQL语句该如何写

www.MyException.Cn  网友分享于:2013-12-30  浏览:11次
求助这个SQL语句该怎么写
表名:abc
字段:a(char),b(int),c(int)

表中的数据

ggg     1       1
ggg     2       2
ggg     2       3
hhh     1       1
hhh     2       2

查询结果只需要   a和c字段的值,a字段的值不重复(只取b值和c值都最大的那一条记录)

也就是说,查询的结果(只显示字段a和c)应该为:
ggg           3
hhh           2

select   a,c   from   abc   where   a不重复   and   b最大   and   c最大

Where后面应该怎么写?请帮忙,谢谢!

------解决方案--------------------
select a,max(c) from abc group by a

------解决方案--------------------
select a,max(c) from abc where b(此处加条件) group by a
------解决方案--------------------
--group by 分组语句可以设置数据分组前跟分组后的条件,分别是where 跟 having

select a,max(c) from abc where 此处为分组前的条件 group by a having 此处为分组后的条件
------解决方案--------------------
--方法一
select * from abc t where not exists(select 1 from abc where a=t.a and c> t.c)

--方法二
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.a,Max(A.c)
FROM abc A
INNER JOIN (SELECT a,MAX(b) AS bb FROM abc GROUP BY a ) AS B ON
A.a = B.a AND A.b = B.bb
GROUP BY A.a
------解决方案--------------------
declare @abc table
(a char(10),b int,c int)
insert into @abc
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


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

----------------
ggg 2 3
hhh 2 2

------解决方案--------------------
SELECT c.a, c.c
FROM
(
SELECT a, b, MAX(c) c
FROM @abc a2
GROUP BY a, b
) c
INNER JOIN
(
SELECT a, MAX(b) b
FROM @abc a2
GROUP BY a
) b ON c.a = b.a AND c.b = b.b
------解决方案--------------------
select
t.*
from
abc t
where
not exists(select 1 from abc where a=t.a and (b> t.b or c> t.c))
------解决方案--------------------
其实我的意思是查询记录的时候先满足b最大的条件,假如有多行记录,则取c最大的那一条。
-------------------
select * from abc
ggg 12 2
ggg 13 1
ggg 2 6
hhh 10 9
hhh 11 1
--------------------
select a,max(c) from abc where b in (select max(b) from abc group by a) group by a
ggg 1
hhh 1
应该是楼主你要的结果了
------解决方案--------------------
看看这个
with myTemp(a,b,c) as (select a,max(b),max(c) from dbo.abc where 1=1 group by a)

select a,c from myTemp
------解决方案--------------------
--方法一
select * from abc t where not exists(select 1 from abc where a=t.a and c> t.c)

文章评论

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