SELECT * FROM (SELECT c.planYear,c.orgId,d.ONAME,e.name AS typeName,f.name AS sizeName,c.type,c.size,c.quantityAll,c.arrivalDate,c.quantitys,c.dealData,
(c.quantityAll-c.quantitys) AS other FROM SM_LabourLedger AS a,SM_LabourLedgerList AS b,
(SELECT d.orgId,d.type,d.size,b.planYear,a.arrivalDate,c.quantity AS quantityAll,d.quantitys,d.dealData FROM SM_GrantPlanList AS a,
SM_GrantPlan AS b,SM_GrantPlanDepList AS c,
(SELECT b.orgId,a.type,a.size,sum(quantity) AS quantitys,max(dealDate) AS dealData FROM SM_LabourLedger AS a,SM_LabourLedgerList AS b
WHERE a.id = b.llId AND b.type = '1' GROUP BY b.orgId,a.type,a.size) AS d
WHERE a.size = d.size AND b.type = d.type AND b.id = a.gpId AND c.orgId = d.orgId) AS c
LEFT JOIN T_ORGANIZATION AS d ON c.orgId = d.OID
LEFT JOIN T_CODETABLE AS e ON e.Code = 'SMEMPSIZETYPE'
LEFT JOIN T_CODETABLE AS f ON f.Code = 'SMEMPSIZENAME'
WHERE a.type = c.type AND a.type = e.value AND a.size = c.size AND a.size = f.value AND b.type = '1' AND b.orgId = c.orgId
GROUP BY c.planYear,c.orgId,c.type,c.size,c.quantityAll,c.arrivalDate,d.ONAME,e.name,f.name,c.quantitys,c.dealData
)AS TEMP
(c.quantityAll-c.quantitys) AS other FROM SM_LabourLedger AS a,SM_LabourLedgerList AS b,
(SELECT d.orgId,d.type,d.size,b.planYear,a.arrivalDate,c.quantity AS quantityAll,d.quantitys,d.dealData FROM SM_GrantPlanList AS a,
SM_GrantPlan AS b,SM_GrantPlanDepList AS c,
(SELECT b.orgId,a.type,a.size,sum(quantity) AS quantitys,max(dealDate) AS dealData FROM SM_LabourLedger AS a,SM_LabourLedgerList AS b
WHERE a.id = b.llId AND b.type = '1' GROUP BY b.orgId,a.type,a.size) AS d
WHERE a.size = d.size AND b.type = d.type AND b.id = a.gpId AND c.orgId = d.orgId) AS c
LEFT JOIN T_ORGANIZATION AS d ON c.orgId = d.OID
LEFT JOIN T_CODETABLE AS e ON e.Code = 'SMEMPSIZETYPE'
LEFT JOIN T_CODETABLE AS f ON f.Code = 'SMEMPSIZENAME'
WHERE a.type = c.type AND a.type = e.value AND a.size = c.size AND a.size = f.value AND b.type = '1' AND b.orgId = c.orgId
GROUP BY c.planYear,c.orgId,c.type,c.size,c.quantityAll,c.arrivalDate,d.ONAME,e.name,f.name,c.quantitys,c.dealData
)AS TEMP
本来是没有格式的,我还是把这条SQL格式化了一下。
还有这B人的代码,百分之五十是废的。测试提了一个BUG,我找啊找,那个1000行代码的class。看得我发晕,一怒之下直接Delete,结果程序还是好好的。
我服了。
好几个Class我直接delete掉程序照跑。这人写代码不管有没有用。只要结果能跑通就OK。
唉,终于要离开软件这一行了。明天上完最后一天班就离职。
不干这一行了。
SELECT *
FROM ( SELECT c.planYear ,
c.orgId ,
d.ONAME ,
e.name AS typeName ,
f.name AS sizeName ,
c.type ,
c.size ,
c.quantityAll ,
c.arrivalDate ,
c.quantitys ,
c.dealData ,
( c.quantityAll - c.quantitys ) AS other
FROM SM_LabourLedger AS a ,
SM_LabourLedgerList AS b ,
( SELECT d.orgId ,
d.type ,
d.size ,
b.planYear ,
a.arrivalDate ,
c.quantity AS quantityAll ,
d.quantitys ,
d.dealData
FROM SM_GrantPlanList AS a ,
SM_GrantPlan AS b ,
SM_GrantPlanDepList AS c ,
( SELECT b.orgId ,
a.type ,
a.size ,
SUM(quantity) AS quantitys ,
MAX(dealDate) AS dealData
FROM SM_LabourLedger AS a ,
SM_LabourLedgerList AS b
WHERE a.id = b.llId
AND b.type = '1'
GROUP BY b.orgId ,
a.type ,
a.size
) AS d
WHERE a.size = d.size
AND b.type = d.type
AND b.id = a.gpId
AND c.orgId = d.orgId
) AS c
LEFT JOIN T_ORGANIZATION AS d ON c.orgId = d.OID
LEFT JOIN T_CODETABLE AS e ON e.Code = 'SMEMPSIZETYPE'
LEFT JOIN T_CODETABLE AS f ON f.Code = 'SMEMPSIZENAME'
WHERE a.type = c.type
AND a.type = e.value
AND a.size = c.size
AND a.size = f.value
AND b.type = '1'
AND b.orgId = c.orgId
GROUP BY c.planYear ,
c.orgId ,
c.type ,
c.size ,
c.quantityAll ,
c.arrivalDate ,
d.ONAME ,
e.name ,
f.name ,
c.quantitys ,
c.dealData
) AS TEMP
更牛B的是写的JAVA类,我直接删掉对程序没影响。
SELECT *
FROM (SELECT c.planYear
,c.orgId
,d.ONAME
,e.name AS typeName
,f.name AS sizeName
,c.type
,c.size
,c.quantityAll
,c.arrivalDate
,c.quantitys
,c.dealData
,(c.quantityAll - c.quantitys) AS other
FROM SM_LabourLedger AS a
,SM_LabourLedgerList AS b
,(SELECT d.orgId
,d.type
,d.size
,b.planYear
,a.arrivalDate
,c.quantity AS quantityAll
,d.quantitys
,d.dealData
FROM SM_GrantPlanList AS a
,SM_GrantPlan AS b
,SM_GrantPlanDepList AS c
,(SELECT b.orgId
,a.type
,a.size
,sum(quantity) AS quantitys
,max(dealDate) AS dealData
FROM SM_LabourLedger AS a
,SM_LabourLedgerList AS b
WHERE a.id = b.llId
AND b.type = '1'
GROUP BY b.orgId,a.type,a.size) AS d
WHERE a.size = d.size
AND b.type = d.type
AND b.id = a.gpId
AND c.orgId = d.orgId) AS c
LEFT JOIN T_ORGANIZATION AS d
ON c.orgId = d.OID
LEFT JOIN T_CODETABLE AS e
ON e.Code = 'SMEMPSIZETYPE'
LEFT JOIN T_CODETABLE AS f
ON f.Code = 'SMEMPSIZENAME'
WHERE a.type = c.type
AND a.type = e.value
AND a.size = c.size
AND a.size = f.value
AND b.type = '1'
AND b.orgId = c.orgId
GROUP BY c.planYear,c.orgId,c.type,c.size,c.quantityAll,
c.arrivalDate,d.ONAME,e.name,f.name,c.quantitys,
c.dealData) AS TEMP
呵呵
貌似我以前也在oracle中写过这样的语句
我也最讨厌 可以用厌恶来形容这种写代码的人。 发自内心的BS。。LZ不用灰心呀 咋个要离开软件行业呢!!
SELECT *
FROM (SELECT c.planYear,c.orgId,d.ONAME,e.name AS typeName,f.name AS sizeName,c.type,c.size,c.quantityAll,c.arrivalDate,
c.quantitys,c.dealData,(c.quantityAll-c.quantitys) AS other
FROM SM_LabourLedger AS a,SM_LabourLedgerList AS b,(SELECT d.orgId,d.type,d.size,b.planYear,a.arrivalDate,c.quantity AS
quantityAll,d.quantitys,d.dealData
FROM SM_GrantPlanList AS a,SM_GrantPlan AS b,
SM_GrantPlanDepList AS c,(SELECT b.orgId,a.type,a.size,
SUM(quantity) AS quantitys,MAX(dealDate) AS dealData
FROM SM_LabourLedger AS
a,
SM_LabourLedgerList AS
b
WHERE a.id = b.llId
AND b.type = '1'
GROUP BY b.orgId,a.type,a.size
) AS d
WHERE a.size = d.size
AND b.type = d.type
AND b.id = a.gpId
AND c.orgId = d.orgId
) AS c
LEFT JOIN T_ORGANIZATION AS d ON c.orgId = d.OID
LEFT JOIN T_CODETABLE AS e ON e.Code = 'SMEMPSIZETYPE'
LEFT JOIN T_CODETABLE AS f ON f.Code = 'SMEMPSIZENAME'
WHERE a.type = c.type
AND a.type = e.value
AND a.size = c.size
AND a.size = f.value
AND b.type = '1'
AND b.orgId = c.orgId
GROUP BY c.planYear,c.orgId,c.type,c.size,c.quantityAll,c.arrivalDate,d.ONAME,e.name,f.name,c.quantitys,c.dealData
) AS TEMP
一是乱
二是增加了复杂逻辑