A A1 3 B B2 5 ? 不应该是 A A2 3 B B2 5 么?你到底要的是COL3的最大值么?如果是的话,就应该第一个结果是A A2 3 呀
select t.* from t,(select COL1,COL3=max(COL3) from t) a where t.COL1=a.COL1 and t.COL3=a.COL3
这可能是搂住写错了吧。select testtbl.* from testtbl, ( select col1,max(col3) as col3 from testtbl group by col1 )tabletemp where testtbl.col1=tabletemp.col1 and testtbl.col3=tabletemp.col3
select col1,col2,max(col3)from tb a where col1 in (select col1 from test where col1=a.col1)
估计你要的是pbsql(风云) 所说的意思把。
sorry,结果是; 我想通过一条SQL语句得到如下数据 A A2 3 B B2 5 C C1 2 即根据COL1 列中值相等分离后, COL3 最大的值得到的
select col1,col2,max(col3) as col3 from tb a where col1 in (select col1 from test where col1=a.col1) group by col1,col2 忘 了 后 面 的 拉 , 呵 呵
这个可不可以: select distinct(col1),col2,col3 from testtbl order by col3 desc
试下这个:select * from TestTBL a where not exists (select * from TestTBL where col1=a.col1 and col3>a.col3)
create table #test(col1 nvarchar(10), col2 nvarchar(10), col3 float) insert into #test values ('A', 'A1', 2) insert into #test values ('A', 'A2', 3) insert into #test values ('B', 'B1', 4) insert into #test values ('B', 'B2', 6) insert into #test values ('C', 'C1', 2)select #test.* from #test, (select col1,Max(col3) as col3 from #test a group by col1) b where #test.col1 = b.col1 and #test.col3 = b.col3 order by #test.col1drop table #test在SQL Server2000上调试通过
create table #test(col1 nvarchar(10), col2 nvarchar(10), col3 int) insert into #test values ('A', 'A1', 2) insert into #test values ('A', 'A2', 3) insert into #test values ('B', 'B1', 4) insert into #test values ('B', 'B2', 5) insert into #test values ('C', 'C1', 2)测试: select * from #test a where not exists (select * from #test where col1=a.col1 and col3>a.col3)结果: A A2 3 B B2 5 C C1 2
select COL1,min(COL2),max(COL3) from TestTBL group by COL1
create table #test(col1 nvarchar(10), col2 nvarchar(10), col3 float) insert into #test values ('A', 'A1', 2) insert into #test values ('A', 'A2', 3) insert into #test values ('B', 'B1', 4) insert into #test values ('B', 'B2', 6) insert into #test values ('C', 'C1', 2) select * from #test a where not exists(select * from #test where col3>a.col3 and col1=a.col1) /**********************结果******************/ col1 col2 col3 ---------- ---------- --------- A A2 3.0 B B2 6.0 C C1 2.0(所影响的行数为 3 行)
B B2 5
?
不应该是
A A2 3
B B2 5
么?你到底要的是COL3的最大值么?如果是的话,就应该第一个结果是A A2 3 呀
where t.COL1=a.COL1 and t.COL3=a.COL3
这可能是搂住写错了吧。select testtbl.* from testtbl,
(
select col1,max(col3) as col3 from testtbl group by col1
)tabletemp
where testtbl.col1=tabletemp.col1 and testtbl.col3=tabletemp.col3
我想通过一条SQL语句得到如下数据
A A2 3
B B2 5
C C1 2
即根据COL1 列中值相等分离后, COL3 最大的值得到的
忘 了 后 面 的 拉 , 呵 呵
select distinct(col1),col2,col3 from testtbl order by col3 desc
insert into #test values ('A', 'A1', 2)
insert into #test values ('A', 'A2', 3)
insert into #test values ('B', 'B1', 4)
insert into #test values ('B', 'B2', 6)
insert into #test values ('C', 'C1', 2)select #test.* from #test,
(select col1,Max(col3) as col3 from #test a group by col1) b
where #test.col1 = b.col1 and #test.col3 = b.col3
order by #test.col1drop table #test在SQL Server2000上调试通过
insert into #test values ('A', 'A1', 2)
insert into #test values ('A', 'A2', 3)
insert into #test values ('B', 'B1', 4)
insert into #test values ('B', 'B2', 5)
insert into #test values ('C', 'C1', 2)测试:
select * from #test a where not exists (select * from #test where col1=a.col1 and col3>a.col3)结果:
A A2 3
B B2 5
C C1 2
group by COL1
insert into #test values ('A', 'A1', 2)
insert into #test values ('A', 'A2', 3)
insert into #test values ('B', 'B1', 4)
insert into #test values ('B', 'B2', 6)
insert into #test values ('C', 'C1', 2)
select * from #test a where not exists(select * from #test where col3>a.col3 and col1=a.col1)
/**********************结果******************/
col1 col2 col3
---------- ---------- ---------
A A2 3.0
B B2 6.0
C C1 2.0(所影响的行数为 3 行)
学习 Zengqf_W(闷不出)的做法