A列 B列
16/0 402003583
16/1 402003583
16/2 402003583
3/0 402013449
3/1 402013449
4/0 402013462
4/1 402013462如上表格数据,我想查询出这样的数据
16/0 402003583
3/0 402013449
4/0 402013462即根据B列相同值,查找到A列最小的值。其余的不要了。
这个sql语句怎么写呢?
谢谢~
16/0 402003583
16/1 402003583
16/2 402003583
3/0 402013449
3/1 402013449
4/0 402013462
4/1 402013462如上表格数据,我想查询出这样的数据
16/0 402003583
3/0 402013449
4/0 402013462即根据B列相同值,查找到A列最小的值。其余的不要了。
这个sql语句怎么写呢?
谢谢~
如果不是可以用select A,B from (select A,B, row_number ()over(partition by A order by A ) as rn from table_name)where rn=1
group by b
insert into tb values('16/1','402003583');
insert into tb values('16/2','402003583');
insert into tb values('3/0','402013449');
insert into tb values('3/1','402013449');
insert into tb values('4/0','402013462');
insert into tb values('4/1','402013462');select a,b from tb group by b,a having min(substr(a,length(a),1))=0
SELECT '16/0' AS A, 402003583 AS B FROM DUAL
UNION ALL
SELECT '16/1' AS A, 402003583 AS B FROM DUAL
UNION ALL
SELECT '16/2' AS A, 402003583 AS B FROM DUAL
UNION ALL
SELECT '3/0' AS A,402013449 AS B FROM DUAL
UNION ALL
SELECT '3/1' AS A,402013449 AS B FROM DUAL
UNION ALL
SELECT '4/0' AS A,402013462 AS B FROM DUAL
UNION ALL
SELECT '4/1' AS A,402013462 AS B FROM DUAL
)
select min(A),B from TEST group by B
select min(a) a_colum, b from T_table group by b_colum
group by B;
然后我就不知该怎么做了,求大神指教
还得拆分字符串啊
截图的那个软件,就是客户端,我一直没用客户端,也不知道用哪个,求推荐,。最好是linux和windows都能用的~~
from table_name t1
where exists
(
select 1
(
select min(t2.A) A,t2.B
from table_name t2
group by t2.B
) t3
where t3.A=t1.A and t3.B=t1.B
)
可以这样解决:select test_tmp1.*
from test test_tmp1
join (select min(cast(substr(a, instr(a, '/') + 1, length(a)) as integer)) a, b
from test
group by b) test_tmp2
on cast(substr(test_tmp1.a,
instr(test_tmp1.a, '/') + 1,
length(test_tmp1.a)) as integer) = test_tmp2.a
and test_tmp1.b = test_tmp2.b