SELECT A.ASC_CD,A.MATERIAL_ID,
(
SELECT COUNT(*) FROM WTY_MAT B WHERE A.ASC_CD=B.ASC_CD AND A.MATERIAL_ID=B.MATERIAL_ID
) AS [QTY]
FROM
(
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_ENG_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD INNER JOIN ENG_MST C WITH(NOLOCK) ON A.ENG_ID=C.ENG_ID WHERE B.RSC_CD='A010' AND C.USED='Y' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_HIS A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' AND A.STOCK_FLAG='01' AND ISNULL(LSTOREHOUSE,'')='' GROUP BY A.ASC_CD,A.MATERIAL_ID
)
A LEFT JOIN (SELECT ASC_CD,MATERIAL_ID FROM WTY_MAT) B ON A.MATERIAL_ID=B.MATERIAL_ID AND A.ASC_CD=B.ASC_CD
GROUP BY A.ASC_CD,A.MATERIAL_ID
0002300024 0401-000101 0
0002300024 0402-001311 17
0002300024 0402-001407 9
0002300024 0402-001624 8
0002300024 0403-000660 5
0002300024 2009-001096 3
0002300024 6001-001795 2
0002300024 AD97-09605G 0
0002300024 GH07-00346A 0
0002300024 GH82-01208A 1
0002300589 0401-000101 16
0002300589 6001-001795 3
0002300589 AD97-09594A 0
0002300589 GH30-00119A 0我想查QTY等于0的怎么写呀加上QTY=0就提示
列名 'qty' 无效。
(
SELECT COUNT(*) FROM WTY_MAT B WHERE A.ASC_CD=B.ASC_CD AND A.MATERIAL_ID=B.MATERIAL_ID
) AS [QTY]
FROM
(
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_ENG_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD INNER JOIN ENG_MST C WITH(NOLOCK) ON A.ENG_ID=C.ENG_ID WHERE B.RSC_CD='A010' AND C.USED='Y' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_HIS A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' AND A.STOCK_FLAG='01' AND ISNULL(LSTOREHOUSE,'')='' GROUP BY A.ASC_CD,A.MATERIAL_ID
)
A LEFT JOIN (SELECT ASC_CD,MATERIAL_ID FROM WTY_MAT) B ON A.MATERIAL_ID=B.MATERIAL_ID AND A.ASC_CD=B.ASC_CD
GROUP BY A.ASC_CD,A.MATERIAL_ID
0002300024 0401-000101 0
0002300024 0402-001311 17
0002300024 0402-001407 9
0002300024 0402-001624 8
0002300024 0403-000660 5
0002300024 2009-001096 3
0002300024 6001-001795 2
0002300024 AD97-09605G 0
0002300024 GH07-00346A 0
0002300024 GH82-01208A 1
0002300589 0401-000101 16
0002300589 6001-001795 3
0002300589 AD97-09594A 0
0002300589 GH30-00119A 0我想查QTY等于0的怎么写呀加上QTY=0就提示
列名 'qty' 无效。
(
SELECT COUNT(*) FROM WTY_MAT B WHERE A.ASC_CD=B.ASC_CD AND A.MATERIAL_ID=B.MATERIAL_ID
) AS [QTY]
FROM
(
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_ENG_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD INNER JOIN ENG_MST C WITH(NOLOCK) ON A.ENG_ID=C.ENG_ID WHERE B.RSC_CD='A010' AND C.USED='Y' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_MST A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' GROUP BY A.ASC_CD,A.MATERIAL_ID
UNION ALL
SELECT A.ASC_CD,A.MATERIAL_ID
FROM STOCK_HIS A WITH(NOLOCK) INNER JOIN ASC_MST B WITH(NOLOCK) ON A.ASC_CD=B.ASC_CD WHERE RSC_CD='A010' AND A.STOCK_FLAG='01' AND ISNULL(LSTOREHOUSE,'')='' GROUP BY A.ASC_CD,A.MATERIAL_ID
)
A where qty=0
GROUP BY A.ASC_CD,A.MATERIAL_ID
(
--这里把你上面的语句复制上
)t
where QTY=0
where qty=0