select DP.CODE,DP.NAME,
(case when DP.code like 'A%' THEN 'A000' ELSE DP.ID END) AS T_ID ,
(case when DP.code like 'A%' THEN '管理部' ELSE DP.NAME END) AS T_NAME
FROM SA
left joio DP on SA.ID = DP.ID
中間還有一些計算式(太長.就略) ,想要用T_ID 做與其他TABLE LEFT JOIN,但CASE WHEN出來的值不能做判斷及GROUP BY或LEFT JOIN,請問要如何改??謝謝
已邀請的邦友 {{ invite_list.length }}/5
子查詢應該可以吧
SELECT A.* FROM
(
select DP.CODE,DP.NAME,
(case when DP.code like 'A%' THEN 'A000' ELSE DP.ID END) AS T_ID ,
(case when DP.code like 'A%' THEN '管理部' ELSE DP.NAME END) AS T_NAME
FROM SA
left joio DP on SA.ID = DP.ID
) AS A
JOIN tableName..........
感謝asd831012大大,您教的可以,但又有一個問題如下
想把內層的group by使用外層的T_ID做.可以嗎
SA_1.ID,改成A.T_ID
SELECT A.* FROM
(
select DP.CODE,DP.NAME,
(case when DP.code like 'A%' THEN 'A000' ELSE DP.ID END) AS T_ID ,
(case when DP.code like 'A%' THEN '管理部' ELSE DP.NAME END) AS T_NAME,
(SELECT B.B1 FROM B
LEFT JOIN SA SA_1 ON SA_1.ID = B.ID
GROUP SA_1.ID ) →想把SA_1.ID,改成A.T_ID
FROM SA
left joio DP on SA.ID = DP.ID
) AS A
JOIN tableName..........
不太懂你的意思，但是看上面你所想要的輸出應該是你現在所撈出來的
Table再去JOIN到另一個Table裡面有金額然後SUM出來
SELECT A.DP.CODE,A.DP.NAME,SUM(B.money) FROM
(
select DP.CODE,DP.NAME,
(case when DP.code like 'A%' THEN 'A000' ELSE DP.ID END) AS T_ID ,
(case when DP.code like 'A%' THEN '管理部' ELSE DP.NAME END) AS T_NAME
FROM SA
left joio DP on SA.ID = DP.ID
) AS A
JOIN tableName AS B ..........
GROUP BY A.DP.CODE,A.DP.NAME
應該是說...在加內層 一組SELECT
(SELECT B.B1 FROM B
LEFT JOIN SA SA_1 ON SA_1.ID = B.ID
GROUP SA_1.ID ) →想把SA_1.ID,改成A.T_ID
但內層的GROUP BY 想要用外層的A.T_ID做GORUP 可行嗎??
那直接加在最外層不行嗎?
iThome鐵人賽
看影片追技術