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

debugcn 发表于 Dev

阿米什特

我在Oracle中有一个SQL查询正在执行并提供正确的结果。但是，在MS SQL中，我在表连接方面遇到了问题，因此需要类似的结果。

以下是我的Oracle SQL：

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 SQL的FROM子句中联接表，与Oracle不同，我们也可以在WHERE子句中联接表。

当我尝试执行MS SQL查询时，出现以下错误：

Incorrect syntax near the keyword 'WHERE'.

注意：我正在使用SQL Server 2014，因此无法更改数据库change Compatibility Level。

您能帮我解决我在MS SQL查询中做错的事情吗？

戈登·利诺夫

我会在两个数据库中使用条件聚合。这是SQL 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]);

这不完全是您的查询。但这应该为您提供有关如何解决问题的正确思路。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。