无法绑定由多个部分组成的标识符 "A.STUD_NO"。(进着有分)

[zengpei99]

RT

SELECT A.ARR_DATE,B.NUMBER
FROM  
(
	SELECT BRANCH,ARR_DATE,CLASS_NO,STUD_NO FROM CLT09 WHERE 
	STATUS_CODE IN (SELECT ACTIVE_CODE FROM HRC22 WHERE USE_TYPE='2' AND ACTIVE_NOW='Y')
	AND BRANCH='NCHC' AND  ARR_DATE>='20101112' AND ARR_DATE<='20101212'
) AS A
INNER JOIN CLM10  ON A.CLASS_NO = CLM10.CLASS_NO  AND A.BRANCH = CLM10.BRANCH
INNER JOIN 
(
	SELECT (CLT09.STUD_NO)AS STUD_NO,(count(*)+1)AS NUMBER FROM CLT29
	left join CLT09 on CLT09.BRANCH=CLT29.BRANCH AND CLT09.CLASS_NO=CLT29.CLASS_NO WHERE CLT29.BRANCH='NCHC' AND LESN_DATE<CLT09.ARR_DATE 
	AND CLT29.CLASS_NO IN (A.CLASS_NO)
	GROUP BY CLT09.STUD_NO
)AS B WHERE  CLM10.BRANCH='NCHC' GROUP BY A.ARR_DATE,B.NUMBER

报： 无法绑定由多个部分组成的标识符 "A.STUD_NO"。
我需要实现 B表中的 CLT29.CLASS_NO IN (A.CLASS_NO) 这样可以实现吗？


谢谢各位大神！

[dawugui]

太乱了.

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[Shawn]

#1. 代码出错在标红色的地方
#2. 用IN 语句时,一不能直接引用前面的A表，二,IN后面的结果集中只能有一个字段
SELECT A.ARR_DATE,
B.NUMBER
FROM (SELECT BRANCH,
ARR_DATE,
CLASS_NO,
STUD_NO
FROM CLT09
WHERE STATUS_CODE IN (SELECT ACTIVE_CODE
FROM HRC22
WHERE USE_TYPE = '2'
AND ACTIVE_NOW = 'Y')
AND BRANCH = 'NCHC'
AND ARR_DATE >= '20101112'
AND ARR_DATE <= '20101212') AS A
INNER JOIN CLM10
ON A.CLASS_NO = CLM10.CLASS_NO
AND A.BRANCH = CLM10.BRANCH
INNER JOIN (SELECT ( CLT09.STUD_NO )AS STUD_NO,
( COUNT(*) + 1 ) AS NUMBER
FROM CLT29
LEFT JOIN CLT09
ON CLT09.BRANCH = CLT29.BRANCH
AND CLT09.CLASS_NO = CLT29.CLASS_NO
WHERE CLT29.BRANCH = 'NCHC'
AND LESN_DATE < CLT09.ARR_DATE
AND CLT29.CLASS_NO IN ( A.CLASS_NO )
GROUP BY CLT09.STUD_NO)AS B
ON A.STUD_NO = B.STUD_NO
WHERE CLM10.BRANCH = 'NCHC'
GROUP BY A.ARR_DATE,
B.NUMBER
#3. 虽然不懂你的逻辑，正确的参考如下：

SELECT A.ARR_DATE,
       B.NUMBER
FROM   (SELECT BRANCH,
               ARR_DATE,
               CLASS_NO,
               STUD_NO
        FROM   CLT09
        WHERE  STATUS_CODE IN (SELECT ACTIVE_CODE
                               FROM   HRC22
                               WHERE  USE_TYPE = '2'
                                      AND ACTIVE_NOW = 'Y')
               AND BRANCH = 'NCHC'
               AND ARR_DATE >= '20101112'
               AND ARR_DATE <= '20101212') AS A
       INNER JOIN CLM10
         ON A.CLASS_NO = CLM10.CLASS_NO
            AND A.BRANCH = CLM10.BRANCH
       INNER JOIN (SELECT ( CLT09.STUD_NO )AS STUD_NO,
                          ( COUNT(*) + 1 ) AS NUMBER
                   FROM   CLT29
                          LEFT JOIN CLT09
                            ON CLT09.BRANCH = CLT29.BRANCH
                               AND CLT09.CLASS_NO = CLT29.CLASS_NO
                   WHERE  CLT29.BRANCH = 'NCHC'
                          AND LESN_DATE < CLT09.ARR_DATE
                          AND CLT29.CLASS_NO IN (SELECT CLASS_NO
                                                 FROM   CLT09
                                                 WHERE  STATUS_CODE IN (SELECT ACTIVE_CODE
                                                                        FROM   HRC22
                                                                        WHERE  USE_TYPE = '2'
                                                                               AND ACTIVE_NOW = 'Y')
                                                        AND BRANCH = 'NCHC'
                                                        AND ARR_DATE >= '20101112'
                                                        AND ARR_DATE <= '20101212')
                   GROUP  BY CLT09.STUD_NO)AS B
         ON A.STUD_NO = B.STUD_NO
WHERE  CLM10.BRANCH = 'NCHC'
GROUP  BY A.ARR_DATE,
          B.NUMBER 

[飘零一叶]

