# 逻辑有点难理解的Sql执行结果，以及用处

create table kevintest (col1 char(1),col2 char(2),col3 int);
insert into kevintest values('A','B',2);
insert into kevintest values('A','B',3);
insert into kevintest values('A','B',4);
insert into kevintest values('A','C',1);
insert into kevintest values('A','C',2);
insert into kevintest values('A','C',3);
insert into kevintest values('A','C',4);
insert into kevintest values('A','C',5);

SELECT *
FROM kevintest c
WHERE col1 = 'A' AND col3 = (SELECT MAX (col3)
FROM kevintest
WHERE col1 = c.col1 AND col2 = c.col2)

SELECT *
FROM kevintest c
WHERE col1 = 'A' AND col3 = (SELECT MAX (col3)
FROM kevintest
WHERE col1 = c.col1 AND col2 = c.col2)

PS：实现与分析对象相同功能还有一个写法，这种写法看起来好理解一些，不过Sql Server的写法看起来没有分析对象简洁，还要考虑各个列的类型转换。不过Oracle的写法不用考虑类型转换的问题，不过Oracle的问题在与这种写法在Sql Server里是有语法错误的。
Sql Server写法：
SELECT *
FROM kevintest c
WHERE col1 = 'A' AND col1+'|'+col2+'|'+CHAR(col3) in (SELECT col1+'|'+col2+'|'+CHAR(MAX (col3))
FROM kevintest group by col1,col2)
Oracle写法：
SELECT *
FROM kevintest c
WHERE col1 = 'A' AND (col1,col2,col3) in (SELECT col1,col2,MAX (col3)
FROM kevintest group by col1,col2)

• 博文量
272
• 访问量
437122