27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM (
SELECT LINK.PARENT_SERIAL_NUMBER_ID AS ME_SERIAL_NUMBER_ID,
PARENTSNSERIAL.PRODUCT_ID,
PARENTSNSERIAL.LOT_ID,
COMPONENT.NAME AS ME_NAME
FROM MESDB.DBO.QS_SERIAL_NUMBERS SNSERIAL
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SNSERIAL.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS PARENTSNSERIAL ON LINK.PARENT_SERIAL_NUMBER_ID=PARENTSNSERIAL.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENT ON PARENTSNSERIAL.PRODUCT_ID=COMPONENT.PRODUCT_ID
WHERE SNSERIAL.SERIAL_NUMBER='847603030100834665'
) MECOMPONET LEFT OUTER JOIN (
SELECT LINK.PARENT_SERIAL_NUMBER_ID, COMPONENTS.NAME , SN.SERIAL_NUMBER
FROM MESDB.DBO.QS_SN_LINKS LINK
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SN ON LINK.CHILD_SERIAL_NUMBER_ID=SN.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENTS ON LINK.CHILD_COMPONENT_ID=COMPONENTS.COMPONENT_ID
WHERE PARENT_SERIAL_NUMBER_ID=(
SELECT LINK.PARENT_SERIAL_NUMBER_ID
FROM MESDB.DBO.QS_SERIAL_NUMBERS SN
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SN.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SNPARENT ON LINK.PARENT_SERIAL_NUMBER_ID=SNPARENT.SERIAL_NUMBER_ID
WHERE SN.SERIAL_NUMBER='847603030100834665'
)
) MEMPURSUE ON MECONPONET.ME_SERIAL_NUMBER_ID=MEMPURSUE.PARENT_SERIAL_NUMBER_ID AND MECONPONET.ME_NAME=MEMPURSUE.NAME
DECLARE @ME_BOM TABLE
(
MPARENT_SERIAL_NUMBER_ID NVARCHAR(100),
MPRODUCT_ID NVARCHAR(100),
MNAME NVARCHAR(100),
MLOT_ID NVARCHAR(100)
)
INSERT INTO @ME_BOM(MPARENT_SERIAL_NUMBER_ID,MPRODUCT_ID,MNAME,MLOT_ID)
SELECT LINK.PARENT_SERIAL_NUMBER_ID,
PARENTSNSERIAL.PRODUCT_ID,
PARENTSNSERIAL.LOT_ID,
COMPONENT.NAME
FROM MESDB.DBO.QS_SERIAL_NUMBERS SNSERIAL
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SNSERIAL.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS PARENTSNSERIAL ON LINK.PARENT_SERIAL_NUMBER_ID=PARENTSNSERIAL.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENT ON PARENTSNSERIAL.PRODUCT_ID=COMPONENT.PRODUCT_ID
WHERE SNSERIAL.SERIAL_NUMBER='847603030100834665'
SELECT MECOMPONET.MNAME,ME_PURSUE.[NAME],ME_PURSUE.SERIAL_NUMBER
FROM @ME_BOM MECOMPONET LEFT OUTER JOIN (
SELECT LINK.PARENT_SERIAL_NUMBER_ID, COMPONENTS.[NAME] , SN.SERIAL_NUMBER
FROM MESDB.DBO.QS_SN_LINKS LINK
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SN ON LINK.CHILD_SERIAL_NUMBER_ID=SN.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENTS ON LINK.CHILD_COMPONENT_ID=COMPONENTS.COMPONENT_ID
WHERE LINK.PARENT_SERIAL_NUMBER_ID=(
SELECT LINK.PARENT_SERIAL_NUMBER_ID
FROM MESDB.DBO.QS_SERIAL_NUMBERS SN
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SN.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SNPARENT ON LINK.PARENT_SERIAL_NUMBER_ID=SNPARENT.SERIAL_NUMBER_ID
WHERE SN.SERIAL_NUMBER='847603030100834665'
)
) ME_PURSUE ON MECONPONET.MPARENT_SERIAL_NUMBER_ID=ME_PURSUE.PARENT_SERIAL_NUMBER_ID
AND MECONPONET.MNAME=ME_PURSUE.[NAME]
还是不行,我用表变量先存数据都报同样的错误SELECT *
FROM
(
SELECT LINK.PARENT_SERIAL_NUMBER_ID AS ME_SERIAL_NUMBER_ID,
PARENTSNSERIAL.PRODUCT_ID,
PARENTSNSERIAL.LOT_ID,
COMPONENT.NAME AS ME_NAME
FROM MESDB.DBO.QS_SERIAL_NUMBERS SNSERIAL
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SNSERIAL.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS PARENTSNSERIAL ON LINK.PARENT_SERIAL_NUMBER_ID=PARENTSNSERIAL.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENT ON PARENTSNSERIAL.PRODUCT_ID=COMPONENT.PRODUCT_ID
WHERE SNSERIAL.SERIAL_NUMBER='847603030100834665'
)
MECOMPONET LEFT OUTER JOIN
(
SELECT LINK.PARENT_SERIAL_NUMBER_ID, COMPONENTS.NAME , SN.SERIAL_NUMBER
FROM MESDB.DBO.QS_SN_LINKS LINK
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SN ON LINK.CHILD_SERIAL_NUMBER_ID=SN.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENTS ON LINK.CHILD_COMPONENT_ID=COMPONENTS.COMPONENT_ID
WHERE PARENT_SERIAL_NUMBER_ID =
(
SELECT LINK.PARENT_SERIAL_NUMBER_ID
FROM MESDB.DBO.QS_SERIAL_NUMBERS SN
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SN.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SNPARENT ON LINK.PARENT_SERIAL_NUMBER_ID=SNPARENT.SERIAL_NUMBER_ID
WHERE SN.SERIAL_NUMBER='847603030100834665'
)
) MEMPURSUE
ON MECONPONET.ME_SERIAL_NUMBER_ID=MEMPURSUE.PARENT_SERIAL_NUMBER_ID
where MECONPONET.ME_NAME=MEMPURSUE.NAME
SELECT *
FROM (
SELECT LINK.PARENT_SERIAL_NUMBER_ID AS ME_SERIAL_NUMBER_ID,
PARENTSNSERIAL.PRODUCT_ID,
PARENTSNSERIAL.LOT_ID,
COMPONENT.NAME AS ME_NAME
FROM MESDB.DBO.QS_SERIAL_NUMBERS SNSERIAL
INNER JOIN MESDB.DBO.QS_SN_LINKS LINK ON SNSERIAL.SERIAL_NUMBER_ID=LINK.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS PARENTSNSERIAL ON LINK.PARENT_SERIAL_NUMBER_ID=PARENTSNSERIAL.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENT ON PARENTSNSERIAL.PRODUCT_ID=COMPONENT.PRODUCT_ID
WHERE SNSERIAL.SERIAL_NUMBER='847603030100834665'
) MECOMPONET LEFT OUTER JOIN (
SELECT LINK.PARENT_SERIAL_NUMBER_ID, COMPONENTS.NAME , SN.SERIAL_NUMBER
FROM MESDB.DBO.QS_SN_LINKS LINK
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SN ON LINK.CHILD_SERIAL_NUMBER_ID=SN.SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_COMPONENTS COMPONENTS ON LINK.CHILD_COMPONENT_ID=COMPONENTS.COMPONENT_ID
WHERE PARENT_SERIAL_NUMBER_ID=(
SELECT aa.PARENT_SERIAL_NUMBER_ID
FROM MESDB.DBO.QS_SERIAL_NUMBERS SN
INNER JOIN MESDB.DBO.QS_SN_LINKS aa ON SN.SERIAL_NUMBER_ID=aa.CHILD_SERIAL_NUMBER_ID
INNER JOIN MESDB.DBO.QS_SERIAL_NUMBERS SNPARENT ON aa.PARENT_SERIAL_NUMBER_ID=SNPARENT.SERIAL_NUMBER_ID
WHERE SN.SERIAL_NUMBER='847603030100834665'
)
) MEMPURSUE ON MECONPONET.ME_SERIAL_NUMBER_ID=MEMPURSUE.PARENT_SERIAL_NUMBER_ID AND MECONPONET.ME_NAME=MEMPURSUE.NAME
试试这一段