求助一条sql查询
SELECT a.productname, a.containername, a.specname, a.moveinqty, a.moveoutqty
FROM aaa a, bbb b
WHERE a.moveouttimestamp IS NOT NULL
AND a.specname IN
( '6010 ', '7010 ', '7020 ', '7030 ', '7040 ', '7050 ', '7060 ', '7070 ')
AND a.containername IN (SELECT containername
FROM aaa)
AND a.productname = b.productname
AND b.cdoname = 'TrackOutLot '
AND b.txndate =
(SELECT MAX (b.txndate)
FROM aaa a, bbb b
WHERE a.moveouttimestamp IS NOT NULL
AND a.specname IN
( '6010 ',
'7010 ',
'7020 ',
'7030 ',
'7040 ',
'7050 ',
'7060 ',
'7070 '
)
AND a.containername = b.containername
AND a.productname = b.productname
AND b.cdoname = 'TrackOutLot ')
这是小弟现在的sql，现在需要的结果是，moveinqty只需要specname是6010的，而moveoutqty则需要列出来的所有specname的相应记录。请问有什么好办法吗？
[解决办法]
SELECT a.productname, a.containername, a.specname, a.moveinqty, b.moveoutqty
FROM aaa a, bbb b, aaa c
WHERE a.moveouttimestamp IS NOT NULL
AND a.specname IN ( '6010 ')
AND a.containername IN (SELECT containername
FROM aaa)
AND a.productname = b.productname
AND b.cdoname = 'TrackOutLot '
AND b.txndate =
(SELECT MAX (b.txndate)
FROM aaa a, bbb b
WHERE a.moveouttimestamp IS NOT NULL
AND a.specname IN ( '6010 ')
AND a.containername = b.containername
AND a.productname = b.productname
AND b.cdoname = 'TrackOutLot ')
and c.moveouttimestamp IS NOT NULL
AND c.specname IN
( '6010 ', '7010 ', '7020 ', '7030 ', '7040 ', '7050 ', '7060 ', '7070 ')
AND c.containername IN (SELECT containername
FROM aaa)
AND c.productname = b.productname
AND b.cdoname = 'TrackOutLot '
AND b.txndate =
(SELECT MAX (b.txndate)
FROM aaa a, bbb b
WHERE a.moveouttimestamp IS NOT NULL
AND a.specname IN
( '6010 ',
'7010 ',
'7020 ',
'7030 ',
'7040 ',
'7050 ',
'7060 ',
'7070 '
)
AND c.containername = b.containername
AND c.productname = b.productname
AND b.cdoname = 'TrackOutLot ')
[解决办法]
可以再UNION ALL一个查询