SELECT A.ARR_DATE,B.NUMBER
FROM  
(
    SELECT BRANCH,ARR_DATE,CLASS_NO,STUD_NO FROM CLT09 WHERE 
    STATUS_CODE IN (SELECT ACTIVE_CODE FROM HRC22 WHERE USE_TYPE='2' AND ACTIVE_NOW='Y')
    AND BRANCH='NCHC' AND  ARR_DATE>='20101112' AND ARR_DATE<='20101212'
) AS A
INNER JOIN CLM10  ON A.CLASS_NO = CLM10.CLASS_NO  AND A.BRANCH = CLM10.BRANCH
INNER JOIN 
(
    SELECT (CLT09.STUD_NO)AS STUD_NO,(count(*)+1)AS NUMBER FROM CLT29
    left join CLT09 on CLT09.BRANCH=CLT29.BRANCH AND CLT09.CLASS_NO=CLT29.CLASS_NO WHERE CLT29.BRANCH='NCHC' AND LESN_DATE<CLT09.ARR_DATE 
    GROUP BY CLT09.STUD_NO
)AS B on A.CLASS_NO=B.CLASS_NO WHERE CLM10.BRANCH='NCHC' GROUP BY A.ARR_DATE,B.NUMBER

[gw6328]

第二个join 没有on

[飘零一叶]

SELECT A.ARR_DATE,B.NUMBER
FROM  
(
    SELECT BRANCH,ARR_DATE,CLASS_NO,STUD_NO FROM CLT09 WHERE 
    STATUS_CODE IN (SELECT ACTIVE_CODE FROM HRC22 WHERE USE_TYPE='2' AND ACTIVE_NOW='Y')
    AND BRANCH='NCHC' AND  ARR_DATE>='20101112' AND ARR_DATE<='20101212'
) AS A
INNER JOIN CLM10  ON A.CLASS_NO = CLM10.CLASS_NO  AND A.BRANCH = CLM10.BRANCH
INNER JOIN 
(
    SELECT (CLT09.STUD_NO)AS STUD_NO,(count(*)+1)AS NUMBER,CLASS_NO FROM CLT29
    left join CLT09 on CLT09.BRANCH=CLT29.BRANCH AND CLT09.CLASS_NO=CLT29.CLASS_NO WHERE CLT29.BRANCH='NCHC' AND LESN_DATE<CLT09.ARR_DATE 
    GROUP BY CLT09.STUD_NO
)AS B ON A.CLASS_NO=B.CLASS_NO WHERE  CLM10.BRANCH='NCHC' GROUP BY A.ARR_DATE,B.NUMBER

[zengpei99]

额 ，我的有X写，贴错了 源代码是这样的

SELECT A.ARR_DATE,B.NUMBER
FROM  
(
	SELECT BRANCH,ARR_DATE,CLASS_NO,STUD_NO FROM CLT09 WHERE 
	STATUS_CODE IN (SELECT ACTIVE_CODE FROM HRC22 WHERE USE_TYPE='2' AND ACTIVE_NOW='Y')
	AND BRANCH='NCHC' AND  ARR_DATE>='20101112' AND ARR_DATE<='20101212'
) AS A
INNER JOIN CLM10  ON A.CLASS_NO = CLM10.CLASS_NO  AND A.BRANCH = CLM10.BRANCH
INNER JOIN 
(
	SELECT (CLT09.STUD_NO)AS STUD_NO,(count(*)+1)AS NUMBER FROM CLT29
	left join CLT09 on CLT09.BRANCH=CLT29.BRANCH AND CLT09.CLASS_NO=CLT29.CLASS_NO WHERE CLT29.BRANCH='NCHC' 
	AND LESN_DATE<CLT09.ARR_DATE 
	AND CLT29.CLASS_NO IN (A.CLASS_NO)
	GROUP BY CLT09.STUD_NO
)AS B ON A.STUD_NO=B.STUD_NO  WHERE  CLM10.BRANCH='NCHC' GROUP BY A.ARR_DATE,B.NUMBER

[zengpei99]

谢谢各位哥哥姐姐们， 问题急啊！ 帮帮忙！

[飘零一叶]

SELECT A.ARR_DATE,B.NUMBER
FROM  
(
    SELECT BRANCH,ARR_DATE,CLASS_NO,STUD_NO FROM CLT09 WHERE 
    STATUS_CODE IN (SELECT ACTIVE_CODE FROM HRC22 WHERE USE_TYPE='2' AND ACTIVE_NOW='Y')
    AND BRANCH='NCHC' AND  ARR_DATE>='20101112' AND ARR_DATE<='20101212'
) AS A
INNER JOIN CLM10  ON A.CLASS_NO = CLM10.CLASS_NO  AND A.BRANCH = CLM10.BRANCH
INNER JOIN 
(
    SELECT (CLT09.STUD_NO)AS STUD_NO,(count(*)+1)AS NUMBER,CLASS_NO FROM CLT29
    left join CLT09 on CLT09.BRANCH=CLT29.BRANCH AND CLT09.CLASS_NO=CLT29.CLASS_NO WHERE CLT29.BRANCH='NCHC' 
    AND LESN_DATE<CLT09.ARR_DATE 
    GROUP BY CLT09.STUD_NO
)AS B ON A.STUD_NO=B.STUD_NO AND A.CLASS_NO=B.CLASS_NO  WHERE  CLM10.BRANCH='NCHC' GROUP BY A.ARR_DATE,B.NUMBER

[zengpei99]

TO:Dlut_LIuQ

我添加了CLASS_NO 按照你的方法去做，依然没有效果，仍然报 无法绑定由多个部分组成的标识符 "A.CLASS_NO"。

[zengpei99]

To:dawugui
不好意思，第一次发问题帖，下次改进。


To:wwwwgou
你的方法就是我现在用的，我想优化下语句，才提的问题。不过还是谢谢！