时间戳

首页 > 解决方案 > 将单个 SQL Server 查询合并为 1 个查询

sql - 将单个 SQL Server 查询合并为 1 个查询

问题描述

我尝试将三个 SQL Server 查询合并为一个查询。我是这个级别的 SQL 的新手，但这是我的三个查询

SELECT detail.map_id
, parcel.dsc_cd
, CASE WHEN ISNULL(parcel.dsc_cd, '') <> ''
       THEN (select dscr from lu_dsc where cd = parcel.dsc_cd)
       ELSE ''
  END AS Name
, parcel.nh_cd
, parcel.strap
, parcel.dor_cd
, detail.sub
, detail.section
, detail.township
, detail.range
, detail.acreage
, detail.sqft
FROM parcel
     INNER JOIN detail ON parcel.strap = detail.strap
WHERE parcel.status_cd = 'A'
AND (detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
    OR parcel.nh_cd IN (NULL,'0.00')
    OR parcel.dor_cd IN (NULL,'')
    OR detail.sub IN (NULL,'')
    OR detail.section IN (NULL,'')
    OR detail.township IN (NULL,'')
    OR detail.range IN (NULL,'')
    OR detail.acreage < 0
    OR detail.sqft < 0)
AND parcel.dor_cd NOT IN ('POSS', 'ALN')

SELECT detail.map_id MAP_ID
, parcel.nh_cd NBHD
, parcel.dor_cd DOR_CD
, detail.strap ACCOUNTNO
, detail.mkt_ar_2 LAND_MRKT
, detail.mkt_ar_1 ECON_AREA
FROM detail
     INNER JOIN parcel ON detail.strap = parcel.strap
WHERE parcel.status_cd = 'A'
      AND detail.mkt_ar_1 IN (NULL,'0')

SELECT TOP 100000 detail.map_id, parcel.strap, parcel.dor_cd, parcel.status_cd
FROM parcel INNER JOIN detail ON parcel.strap = detail.strap
WHERE (parcel.status_cd = 'A ')
AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)

这就是我到目前为止所拥有的。它运行没有任何错误，但它没有提取更正信息。我知道查找 detail.mkt_ar_1 的查询应该有一些结果。

SELECT detail.map_id
, CASE WHEN ISNULL(parcel.dsc_cd, '') <> ''
       THEN (select dscr from lu_dsc where cd = parcel.dsc_cd)
       ELSE ''
  END AS Name
, parcel.nh_cd
, parcel.strap
, parcel.dor_cd
, detail.mkt_ar_1
, detail.sub
, detail.section
, detail.township
, detail.range
, detail.acreage
, detail.sqft
FROM parcel
     INNER JOIN detail ON parcel.strap = detail.strap
WHERE parcel.status_cd = 'A'
AND ((detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
    OR parcel.nh_cd IN (NULL,'0.00')
    OR parcel.dor_cd IN (NULL,'')
    OR detail.sub IN (NULL,'')
    OR detail.section IN (NULL,'')
    OR detail.township IN (NULL,'')
    OR detail.range IN (NULL,'')
    OR detail.acreage < 0
    OR detail.sqft < 0
    OR detail.mkt_ar_1 IN (NULL,'0'))
AND parcel.dor_cd NOT IN ('POSS', 'ALN'))
AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)

我如何以这样的方式编写它以运行每个查询？任何有关格式的建议也将不胜感激。

标签： sqlsql-server

解决方案

结合WHERE条款...

WHERE
  (
        parcel.status_cd = 'A'
    AND parcel.dor_cd NOT IN ('POSS', 'ALN')
    AND
    (
          detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
      OR  parcel.nh_cd IN (NULL,'0.00')
      OR  parcel.dor_cd IN (NULL,'')
      OR  detail.sub IN (NULL,'')
      OR  detail.section IN (NULL,'')
      OR  detail.township IN (NULL,'')
      OR  detail.range IN (NULL,'')
      OR  detail.acreage < 0
      OR  detail.sqft < 0
    )
  )

OR

  (
        parcel.status_cd = 'A'
    AND detail.mkt_ar_1 IN (NULL,'0')
  )

OR

  (
        parcel.status_cd = 'A '
    AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)
  )

也就是说，我不认为你会得到你认为的NULLs 行为，例如......

'x' NOT IN (NULL, 'y', 'z')  => Does NOT return TRUE, because of the NULL
   NULL IN (NULL, '')        => Does NOT return TRUE, because NULL never equals anything, even another NULL

推荐阅读