子查詢？!

[yin9184]

SELECT NAME,NUM,ADDRESS FROM
(SELECT A.NAME.A,NUM,A.ADDRESS,B.DATE FROM
(SELECT NAME,NUM,ADDRESS FROM TABLEA ) A
INNER JOIN
(SELECT DATE,SAL FROM TABLEB) B ON XX XX XXX ) S
这个子查询，麻烦大家帮看下，提示没有为 S的数据行指定任何数据行，是为什么？该怎么改

[wangdehao]

貌似没有问题

[paoluo]

直接
SELECT A.NAME.A,NUM,A.ADDRESS FROM TABLEA A
INNER JOIN TABLEB B
On XXX

不就可以

[w75251455]

要不就是还有什么东西没写..

[yin9184]

我現在想把 (SELECT A.NAME.A,NUM,A.ADDRESS,B.DATE FROM
(SELECT NAME,NUM,ADDRESS FROM TABLEA ) A
INNER JOIN
(SELECT DATE,SAL FROM TABLEB) B ON XX XX XXX )
做為一個結果 S，然後在連接另外個表 H ，然後SELECT,因為這個SELECT要求求SUM，在分類，問題是S老出錯

[paoluo]

你這段代碼是完整的嗎？

這段代碼本身沒有錯誤的。

[yin9184]

SELECT FACT_NO,YEAR_M,SEC=CASE COST_KIND WHEN '06' THEN 'Z0004' WHEN '12' THEN 'Z0005' WHEN '13' THEN 'Z0006' WHEN '14' THEN 'Z0007' END,
SEC_NM=CASE COST_KIND WHEN '06' THEN 'AA' WHEN '12' THEN 'BB' WHEN '13' THEN 'BB' WHEN '14' THEN 'DD' END,COST_KIND,
SUM(SEC_NUM) AS SEC_NUM,SUM(WT_TOTAL) AS WT_TOTAL,SUM(WT_ACTION) AS WT_ACTION,SUM(BASE) AS BASE,SUM(BONUS) AS BONUS,SUM(TRAIN_BONUS) AS TRAIN_BONUS,SUM(JS) AS JS,
SUM(SPECIAL) AS SPECIAL,SUM(OVERPAY) AS OVERPAY,SUM(ADDITION) AS ADDITION,SUM(DEDUCT) AS DEDUCT,SUM(SS) AS SS,SUM(TAX) AS TAX,SUM(SAL_Y) AS SAL_Y,SUM(SALARY) AS SALARY,0,SUM(ILL_HOUR),SUM(ILL_SAL),SUM(SS_ADD),SUM(EV_ADD)
FROM

(SELECT A.FACT_NO,A.YEAR_M,A.SEC,C.NAME AS SEC_NM,C.FLAG AS COST_KIND,A.SEC_NUM,B.WT_TOTAL,B.WT_ACTION,A.BASE,A.BONUS,A.TRAIN_BONUS,A.JS,
A.SPECIAL,A.OVERPAY,A.ADDITION,A.DEDUCT,A.SS,A.TAX,A.SAL_Y,A.SALARY,0,B.ILL_HOUR,B.ILL_SAL,A.SS_ADD,A.EV_ADD
FROM
(SELECT FACT_NO, YEAR_M, SEC, SUM(BASE) AS BASE, SUM(BONUS) AS BONUS, SUM(TRAIN_BONUS) AS TRAIN_BONUS,SUM(JS) AS JS, SUM(SPECIAL) AS SPECIAL, SUM(OVERPAY) AS OVERPAY, SUM(ADDITION) AS ADDITION, SUM(DEDUCT) AS DEDUCT, SUM(SS) AS SS,SUM(TAX) AS TAX, SUM(SAL_Y) AS SAL_Y, SUM(SALARY) AS SALARY,SUM(SS_ADD) AS SS_ADD,SUM(EV_ADD) AS EV_ADD, COUNT(*) AS SEC_NUM
FROM dbo.TABLEA S
GROUP BY FACT_NO, YEAR_M, SEC) A INNER JOIN
(SELECT E.FACT_NO, E.YEAR_M, E.SEC, SUM(E.WK_TOTAL) AS WT_TOTAL, SUM(E.WK_ACTION) AS WT_ACTION,SUM(E.ILL_HOUR) AS ILL_HOUR,SUM(E.ILL_HOUR*(F.BASE+F.JS)/20.92/8) AS ILL_SAL FROM TABLEB E LEFT OUTER JOIN TABLEC F ON E.FACT_NO=F.FACT_NO AND E.PNL=F.PNL
GROUP BY E.FACT_NO, E.YEAR_M, E.SEC) B ON A.FACT_NO=B.FACT_NO AND A.YEAR_M=B.YEAR_M AND A.SEC=B.SEC INNER JOIN DEPART C ON A.FACT_NO=C.FACT_NO AND A.SEC=C.SEC WHERE A.YEAR_M='200701') S

WHERE YEAR_M='200701'
代碼可能有點亂，呵呵
執行後就是下面的提示
伺服器: 訊息 8155，層級 16，狀態 2，行 1
沒有為 'S' 的資料行 21 指定任何資料行。

[paoluo]

問題出在這裡

SUM(SALARY) AS SALARY,0,SUM(ILL_HOUR)

沒有為這個0指定列名

SUM(SALARY) AS SALARY,0 As aaaa,SUM(ILL_HOUR)

指定一個別名即可