select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y group by x,y)
select xy,Max(jiage) from bgroup by xy
select * from b where jidge in(select max(jidge) from b group by x,y)
select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y group by x,y having count(*) > 1)
XY这个字段有必要存在吗?表中没有didge字段。
declare @table table(x char(1),y char(1) ,jiage char(10)) insert @table values('A','B','3') insert @table values('A','B','2') insert @table values('A','C','9') insert @table values('B','C','10') insert @table values('B','C','100') insert @table values('D','E','99')select * from @table a where jiage = (select max(jiage) from @table where a.x = x and a.y = y group by x,y having count(*) > 1) /*x y jiage ---- ---- ---------- A B 3 B C 100 (所影响的行数为 2 行) */
--这样嘛?? declare @table table(x char(1),y char(1) ,jiage char(10),xy char(10)) insert @table values('A','B','3','AB') insert @table values('A','B','2','AB') insert @table values('A','C','9','AC') insert @table values('B','C','10','BC') insert @table values('B','C','100','BC') insert @table values('D','E','99','DE')select * from @table a where jiage = (select max(jiage) from @table where a.xy = xy group by xy having count(*) > 1)/* x y jiage xy ---- ---- ---------- ---------- A B 3 AB B C 100 BC (所影响的行数为 2 行) */
jiage吧 select b.* from b, (select b.x ,b.y from b ,max(jiage) as jiage from b group by b.x,b.y) b2 where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
错 select b.* from b, (select x ,y ,max(jiage) as jiage from b group by x,y) b2 where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
select xy,Max(jiage) from b group by xy 或 select xy,Max(jiage) from b group by xy having count(*) > 1 -- 没有重复的不算。 或 select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y )
select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y )
--> select xy,Max(jiage) from b group by xy 或 select xy,Max(jiage) from b group by xy having count(*) > 1 -- 没有重复的不算。 或 select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y ) ---> select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y ) 肯定不正确,没有考虑存在jiage重复的情况.别的在测试.
x,y的组合xy存在重复的数据,希望找出xy重复,jidge最大的数据,这个sql语句该如何写?select * from b aa where jidge=(select max(jidge) from b where x=aa.x and y=aa.y having count(*)>1)
--下面是测试:--测试数据 declare @b table(x nvarchar(255),y nvarchar(255) ,jiage nvarchar(255),xy nvarchar(50)) insert @b select 'A','B','3','AB' union all select 'A','B','2','AB' union all select 'A','C','9','AC' union all select 'B','C','10','BC' union all select 'B','C','100','BC' union all select 'D','E','99','DE'--查询 select * from @b aa where jiage=(select max(jiage) from @b where x=aa.x and y=aa.y having count(*)>1)/*--测试结果x y jiage xy ----- ----- ----- ----- A B 3 AB B C 100 BC(所影响的行数为 2 行)--*/
还只要重复的?没看见 select b.* from b, (select x ,y ,max(jiage) as jiage from b group by x,y having count(*) >1) b2 where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
多谢各位: 最后选择的是该语句。 select xy,Max(jiage) from b group by xy 呵呵。 最后我使用的是 select x,y,Max(jiage) as jiage into b_back from b group by x,y order by x select b.x,b.y,b.jiage,b.tequ,b.longgang,b.baoan into b_all from b ,b_back as c where b.x=c.x and b.y=c.y and b.jiage=c.jiage order by b.i_d
insert @table values('A','B','3')
insert @table values('A','B','2')
insert @table values('A','C','9')
insert @table values('B','C','10')
insert @table values('B','C','100')
insert @table values('D','E','99')select * from @table a where jiage = (select max(jiage) from @table where a.x = x and a.y = y group by x,y having count(*) > 1)
/*x y jiage
---- ---- ----------
A B 3
B C 100 (所影响的行数为 2 行)
*/
declare @table table(x char(1),y char(1) ,jiage char(10),xy char(10))
insert @table values('A','B','3','AB')
insert @table values('A','B','2','AB')
insert @table values('A','C','9','AC')
insert @table values('B','C','10','BC')
insert @table values('B','C','100','BC')
insert @table values('D','E','99','DE')select * from @table a where jiage = (select max(jiage) from @table where a.xy = xy group by xy having count(*) > 1)/*
x y jiage xy
---- ---- ---------- ----------
A B 3 AB
B C 100 BC (所影响的行数为 2 行)
*/
select b.*
from b,
(select b.x ,b.y from b ,max(jiage) as jiage from b group by b.x,b.y) b2
where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
select b.*
from b,
(select x ,y ,max(jiage) as jiage from b group by x,y) b2
where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
group by xy
或
select xy,Max(jiage) from b
group by xy
having count(*) > 1 -- 没有重复的不算。
或
select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y )
select xy,Max(jiage) from b
group by xy
或
select xy,Max(jiage) from b
group by xy
having count(*) > 1 -- 没有重复的不算。
或
select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y )
--->
select * from b a where jiage = (select max(jiage) from b where a.x = x and a.y = y )
肯定不正确,没有考虑存在jiage重复的情况.别的在测试.
where jidge=(select max(jidge) from b where x=aa.x and y=aa.y having count(*)>1)
declare @b table(x nvarchar(255),y nvarchar(255) ,jiage nvarchar(255),xy nvarchar(50))
insert @b
select 'A','B','3','AB'
union all select 'A','B','2','AB'
union all select 'A','C','9','AC'
union all select 'B','C','10','BC'
union all select 'B','C','100','BC'
union all select 'D','E','99','DE'--查询
select * from @b aa
where jiage=(select max(jiage) from @b where x=aa.x and y=aa.y having count(*)>1)/*--测试结果x y jiage xy
----- ----- ----- -----
A B 3 AB
B C 100 BC(所影响的行数为 2 行)--*/
select b.*
from b,
(select x ,y ,max(jiage) as jiage from b group by x,y having count(*) >1) b2
where b.x+b.y=b2.x+b2.y and b.jiage=b2.jiage
最后选择的是该语句。
select xy,Max(jiage) from b group by xy
呵呵。
最后我使用的是
select x,y,Max(jiage) as jiage into b_back from b group by x,y order by x
select b.x,b.y,b.jiage,b.tequ,b.longgang,b.baoan into b_all from b ,b_back as c where b.x=c.x and b.y=c.y and b.jiage=c.jiage order by b.i_d