无法将查询从Oracle SQL传输到具有多个联接的MS

Question

我在Oracle中有一个SQL查询，它正在执行并给出正确的结果。但是，在MS中，我需要一个类似的结果，在这里，我遇到了有关表联接的问题。

下面是我的Oracle

SELECT
    number_of_inspection.line,
    number_of_inspection.week,
    number_of_inspection.total_records,
    ( visual_fails.fails + qc_fails.fails ) total_fails,
    nvl(round((100 / number_of_inspection.total_records) *(visual_fails.fails + qc_fails.fails), 2), 0) percentage_fails
FROM
    (
        SELECT
            COUNT(*) total_records,
            line,
            to_char(inspection_date, 'IW') week
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND inspection_date >= add_months(sysdate, - 3)
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) number_of_inspection,
    (
        SELECT
            line,
            to_char(inspection_date, 'IW') week,
            COUNT(*) fails
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND visual_inspection = 'FAIL'
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) visual_fails,
    (
        SELECT
            line,
            to_char(inspection_date, 'IW') week,
            COUNT(*) fails
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND visual_inspection != 'FAIL'
            AND nvl(qc_inspection, 'FAIL') = 'FAIL'
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) qc_fails
WHERE
    number_of_inspection.line = visual_fails.line (+)
    AND number_of_inspection.week = visual_fails.week (+)
    AND number_of_inspection.line = qc_fails.line (+)
    AND number_of_inspection.week = qc_fails.week (+)

我在Oracle中得到的正确结果是：

+--------+------+---------------+------------+-----------------+
| LINE   | WEEK | TOTAL_RECORDS |TOTAL_FAILS | PERCENTAGE_FAILS|
+--------+------+---------------+------------+-----------------+
| Line 1 |  08  |   845         |    6       |   0.71          |
| Line 2 |  08  |   1790        |    11      |   0.61          |
| Line 1 |  09  |   350         |    9       |   2.57          |
| Line 2 |  09  |   1125        |    20      |   1.77          |
+--------+------+---------------+------------+-----------------+

下面是我的MS SQL查询：

SELECT number_of_inspection.line, 
       number_of_inspection.week_no, 
       number_of_inspection.total_records, 
       ( visual_fails.fails + qc_fails.fails ) total_fails, 
       Isnull(Round(( 100 / number_of_inspection.total_records ) * ( 
                           visual_fails.fails + qc_fails.fails ), 2), 0) 
                                               percentage_fails 
FROM   (SELECT Count(*)                        total_records, 
               line, 
               Datepart(ww, [inspection date]) Week_No 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [inspection date] >= Dateadd(month, -1, Getdate()) 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) number_of_inspection, 
       (SELECT line, 
               Datepart(ww, [inspection date]) AS Week_No, 
               Count(*)                        AS fails 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [visual inspection] = 'FAIL' 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) visual_fails, 
       (SELECT line, 
               Datepart(ww, [inspection date]) AS Week_No, 
               Count(*)                        AS fails 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [visual inspection] != 'FAIL' 
               AND Isnull([qc inspection], 'FAIL') = 'FAIL' 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) qc_fails, 
       where number_of_inspection 
       LEFT OUTER JOIN visual_fails 
                    ON number_of_inspection.line = visual_fails.line 
                       -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN visual_fails 
                    ON number_of_inspection.week_no = visual_fails.week_no 
                       -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN qc_fails 
                    ON number_of_inspection.line = qc_fails.line -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN qc_fails 
                    ON number_of_inspection.week_no = qc_fails.week_no -- join issue 

我知道，我们从MS中的子句中加入了表，不像Oracle，我们也可以加入 where 子句。

当我尝试执行MS查询时，会得到以下错误：

Incorrect syntax near the keyword 'WHERE'.

注意:我正在使用Server 2014，所以我不能更改数据库以更改Compatibility Level。

你能帮我解决我在MS查询中做错了什么吗？

Answer 1

我会在这两个数据库中使用条件聚合。下面是Server中的想法：

select line, datepart(week, [Inspection Date]), 
       count(*) total_records,
       sum(case when visual_inspection = 'FAIL' then 1 else 0 end) as num_visual_fails,
       sum(case when [QC Inspection] = 'FAIL' or [QC Inspection] is null then 1 else 0 end) as num_qc_fails,
       avg(case when visual_inspection = 'FAIL' or
                     [QC Inspection] = 'FAIL' or
                     [QC Inspection] is null
                then 1.0 else 0
            end) as fail_ratio
       . . . 
from master_table
where year(inspection_date) = year(getdate()) and
      inspection_date >= dateadd(month, -3, getdate())
group by line, datepart(week, [Inspection Date]);

这不是你的问题。但它应该给你正确的想法，如何处理这个问题。

Answer 2

我推荐Gordon的答案来改进您的代码，但我也希望您能够理解如何将Oracle的旧连接样式转换为现代标准连接语法(这在Oracle中也适用)。

为了提高可读性，我编辑了内联视图子查询的内容，但我认为这样可以理解。

SELECT
    number_of_inspection.line,
    number_of_inspection.Week_No,
    number_of_inspection.total_records,
    (visual_fails.fails + qc_fails.fails) total_fails,
    ISNULL(ROUND((100/number_of_inspection.total_records)*(visual_fails.fails + qc_fails.fails),2),0) percentage_fails
FROM
    (...) number_of_inspection
LEFT OUTER JOIN 
    (...) visual_fails
    ON number_of_inspection.line = visual_fails.line
    AND number_of_inspection.Week_No = visual_fails.Week_No
LEFT OUTER JOIN
    (...) qc_fails
    ON number_of_inspection.line = qc_fails.line
    AND number_of_inspection.Week_No = qc_fails.Week_No
;