LEFTJOINwithanAND加上IN運算子給出錯誤：在預期條件的背景關系中指定的非布爾型別的運算式

我正在嘗試將 MySQL 查詢移植到 SQL Server。該查詢在 MySQL 中作業正常，但在 SQL Server 中，它失敗并顯示以下錯誤：

在預期條件的背景關系中指定的非布爾型別的運算式

我懷疑這是因為 SQL Server 不喜歡我在左連接條件中使用AND與IN運算子耦合的事實。

詢問

SELECT *
FROM
  (SELECT dol.Id AS 'log_id',
          c.Id AS 'computer_id',
          COALESCE(r.processor_test, 'Skipped') as 'processor_test',
  COALESCE(r.display_test, 'Skipped') as 'display_test',
  COALESCE(r.storage_test, 'Skipped') as 'storage_test',
  COALESCE(r.network_test, 'Skipped') as 'network_test',
  COALESCE(r.keyboard_test, 'Skipped') as 'keyboard_test',
  COALESCE(r.mouse_test, 'Skipped') as 'mouse_test',
          COALESCE(user_fields.customer_id, 'None') AS 'customer_id',
          COALESCE(user_fields.order_no, 'None') AS 'order_no',
          COALESCE(user_fields.technician_id, 'None') AS 'technician_id',
          CONCAT('Customer ID: ', customer_id, '<br>Order No: ', order_no, '<br>Technician ID: ', technician_id) AS 'userfields',
          hd.Id AS 'hard_disk_id',
          dol.StartTime AS 'start_time',
          hd.Serial AS 'hard_disk_serial',
          CONCAT(hd.Gigabytes, ' GB') AS 'gigabytes',
          CONCAT('<b>Vendor: </b>', hd.Vendor, '<br><br><b>Model: </b>', hd.Product, '<br><br><b>Serial: </b>', hd.Serial, '<br><br><b>Size: </b>', hd.Gigabytes, ' GB<br><br><b>Pre-wipe SMART health status: ', dol.SmartOverallPre, '<br><br><b>Post-wipe SMART health status: ', dol.SmartOverallPost) AS 'target_drive_detailed',
          CONCAT('Vendor: ', hd.Vendor, '<br><br>Product: ', hd.Product, '<br><br>Serial: ', hd.Serial, '<br><br>Drive Media Type: ', hd.DriveMediaType , '<br><br>Size: ', hd.Gigabytes, ' GB<br><br><b>Interface: ', hd.Interface) AS 'drive_detailed',
          CONCAT(hd.Serial, ' (',hd.Gigabytes, ' GB)') AS 'target_drive',
          CASE
              WHEN dol.PatternName = 'NIST 800-88r1 (recommended)' THEN 'NIST 800-88r1'
              ELSE dol.PatternName
          END AS 'pattern_name',
          CASE
              WHEN dol.Success = '1' THEN 'SUCCESS'
              WHEN dol.Success = '0' THEN 'FAILED'
          END AS 'wipe_status',
          dol.Errors AS 'errors',
          dol.DirtySectors AS 'dirty_sectors',
          dol.Tool AS 'tool',
          dol.Kernel AS 'kernel',
          dol.JobUUID AS 'job_uuid',
          dol.UUID AS 'uuid',
          CONVERT(VARCHAR(20), dol.EndTime, 120) AS 'end_time',
          substring(CONVERT(VARCHAR(20), dol.EndTime, 120), 1, 11) AS 'end_time_short',
          dol.NumPasses AS 'num_passes',
          dol.TrimPasses AS 'trim_passes',
          dol.SectorsOverwritten AS 'sectors_overwritten',
          dol.SectorsNotOverWritten AS 'sectors_not_overwritten',
          dol.SectorsVerified AS 'sectors_verified',
          dol.CustomField AS 'custom_field_legacy',
          CASE
              WHEN dol.NISTMethodTypeId = '1' THEN 'Unknown'
              WHEN dol.NISTMethodTypeId = '2' THEN 'Clear'
              WHEN dol.NISTMethodTypeId = '3' THEN 'Purge'
          END AS 'nis_method_type',
          CASE 
              WHEN dol.DCOFound = '1' AND dol.DcoRemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ') 
              WHEN dol.DCOFound = '0' AND dol.DcoRemoved = '0' THEN CONCAT('No /</td><td> No', ' ') 
              WHEN dol.DCOFound = '1' AND dol.DcoRemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ') 
              WHEN dol.DCOFound = '0' AND dol.DcoRemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ') 
          END AS 'dco_foundremoved',
          CASE
              WHEN dol.DCOLocked = '1' THEN 'Yes'
              WHEN dol.DCOLocked = '0' THEN 'No'
          END AS 'dco_locked',
          CASE 
              WHEN dol.HPAFound = '1' AND dol.HPARemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ') 
              WHEN dol.HPAFound = '0' AND dol.HPARemoved = '0' THEN CONCAT('No /</td><td> No', ' ') 
              WHEN dol.HPAFound = '1' AND dol.HPARemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ') 
              WHEN dol.HPAFound = '0' AND dol.HPARemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ') 
          END AS 'hpa_foundremoved',
          CASE 
              WHEN dol.AMAXFound = '1' AND dol.AMAXRemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ') 
              WHEN dol.AMAXFound = '0' AND dol.AMAXRemoved = '0' THEN CONCAT('No /</td><td> No', ' ') 
              WHEN dol.AMAXFound = '1' AND dol.AMAXRemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ') 
              WHEN dol.AMAXFound = '0' AND dol.AMAXRemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ') 
          END AS 'amax_foundremoved',
          c.Vendor AS 'vendor',
          c.Model AS 'computer_model',
          c.ComputerSerial AS 'computer_serial',
          CONCAT('<b>Vendor: </b>', c.Vendor, '<br><br><b>Model: </b>', c.Model, '<br><br><b>Serial: </b>', c.ComputerSerial) as 'computer_summary',
          CONCAT('<b>Vendor: </b>', c.MotherboardVendor, '<br><br><b>Model: </b>', c.MotherboardModel) as 'motherboard_summary',
          CONCAT(first_cpu.Name, ' ', first_cpu.Speed) as 'cpu_summary',
          CONCAT(first_nic.Vendor, '<br><br>', first_nic.Product) as 'nic_summary',
          CONCAT(first_vc.Vendor, '<br><br>', first_vc.Product) as display_summary,
          CONCAT('<b>USB1 Ports: </b>', c.UsbPorts, '<br><br><b>USB2 Ports: </b>', c.Usb2Ports, '<br><br><b>USB3 Ports: </b>', c.Usb3Ports) as usb_ports,
          c.Memory AS 'computer_memory',
          hd.DriveMediaType AS 'hard_disk_drive_media_type',
          hd.Product AS 'hard_disk_product',
          hd.Vendor AS 'hard_disk_vendor'
   FROM DiskOperationLog dol
   LEFT JOIN
     (SELECT juf.JobUUID AS 'juf_job_uuid',
             COALESCE(max(CASE
                              WHEN juf.FieldName = 'Customer ID' THEN juf.FieldValue
                          END), 'None') AS customer_id,
             COALESCE(max(CASE
                              WHEN juf.FieldName = 'Order No' THEN juf.FieldValue
                          END), 'None') AS order_no,
             COALESCE(max(CASE
                              WHEN juf.FieldName = 'Technician ID' THEN juf.FieldValue
                          END), 'None') AS technician_id
      FROM DiskOperationLog dol
      INNER JOIN JobUserFields juf ON dol.JobUUID = juf.JobUUID
      GROUP BY juf.JobUUID) user_fields ON dol.JobUUID = user_fields.juf_job_uuid
   LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId and (hd.Id, dol.EndTime) IN (select max(hd.Id) as Id, max(dol.EndTime) as EndTime from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)
   LEFT JOIN Computer c ON c.Id = hd.ComputerId
   LEFT JOIN
(select t.computer_id, 
    MAX(
        CASE 
            WHEN Type='Processor' 
            THEN Result
            ELSE NULL
        END
    ) AS 'processor_test',
    MAX(
        CASE 
            WHEN Type='Display' 
            THEN Result
            ELSE NULL
        END
    ) AS 'display_test',
    MAX(
        CASE
            WHEN Type='Network' 
            THEN Result
            ELSE NULL
        END
    ) AS 'network_test',
    MAX(
        CASE 
            WHEN Type='Keyboard'
            THEN Result
            ELSE NULL
        END
    ) AS 'keyboard_test',
    MAX(
        CASE 
            WHEN Type='Mouse'
            THEN Result
            ELSE NULL
        END
    ) AS 'mouse_test',
    MAX(
        CASE 
            WHEN Type='Storage'
            THEN Result
            ELSE NULL
        END
    ) AS 'storage_test'
  from (    
    select c.Id as computer_id,
        hst.Type, 
        case min(
            case hst.Result 
              when'Fail' then 1
              when'Skipped' then 2
              when'Pass' then 3
            end) 
          when 1 then 'Fail' 
          when 2 then 'Skipped'     
          when 3 then 'Pass'
        end Result
    from Computer c
    inner join HardwareTest ht on c.Id = ht.ComputerId
    inner join HardwareSubTest hst on ht.Id = hst.HardwareTestId
    group by c.Id, hst.Type
    ) t
 group by computer_id) r on c.Id = r.computer_id
   JOIN
     (SELECT *
      FROM Cpu cpu
      WHERE Id in
          (SELECT min(Id)
           FROM Cpu
           GROUP BY ComputerId)) AS first_cpu ON c.Id = first_cpu.ComputerId
   JOIN
     (SELECT *
      FROM Nic nic
      WHERE Id in
          (SELECT min(Id)
           FROM Nic
           GROUP BY ComputerId)) AS first_nic ON c.Id = first_nic.ComputerId
   JOIN
     (SELECT *
      FROM VideoCard vc
      WHERE Id in
          (SELECT min(Id)
           FROM VideoCard
           GROUP BY ComputerId)) AS first_vc ON c.Id = first_vc.ComputerId) AS query_result

