[MySQL] ひとつのテーブルの値を複数回使う

ひとつのテーブルにある値を複数にわたって処理する場合、AS句を使う、JOINを使うなどで処理することができる。

もっとスマートな方法があるハズ・・・。

SELECT
T2.*,
T_TTL.*
FROM
(SELECT
`SK6_TC_SLIP`.StaffID as StaffID2,
sum(truncate((Quantity * CostIn)+0.005,0))  as CostTTL
FROM
`SK6_TC_SLIP`,
`SK6_TC_ITEMS`
WHERE
`SK6_TC_SLIP`.StaffID = 16
AND
`SK6_TC_SLIP`.SystemID = 801
AND
`SK6_TC_SLIP`.GroupID = 18
AND
`SK6_TC_SLIP`.`SlipDate` Between '20070901' AND '20070931'
AND
`SK6_TC_SLIP`.ItemID=`SK6_TC_ITEMS`.ItemID
Group By StaffID
)AS T_TTL LEFT JOIN(
SELECT
`SK6_TC_SLIP`.SlipDate,
`SK6_TC_SLIP`.SlipID,
`SK6_TC_ITEMS`.ItemID,
concat(
G_ClassBig.Var1,
'(', G_ClassMid.Var1, ') ',
G_From.Var1, '→', G_To.Var1
) as ItemName,
`SK6_TC_SLIP`.Quantity,
`SK6_TC_ITEMS`.CostIn,
truncate((Quantity * `SK6_TC_ITEMS`.CostIn)+0.005,0)  as CostRec,
`SK6_TC_SLIP`.StaffID,
G_Staff.Var1 as StaffName,
`SK6_TC_SLIP`.CarNum
FROM
`SK6_TC_SLIP`,
`SK6_TC_ITEMS`,
`SK6_GROUP_VARS` as G_ClassBig,
`SK6_GROUP_VARS` as G_ClassMid,
`SK6_GROUP_VARS` as G_From,
`SK6_GROUP_VARS` as G_To,
`SK6_GROUP_VARS` as G_Staff
WHERE
`SK6_TC_SLIP`.StaffID = 16
AND
`SK6_TC_SLIP`.SystemID = 801
AND
`SK6_TC_SLIP`.GroupID = 18
AND
`SK6_TC_SLIP`.`SlipDate` Between '20070901' AND '20070931'
AND
`SK6_TC_SLIP`.ItemID = `SK6_TC_ITEMS`.ItemID
AND
G_ClassBig.`VarGroupID` = 100 AND G_ClassBig.`VarID` = substr(`SK6_TC_SLIP`.ItemID, 1, 3)
AND
G_ClassMid.`VarGroupID` = 101 AND G_ClassMid.`VarID` = substr(`SK6_TC_SLIP`.ItemID,4, 3)
AND
G_From.`VarGroupID` = 200 AND G_From.`VarID` = substr(`SK6_TC_SLIP`.ItemID, 7, 3)
AND
G_To.`VarGroupID` = 210 AND G_To.`VarID` = substr(`SK6_TC_SLIP`.ItemID, 10, 3)
AND
G_Staff.`VarGroupID` =850 AND G_Staff.`SystemID` =801 AND G_Staff.`GroupID` =18 AND G_Staff.VarID=`SK6_TC_SLIP`.StaffID
Order by
SlipDate, SlipID
)AS T2 ON T2.StaffID = T_TTL.StaffID2