数据如下:第一行为字段名
JH SGXH QXXH QXDM QSSD JSSD SDJG
jh1 1 1 GR 1300 1750 0.125
jh1 1 2 R4 1300 1750 0.125
jh1 1 3 BS 1300 1750 0.125
jh1 1 4 AC 1300 1750 0.125
jh1 1 5 DEN 1300 1750 0.125
jh1 1 6 CAL 1300 1750 0.125
jh1 1 7 ILM 1300 1750 0.125
jh1 1 8 ILD 1300 1750 0.125
jh1 1 9 MSFL 1300 1750 0.125
jh1 1 10 LLD 1300 1750 0.125
jh1 1 11 LLS 1300 1750 0.125
jh1 1 12 CNL 1300 1750 0.125
jh1 2 1 GR 100 1750 0.125
jh1 2 2 R4 100 1750 0.125
jh1 2 3 BS 100 1750 0.125
jh1 2 4 AC 100 1750 0.125
jh1 2 5 DEN 100 1750 0.125
jh1 2 6 CAL 100 1750 0.125要查询 JH='jh1',QXDM = 'CAL',QSSD最小,同时JSSD最大的,记录来。全部输出
SQL如何写?
JH SGXH QXXH QXDM QSSD JSSD SDJG
jh1 1 1 GR 1300 1750 0.125
jh1 1 2 R4 1300 1750 0.125
jh1 1 3 BS 1300 1750 0.125
jh1 1 4 AC 1300 1750 0.125
jh1 1 5 DEN 1300 1750 0.125
jh1 1 6 CAL 1300 1750 0.125
jh1 1 7 ILM 1300 1750 0.125
jh1 1 8 ILD 1300 1750 0.125
jh1 1 9 MSFL 1300 1750 0.125
jh1 1 10 LLD 1300 1750 0.125
jh1 1 11 LLS 1300 1750 0.125
jh1 1 12 CNL 1300 1750 0.125
jh1 2 1 GR 100 1750 0.125
jh1 2 2 R4 100 1750 0.125
jh1 2 3 BS 100 1750 0.125
jh1 2 4 AC 100 1750 0.125
jh1 2 5 DEN 100 1750 0.125
jh1 2 6 CAL 100 1750 0.125要查询 JH='jh1',QXDM = 'CAL',QSSD最小,同时JSSD最大的,记录来。全部输出
SQL如何写?
where JH='jh1'
and QXDM = 'CAL'
and QSSD=(select min(QSSD) from table)
and JSSD=(select min(JSSD) from table)
and QSSD=(select min(QSSD) from tb where JH='jh1' and QXDM = 'CAL')
and JSSD=(select min(JSSD) from tb where JH='jh1' and QXDM = 'CAL')
一、“同时”理解为“并且”
1楼的属于这种情况,虽然不一定能找出记录,楼主给出的数据比较理想,是个例外
二、“同时”表示QSSD最小值有多行记录相同的时候,取其中JSSD最大的一条记录(虽然也可能有多条),即JSSD的值不一定是整个数据表中最大的。
select * from
(select * from table_name order by QSSD,JSSD desc)
where rownum<2;
select * from table_name
where JH='jh1'
and QXDM = 'CAL'
order by QSSD,JSSD desc)
where rownum<2;
(select * from table
where JH='jh1' and QXDM='CAL'
order by QSSD asc,JSSD desc)
where rownum=<1;
where jh='jh1' and qxdm='CAL'
and not exists(select 1 from tt
where jh=t1.jh and qxdm=t1.qxdm
and (qssd<t1.qssd or jssd>t1.jssd))
select JH,SGXH,QXXH,QXDM,QSSD,JSSD,SDJG,rank() over(partition by JH,QXDM order by QSSD) rn1 from temp
) where rn1 = 1 and JH='jh1' and QXDM='CAL' and QSSD = (select min(QSSD) from temp where JH='jh1' and QXDM='CAL')