现在数据库了加了个主键,现在有表结构和记录如下:
表名:FGS
A字段为主键和标识种子
记录:
A F G S
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3先以G字段分组,然后取出S最大那行的F的值,需显示F和G字段
即查询结果如下:
F G
2 1
4 2
SQL语句或者存储过程的写法是?曾经有答案:select F,G from FGS where s in(select max(s) from FGS group by G)结果是错的!
表名:FGS
A字段为主键和标识种子
记录:
A F G S
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3先以G字段分组,然后取出S最大那行的F的值,需显示F和G字段
即查询结果如下:
F G
2 1
4 2
SQL语句或者存储过程的写法是?曾经有答案:select F,G from FGS where s in(select max(s) from FGS group by G)结果是错的!
SELECT F=MAX(F),G FROM FGS GROUP BY G
create table FGS
(
A int identity(1,1),
F int,
G int,
S int
)insert into fgs select 1,1,1
insert into fgs select 2,1,2
insert into fgs select 2,2,1
insert into fgs select 3,2,2
insert into fgs select 4,2,3
--解决方法
select f,g from fgs a where not exists(select 1 from fgs b where a.g=b.g and a.s<b.s)
where a.G=b.G and a.S=b.S楼上的比较高
(select max(s) as S,G from FGS group by G) as b
on a.G=b.G and a.S=b.S
create table FGS
(
A int identity(1,1),
F int,
G int,
S int
)insert into fgs select 1,1,1
insert into fgs select 2,1,2
insert into fgs select 2,2,1
insert into fgs select 3,2,2
insert into fgs select 4,2,3select fgs.a,fgs.f,fgs.g from fgs inner join
(select max(s) as s,g from fgs group by g) as AAA
on fgs.s = aaa.s and fgs.g = aaa.g不过在处理重复的时候不好
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3
6 5 2 3如果记录是这样的话
andy1995(发现一个不错的SQLServer网站:www.windbi.com)
的答案就不正确了,出现了重复,还需要把A作为第二排序才行
不过
andy1995(发现一个不错的SQLServer网站:www.windbi.com)
的SQL语句我不太理解,可以解释一下原理吗?
insert into tb values(1, 1, 1, 1)
insert into tb values(2, 2, 1, 2)
insert into tb values(3, 2, 2, 1)
insert into tb values(4, 3, 2, 2)
insert into tb values(5, 4, 2, 3)
goselect a.* from tb a,
(select g , max(s) s from tb group by g) b
where a.g = b.g and a.s = b.sdrop table tb
/*
A F G S
----------- ----------- ----------- -----------
5 4 2 3
2 2 1 2(所影响的行数为 2 行)
*/
where a.G=b.G and a.S=b.S
group by a.G
insert into tb values(1, 1, 1, 1)
insert into tb values(2, 2, 1, 2)
insert into tb values(3, 2, 2, 1)
insert into tb values(4, 3, 2, 2)
insert into tb values(5, 4, 2, 3)
insert into tb values(6, 5, 2, 3)
go
这样怎么处理?结果?
表名:FGS
A字段为主键和标识种子A F G S
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3
6 5 2 2
7 6 2 3注间:现在问题改如下:
先以G字段分组,然后取出S最大那行的F的值,当最大的S出现几行,取A最大的一行对应的F的值,需显示F和G字段
即查询结果如下:
F G
2 1
6 2
group by t1.G
我再改了一下数据就不对了!!
在G相同且S出现相同数字的时候查询结果不不正确了,现把多种情况的数据列出来,FGS表的数据如下:
表名:FGS
A字段为主键和标识种子A F G S
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3
6 6 2 2
7 5 2 3注间:现在问题改如下:
先以G字段分组,然后取出S最大那行的F的值,当最大的S出现几行,取A最大的一行对应的F的值,需显示F和G字段
即查询结果如下:
F G
2 1
6 2
where a.g=b.g and a.s=b.m order by a.g
根据你那描述 结果不是那样的吧?
以G字段分组,然后取出S最大那行的F的值 应该是 F G
2 1
4 3
5 3
当最大的S出现几行,取A最大的一行对应的F的值 F G
2 1
5 3
结果中怎么会有F 的值为6呢?不知道我这样错没 ?
select f,g from fgs where a in(select max(a) from fgs where s in (select max(s) from fgs group by g)
group by s)
create table tb(A int, F int, G int, S int)
insert into tb values(1, 1, 1, 1)
insert into tb values(2, 2, 1, 2)
insert into tb values(3, 2, 2, 1)
insert into tb values(4, 3, 2, 2)
insert into tb values(5, 4, 2, 3)
insert into tb values(6, 5, 2, 3)
insert into tb values(7, 6, 2, 3)
insert into tb values(8, 6, 1, 1)
insert into tb values(9, 6, 2, 2)
insert into tb values(10, 5, 3, 1)
go
set nocount off
select t.F,t.G from tb t,(select max(t1.A) A from tb t1,(select G,max(s) s from tb group by G) t2 where t1.G=t2.G and t1.s=t2.s
group by t1.G) ta where t.A=ta.A
drop table tb
比如:
A F G S
1 1 1 1
2 2 1 1
3 2 2 1
4 3 2 1
5 4 2 1
select f.f,f.g from fgs f
inner join (select max(s) as s,g from fgs group by g) as t
on f.s = t.s and f.g = t.g如上结果运行差不多吧
select distinct f.f,f.g from fgs f
inner join (select max(s) as s,g from fgs group by g) as t
on f.s = t.s and f.g = t.g如果考虑返回可能重复,就如上 。自我感觉不会有什么重复的
from (select g,max(s) s from fgs group by g) tmp, fgs
where fgs.g = tmp.g and fgs.s = tmp.s
INNER JOIN (SELECT MAX(S) AS S,G FROM FGS b GROUP BY b.G) c
ON a.S= c.S AND a.G =c.G
inner join (select max(s) as s,g from fgs group by g) as t
on f.s = t.s and f.g = t.gselect t.F,t.G from tb t,(select max(t1.A) A from tb t1,(select G,max(s) s from tb group by G) t2 where t1.G=t2.G and t1.s=t2.s
group by t1.G) ta where t.A=ta.Aselect fgs.F, fgs.G
from (select g,max(s) s from fgs group by g) tmp, fgs
where fgs.g = tmp.g and fgs.s = tmp.s
SELECT DISTINCT F,a.G FROM FGS a
INNER JOIN (SELECT MAX(S) AS S,G FROM FGS b GROUP BY b.G) c
ON a.S= c.S AND a.G =c.G以上答案经过验证后都是错的!验证数据如下:
A F G S
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3
6 5 2 2
7 6 2 3先以G字段分组,然后取出S最大那行的F的值,当最大的S出现几行,取A最大的一行对应的F的值,需显示F和G字段
正确答案应该如下:
F G
2 1
6 2
(select fgs.f,fgs.g,fgs.a from
fgs,(SELECT a=MAX(s),fgs.g FROM FGS GROUP BY G)b
where fgs.s = b.a and fgs.g = b.g
)b1,
(select fgs.f,fgs.g,a_max = max(fgs.a) from
fgs GROUP BY fgs.f,fgs.g )b2
where b1.a = b2.a_max
應該:
select b1.f,b1.g from
(select fgs.f,fgs.g,fgs.a from
fgs,(SELECT a=MAX(s),fgs.g FROM FGS GROUP BY G)b
where fgs.s = b.a and fgs.g = b.g
)b1,
(select fgs.s,fgs.g,a_max = max(fgs.a) from
fgs GROUP BY fgs.s,fgs.g )b2
where b1.a = b2.a_max
條件:分解
1
fgs中,滿足關係:(G,Max(S))
2
fgs中,滿足關係:(G,S,Man(A))
1,2交集:
fgs(G,Max(S))∩fgs(G,S,Man(A))=new(G,Max(S),Max(A))
或者:
fgs(G,S,A) => new b1(G,Max(S),A)
b1(G,Max(S),A) => new b2(G,Max(S),Max(A))
(select fgs.f,fgs.g,fgs.a from
fgs,(SELECT a=MAX(s),fgs.g FROM FGS GROUP BY G)b
where fgs.s = b.a and fgs.g = b.g
)b1,
(select fgs.s,fgs.g,a_max = max(fgs.a) from
fgs GROUP BY fgs.s,fgs.g )b2
where b1.a = b2.a_max也是错的!
(select fgs.f,fgs.g,fgs.a from
fgs,(SELECT a=MAX(s),fgs.g FROM FGS GROUP BY G)b
where fgs.s = b.a and fgs.g = b.g
)b1,
(select fgs.g,fgs.s,a_max = max(fgs.a) from
fgs GROUP BY fgs.g,fgs.s )b2
where b1.a = b2.a_max
我試過了,沒有錯啊。
你分析一下,應該也知道我的思路。
(select fgs.f,fgs.g,fgs.a from
fgs,(SELECT fgs.g,ss=MAX(s) FROM FGS GROUP BY G)b
where fgs.s = b.ss and fgs.g = b.g
)b1,
(select fgs.g,fgs.s,a_max = max(fgs.a) from
fgs GROUP BY fgs.g,fgs.s )b2
where b1.a = b2.a_max
再試試,是不是前面,(SELECT a=MAX(s),fgs.g FROM FGS GROUP BY G)b中的a和fgs.a引起的錯誤
drop table fgs
create table FGS
(
A int identity(1,1),
F int,
G int,
S int
)
insert into fgs select 1,1,1
insert into fgs select 2,1,2
insert into fgs select 2,2,1
insert into fgs select 3,2,2
insert into fgs select 4,2,3
insert into fgs select 5,2,2
insert into fgs select 6,2,3--SQL语句
select max(F)as f ,G from fgs a
where s in ( select max(b.s) from fgs b where a.g = b.g group by b.g )
group by G--结果
F G
2 1
6 2
select max(F)as f ,G from fgs a
where s = ( select max(b.s) from fgs b where a.g = b.g group by b.g )
group by G
from(
select b.g,max(b.a) a
from(
select a.g,max(a.s) s
from fgs a
group by a.g
) b
join fgs c on b.g=c.g and b.s=c.s
group by b.g
) d
join fgs e on d.a=e.a
select e.f,e.g
from(
select b.g,max(c.a) a
from(
select a.g,max(a.s) s
from fgs a
group by a.g
) b
join fgs c on b.g=c.g and b.s=c.s
group by b.g
) d
join fgs e on d.a=e.a
select f,g from fgs a where not exists(select 1 from fgs b where a.g=b.g and a.s<=b.s and a.a<b.a)
select max(a.f),a.g from tb a,
(select g , max(s) s from tb group by g) b
where a.g = b.g and a.s = b.s
group by a.s
即查询结果如下:
F G
2 1 --弱弱的问一下,这行是怎么来的啊?
4 2
SQL语句或者存储过程的写法是?
1 1 1 1
2 2 1 2
3 2 2 1
4 3 2 2
5 4 2 3
SELECT F,G FROM TABLE_NAME T
WHERE EXISTS(SELECT G FROM TABLE_NAME GROUP BY G HAVING MAX(S)=T.S AND G=T.G)