Справка по SQL-запросу

Question

Я работаю над запросом отчета, который должен отображать заказы, содержащие как элементы p-типа, так и другие не-p-элементы (не P-элементы состоят из 50 элементов), им нужны детали заказа, объединяющие оба типа элементов

Ниже приведен запрос, который я подготовил, но в этом запросе также отображаются заказы типа ptype, которые не объединяются с элементами, отличными от p.

SELECT
  vwOrD.ONUMBER,
  vwOrD.ITEMID,
  vwITEMs.cat,
  vwITEMs.id
FROM vwITEMs
INNER JOIN vwOrD
  ON vwITEMs.ITEMID = vwOrD.ITEMID
INNER JOIN vwOrders
  ON vwOrD.ONUMBER = vwOrders.ONUMBER
WHERE vwOrders.CUSTID        = 'test'
  AND vwOrders.CREATEDATE >= '1-1-2016'
  AND vwOrders.CREATEDATE <= '11-28-2016'
  AND vwOrD.ONUMBER       IN
  (SELECT vwOrD.ONUMBER
   FROM vwOrD
   INNER JOIN vworders
    ON vwOrD.ONUMBER = vwOrders.ONUMBER
   INNER JOIN vwITEMs
    ON vwITEMs.ASCITEMID = vwOrD.ASCITEMID
    WHERE vwOrders.SOLDTOCUSTID  = 'test'
      AND vwITEMs.cat          = N'PI' -- Pitems cat= pi, id = c
      AND vwITEMs.id           = 'C'
      AND vwOrders.CREATEDATE >= '1-1-2016'
      AND vwOrders.CREATEDATE <= '11-28-2016' --group by          vwOrD.ONUMBER
      -- having count(1) > 1
  )
ORDER BY
  vwOrD.ONUMBER

Образец сгенерированного вывода:

  ornumber  idnum  categ id  id
        12    xxx        pi   c 
        12    xxx     nonpi   c
        11    yyy        pi   c
        10    qqq        pi   c

ожидаемые результаты

12 xxx    pi c
12 xxx nonpi c

Answer 1

Я не уверен, почему столбцы в вашем подзапросе отличаются, но я не думаю, что это корень вашей проблемы.

Вы используете свой подзапрос, чтобы убедиться, что для каждой строки, которую вы возвращаете, есть элемент в этом порядке с 'pi' вещь. Это немного отличается от того, что вы сказали, что пытались сделать.

Запрос ниже возвращает строки, которые 'pi' или же 'nonpi' что есть еще один ряд для этого onumber это также 'pi' или же 'nonpi' но это не то же самое cat как строка это проверяет против.

select 
    d.onumber 
  , d.itemid
  , i.cat
  , i.id 
  from vwitems as i
    inner join vwOrD    as d on i.itemid = d.itemid 
    inner join vwOrders as o on d.onumber = o.onumber 
  where o.custid = 'test' 
    and o.createdate >= '1-1-2016' 
    and o.createdate <= '11-28-2016' 
    and exists (
      select 1 
        from vwOrD
          inner join vwitems on vwitems.ascitemid = vwOrD.ascitemid  /* ascitemid vs itemid ? */
          and vwitems.cat = 'Pi'
          and vwitems.id = 'C' 
          and vwOrD.onumber=o.onumber
          )
    and exists (
      select 1 
        from vwOrD
          inner join vwitems on vwitems.ascitemid = vwOrD.ascitemid  /* ascitemid vs itemid ? */
          and vwitems.cat != 'Pi'
          and vwitems.id = 'C' 
          and vwOrD.onumber=o.onumber
          )
  order by d.onumber;        

Answer 2

select 
   d.onumber 
  ,d.itemid
  ,i.cat
  ,i.id 
from
    vwitems as i
    inner join vwOrD d
    on i.itemid = d.itemid 
    inner join vwOrders o
    on d.onumber = o.onumber 
    AND o.custid = 'test' 
    and o.createdate >= '1-1-2016' 
    and o.createdate <= '11-28-2016' 
WHERE
    EXISTS (SELECT
             1
          FROM
             vmItems i2
             INNER JOIN vwOrd d2
             ON i2.itemid = d2.itemid
          WHERE
             o.onumber = d2.onumber
          HAVING
             COUNT(DISTINCT i.Cat) > 1
             AND COUNT(CASE WHEN i.Cat = 'Pi' THEN 1 END) > 0)

Это должно быть лучше, чем 2 отдельных оператора EXISTS.

Также у вас есть все таблицы, к которым вы уже присоединились, чтобы вы могли использовать оконные функции с условным агрегированием и делать это тоже. Вот версия Common Table Express [CTE], но она также может быть помещена как производная таблица. Это может работать еще лучше:

;WITH cte AS (
    select 
      d.onumber 
     ,d.itemid
     ,i.cat
     ,i.id 
     ,COUNT(CASE WHEN i.Cat = 'Pi' THEN 1 ELSE 0 END) OVER (PARTITION BY o.number) as PiCount
     ,COUNT(CASE WHEN i.Cat <> 'Pi' THEN 1 ELSE 0 END) OVER (PARTITION BY o.number) as NonPiCount
    from
       vwitems as i
       inner join vwOrD d
       on i.itemid = d.itemid 
       inner join vwOrders o
       on d.onumber = o.onumber 
       AND o.custid = 'test' 
       and o.createdate >= '1-1-2016' 
       and o.createdate <= '11-28-2016' 
)

SELECT *
FROM
    cte
WHERE
    PiCount > 0
    AND NonPiCount > 0

Я частично хотел показать вам эти ответы из-за любимой мозоли. Вы должны поместить ограничения, которые находятся на соединениях, в условие ON, а не WHERE. Когда вы делаете inner join Вы не заметите ничего другого, но как только вы используете OUTER JOIN если вы соблюдаете условия в WHERE пункт это станет inner join, Плюс, ограничивая в ON условие SQL может уменьшить набор записей, с которыми он имеет дело, прежде чем присоединяться, что может привести к лучшей оптимизации.