我不知道為什么，SQL Server 支持 in ANDa的使用LEFT JOIN。我錯過了什么？

uj5u.com熱心網友回復：

SQL Server 支持在 JOIN 中使用 AND，但您不能匹配多個列。SQL 服務器只允許一列。

嘗試通過以下方式更改您的連接：

   LEFT JOIN HardDisk hd 
   ON hd.Id = dol.HardDiskId 
   and hd.Id in (select max(hd.Id) from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)
   AND dol.EndTime in (select max(dol.EndTime) from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)

uj5u.com熱心網友回復：

如另一個答案中所述，SQL Server 不支持行值比較器。

但是為了避免重復代碼，您可以使用 APPLY

   OUTER APPLY (
      select
        MaxId = max(hd.Id),
        MaxEndTime = max(dol.EndTime)
      from DiskOperationLog dol2
      join HardDisk hd on hd.Id = dol2.HardDiskId
      group by hd.Id
   ) dol2
   LEFT JOIN HardDisk hd 
   ON hd.Id = dol.HardDiskId 
   and hd.Id = dol2.MaxId
   AND dol.EndTime = dol2.MaxEndTime

我必須說，整個查詢看起來可以用大量的視窗函式來完成，尤其是上面的這一部分。

uj5u.com熱心網友回復：

仔細閱讀后，我相信您查詢的這一部分完全沒有完成。我不確定嘗試匹配的預期目的是什么，dol.EndTime但這不是正確的方法。

LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId and (hd.Id, dol.EndTime) IN (
   select max(hd.Id) as Id, max(dol.EndTime) as EndTime
   from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id
)

我建議您應該更改為以下內容或檢查目標是什么并更新您的問題：

LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId

標籤：sql-server 查询语句

上一篇：遍歷行并洗掉具有特定值的行

下一篇：如何在日期表中訂購過去十二個月？