mysql表
column1 column2 column3
28 a 1
20 b 1
34 c 1
30 d 2
25 f 2
40 g 2我想用查询语句得到
20 b 1
25 f 2
这两条记录怎么写啊,就是column3是1(或2)的行只取一行,并且这一行的column1列的值在column3是1(或2)的三行中是最小的
column1 column2 column3
28 a 1
20 b 1
34 c 1
30 d 2
25 f 2
40 g 2我想用查询语句得到
20 b 1
25 f 2
这两条记录怎么写啊,就是column3是1(或2)的行只取一行,并且这一行的column1列的值在column3是1(或2)的三行中是最小的
from mysql表 a inner join ( select column3,min(column1) as mincolumn1 from mysql表 group by column3) b
on a.column3=b.column3 and a.column1=b.mincolumn1
from mysql表 a
where not exists (select 1 from mysql表 where column3=a.column3 and column1<a.column1);理论上不如上一种效率高。
inner join
(select min(column1) as mincolumn1,column3 from 表 group by (column3)) tmp2
on tmp1.column3=tmp2.column3 and tmp1.column1=tmp2.mincolumn1其中, select min(column1) as mincolumn1,column3 from 表 group by (clumn3) tmp2是从原表中取出最小的column1和它对应的column3,之后,将它与原表做内连接,查询,从集中取出符合条件的即可。
逻辑上select min(column1) as mincolumn1,column3 from 表 group by (clumn3) tmp2这一条语句是可以查处符合要求的结果的。
select * from (select * from t2 order by column3 asc,column1 asc) as b group by b.column3;
left join tt b on a.column3 =b.column3 and a.column1>=b.column1
group by a.column1,a.column2,a.column3
having count(b.column1)=1
column1 column2 column3
28 a 1
20 b 1
34 c 1
30 d 2
25 f 2
40 g 2 我想用查询语句得到
20 b 1
25 f 2
这两条记录怎么写啊,就是column3是1(或2)的行只取一行,并且这一行的column1列的值在column3是1(或2)的三行中是最小的
select b.* from (select min(column1) as column1, column3 from tab group by column3) a , tab b where a.column1=b.column1 and a.column3=b.column3