SELECT a.vendorid, a.NAME, b.specw, b.specl, a.barcode, MAX (b.cday) days,
b.lot_no, b.qty,
MONTHS_BETWEEN (SYSDATE,
TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) months
FROM a, b
WHERE a.barcode = b.barcode
AND b.iotype = '1'
AND ( b.CATEGORY = '10'
OR b.CATEGORY = '30'
OR b.CATEGORY = '40'
OR b.CATEGORY = '50'
)
GROUP BY a.vendorid, a.NAME, b.specw, b.specl, a.barcode, b.lot_no, b.qty
HAVING MONTHS_BETWEEN (SYSDATE, TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) > 6查询得到如下数据:
vendorid name specw specl barcode days lot_no months qty
10050 aaa 120 130 0027 2008.9.11 12345 7 2
10050 aaa 120 130 0027 2008.8.11 12345 8 1
group by数量(qty)后出现这样的数据,但我只想的到最大日期的那一笔数据,不知道该怎么办了
请大家帮忙
b.lot_no, b.qty,
MONTHS_BETWEEN (SYSDATE,
TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) months
FROM a, b
WHERE a.barcode = b.barcode
AND b.iotype = '1'
AND ( b.CATEGORY = '10'
OR b.CATEGORY = '30'
OR b.CATEGORY = '40'
OR b.CATEGORY = '50'
)
GROUP BY a.vendorid, a.NAME, b.specw, b.specl, a.barcode, b.lot_no, b.qty
HAVING MONTHS_BETWEEN (SYSDATE, TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) > 6查询得到如下数据:
vendorid name specw specl barcode days lot_no months qty
10050 aaa 120 130 0027 2008.9.11 12345 7 2
10050 aaa 120 130 0027 2008.8.11 12345 8 1
group by数量(qty)后出现这样的数据,但我只想的到最大日期的那一笔数据,不知道该怎么办了
请大家帮忙
from (
SELECT a.vendorid, a.NAME, b.specw, b.specl, a.barcode, MAX (b.cday) days,
b.lot_no, b.qty,
MONTHS_BETWEEN (SYSDATE,
TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) months
FROM a, b
WHERE a.barcode = b.barcode
AND b.iotype = '1'
AND ( b.CATEGORY = '10'
OR b.CATEGORY = '30'
OR b.CATEGORY = '40'
OR b.CATEGORY = '50'
)
GROUP BY a.vendorid, a.NAME, b.specw, b.specl, a.barcode, b.lot_no, b.qty
HAVING MONTHS_BETWEEN (SYSDATE, TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) > 6
ordr by days)
where rownum=1
如果要按不同的vendorid或者其它分组条件,来取每个分组的最大日期的那条,可以用row_number() over
SELECT x.vendorid, x.NAME, x.specw || '*' || x.specl spec, x.days, x.lot_no,
x.qty,x.months
FROM (SELECT a.vendorid, a.NAME, b.specw, b.specl, a.barcode,
MAX (b.cday) days, b.lot_no, b.qty,
ROW_NUMBER () OVER (PARTITION BY a.vendorid, a.NAME, b.specw, b.specl, a.barcode, b.lot_no ORDER BY MAX
(b.cday) DESC)
AS row_order,
MONTHS_BETWEEN (SYSDATE,
TO_DATE (MAX (b.cday), 'yyyy.mm.dd')
) months
FROM material a, materialiohis b
WHERE a.barcode = b.barcode
AND b.iotype = '1'
AND ( b.CATEGORY = '10'
OR b.CATEGORY = '30'
OR b.CATEGORY = '40'
OR b.CATEGORY = '50'
)
GROUP BY a.vendorid,
a.NAME,
b.specw,
b.specl,
a.barcode,
b.lot_no,
b.qty
HAVING MONTHS_BETWEEN (SYSDATE,
TO_DATE (MAX (b.cday), 'yyyy.mm.dd')) > 6) x
WHERE row_order < 2還有個疑問,row_number over()在我這句SQL子查詢里或外面效果都一樣,為什么在外面反而where條件用到row_order,卻說列名無效??