无法绑定由多个部分组成的标识符【已经使用别名】无效

mafengcf 2013-03-27 10:48:10

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
...全文
234 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
chuanzhang5687 2013-03-27
  • 打赏
  • 举报
回复
引用 8 楼 mafengcf 的回复:
SQL code?1234567891011121314151617181920212223242526272829303132333435DECLARE @ME_BOM TABLE ( MPARENT_SERIAL_NUMBER_ID NVARCHAR(100), MPRODUCT_ID NVARCHAR(100), MNAME NVARCHAR(10……
看6楼
mafengcf 2013-03-27
  • 打赏
  • 举报
回复
好了,非常感谢。有个地方错了
mafengcf 2013-03-27
  • 打赏
  • 举报
回复


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]
还是不行,我用表变量先存数据都报同样的错误
七不语v 2013-03-27
  • 打赏
  • 举报
回复
我也找了下,和4楼一样,少了表别名。加上试试看。
chuanzhang5687 2013-03-27
  • 打赏
  • 举报
回复
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
mafengcf 2013-03-27
  • 打赏
  • 举报
回复
还是报同样的错误
starseeker7 2013-03-27
  • 打赏
  • 举报
回复
WHERE PARENT_SERIAL_NUMBER_ID= 别名缺乏
chuanzhang5687 2013-03-27
  • 打赏
  • 举报
回复
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
试试这一段
mafengcf 2013-03-27
  • 打赏
  • 举报
回复
每段都没问题,执行过
chuanzhang5687 2013-03-27
  • 打赏
  • 举报
回复
太多 自己一段一段的调一下,看看哪一句出错了

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