上周问过一次,但可能没说得清楚,现再提,谢谢各位回复.有个数据表其中有两列是存放流水号的,
lotno product startnum endnum
9210 pa 100 107
9220 pa 108 156
9230 pa 157
9232 pa 158 189
9233 pa 190 300
9236 pb 100 500
例如求出产品是pa的最大流水号的记录就是
9233 pa 190 300
请问查询语句如何写?
lotno product startnum endnum
9210 pa 100 107
9220 pa 108 156
9230 pa 157
9232 pa 158 189
9233 pa 190 300
9236 pb 100 500
例如求出产品是pa的最大流水号的记录就是
9233 pa 190 300
请问查询语句如何写?
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T2.PRODUCT=T1.PRODUCT AND T2.STARTNUM>T1)
AND T1.PRODUCT='pa'
*
from tb a,(select max(startnum)startnum,max(endnum )endnum from tb )b
where a.endnum=b.endnum and a.startnum=b.startnum
lotno,product,startnum ,max(endnum)
from
tb
group by
lotno,product,startnum
t.lotno>lotno)
lotno,product,startnum ,max(endnum)
from
tb
where
product='你要查询的流水号'
group by
lotno,product,startnum
from tb
where product = 'PA'
order by lotno desc
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T2.PRODUCT=T1.PRODUCT AND T2.STARTNUM>T1.STARTNUM)
AND T1.PRODUCT='pa'
--OR
DECLARE @MAXNUM INT
SELECT @MAXNUM=MAX(startnum) FROM TB
SELECT * FROM TB WHERE STARTNUM=@MAXNUM AND PRODUCT='pa'
insert @t
select 9210,'pa',100,107 union all
select 9220,'pa',108,156 union all
select 9230,'pa',157,0 union all
select 9232,'pa',158,189 union all
select 9233,'pa',190,300 union all
select 9236,'pb',100,500
select * from @t t where not exists (select * from @t where t.product=product and startnum>t.startnum and endnum>t.endnum and product='pa')
select * from tab a where exists(select * from tab where a.product=product and a.startnum>startnum)
SELECT * FROM TB T1
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T2.PRODUCT=T1.PRODUCT AND T2.STARTNUM>T1.STARTNUM
and t2.endNUM>t1.endNUM)
刚上班.谢谢各位.
是的,流水号应该是startnum和endnum,那最大值可能是在startnum中,也有可能在endnum中,现在就是先找出流水号最大值在哪列,然后再获得该行.
select * from ta where (流水号最大值的行)?
select * from TB t
where not exists
(select 1 from TB where T.product=product and
t.lotno>lotno)
依据
1、流水号唯一性
2、startnum和endnum不重复
3、endnum要么比startnum大,要么为空
推论:直接取startnum的最大值为所求
GO
CREATE table TB(lotno int,product varchar(10),startnum int,endnum int)
insert INTO TB
select 9210,'pa',100,107 union all
select 9220,'pa',108,156 union all
select 9230,'pa',157,0 union all
select 9232,'pa',158,189 union all
select 9233,'pa',190,300 union all
select 9236,'pb',100,500
GOselect * from tb
where startnum in
(SELECT max(startnum)
FROM tb
where product='pa'
group by product)
golotno product startnum endnum
----------- ---------- ----------- -----------
9233 pa 190 300(1 行受影响)
只能是本行的endnum
所以只需比较startnum
例如我要找出pa这个产品最大的流水号值是300,是在endnum列找到的.最后得到该行其他的值.
是的,startnum是已经保证不能为空.
完成正确,太对了.谢谢.其实startnum最大值的行就是我要的行,因为startnum和endnum的最大值是在同一行的.