一下午就是為了這一句SQL！
快累死了！
SELECT a.cnt_pass, DECODE (b.cnt_fail, NULL, 0, b.cnt_fail) cnt_fail,
e.lob_no, TO_CHAR (SYSDATE, 'YYYY-MM-DD ') today, a.line,
(a.cnt_pass + DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)) total_cnt,
DECODE ( ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '% ',
'% ', '100% ',
ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '% '
) pass_yield,
DECODE (c.cnt_repair, NULL, 0, cnt_repair) cnt_repair,
DECODE (d.cnt_ng, NULL, 0, d.cnt_ng) cnt_ng,
DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)
- ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
) wait_repair,
DECODE (ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
),
NULL, NULL,
ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
)
|| '% '
) repair_yield
FROM (SELECT SUM (a.mb_cnt) cnt_pass, a.line
FROM mb_cnt_by_time a
WHERE a.work_order= '06D000001E '
AND a.dept || a.station = 'D50 '
GROUP BY a.line
ORDER BY a.line) a,
(SELECT line, COUNT (barcode_no) cnt_fail
FROM (SELECT a.work_order, a.line, SUBSTR (f.item_no, 5) AS gr,
a.barcode_no
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
work_order_info_detail f,
data_station i
WHERE station = 10
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F '
AND a.work_order= '06D000001E '
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
AND a.work_order = f.work_order
AND f.TYPE = 'D '
UNION ALL
SELECT work_order, line, model AS gr, a.barcode_no
FROM repair_info a
WHERE dept = 'D '
AND SUBSTR (test_no, 1, 1) = 'T '
AND WORK_ORDER= '06D000001E ')
GROUP BY line) b,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_repair, a.line
FROM repair_info a,
(SELECT code, descript rma_des, responsibility_dept,
issue_belong
FROM repair_code_des
WHERE codetype <> 'E ') b,
(SELECT code ERROR_CODE, descript error_des
FROM repair_code_des
WHERE codetype = 'E ') c
WHERE a.code IS NOT NULL
AND SUBSTR (a.status, 2, 1) || SUBSTR (a.status, 5, 2) = 'D30 '
AND dept = 'D '
AND WORK_ORDER= '06D000001E '
AND complete_date IS NOT NULL
AND a.code = b.code(+)
AND bad_code = ERROR_CODE(+)
GROUP BY a.line
ORDER BY a.line) c,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_ng, a.line
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
repair_code_des g,
machine_test_info h,
data_station i
WHERE repair_date IS NOT NULL
AND station = 10
AND SUBSTR (i.data_station_no, 1, 1)
|| SUBSTR (i.data_station_no, 2, 2) = 'D30 '
AND g.code = b.repair_code
AND d.work_station_id = h.work_station_id
AND a.barcode_no = h.barcode_no
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F '
AND (a.WORK_ORDER= '06D000001E '
)
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
GROUP BY a.line
ORDER BY a.line) d,
mes_bn1.line_info e
WHERE a.line = b.line(+)
AND a.line = c.line(+)
AND a.line = d.line(+)
AND e.line_desc = a.line
AND e.unit_no = 'S '
ORDER BY e.lob_no, a.line
[解决办法]
UP
[解决办法]
够复杂
不知道效率咋样
[解决办法]
高，实在是高！~~~
[解决办法]
挺复杂的，支持一下楼主，佩服这样的精神~
不过，能不能分出几条简单的sql啊，这样的话，以后维护或者什么的都很方便~
难道非要用一条sql语句来实现嘛？
[解决办法]
用toad format过---^_^
[解决办法]
...看来业务逻辑比较复杂!
[解决办法]
偶怕怕......
[解决办法]
DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)
这句可以简化为
nvl(b.cnt_fail, 0)
[解决办法]
DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)
楼上所说的及是啊，呵呵，完全没有必要 用decode 吧！
没有实际环境，实在是很不喜欢看啊！
[解决办法]
这是为实现啥功能写的?
[解决办法]
把业务理清楚再说.
[解决办法]
还好了，不是很复杂，我曾经在C＋＋里面写过一个将近1000行的文件，动态拼SQL语句，没写死我撒！
[解决办法]
IS NOT NULL啥的也可以改改
[解决办法]
up