SQL запрашивает несколько строк в одну, если выполнены критерии

ProgramBox

SQL запрашивает несколько строк в одну, если выполнены критерии

Post author:admin
Запись опубликована:9 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server-2008

Вопрос:

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

Для упрощения таблиц и объема данных должно быть достаточно следующих двух таблиц:

Таблица 1

 PRVDR_NUM | RPT_REC_NUM  
013          1234
014          4567

Таблица 2

 RPT_REC_NUM |  WKSHT_CD | LINE_NUM | ITM_VAL_NUM
1234             f1         3           30
1234             f1         9           3
1234             e2       100           100
4567             f1         3           20
4567             f1         9           8
4567             e2       100           100

Первая часть состоит в том, чтобы получить все записи, где значение ITM_NUM_CAL находится в диапазоне от 25 до 50 для WKSHT_CD f1 и LINE_NUM 3, что значение ITM_NUM_VAL равно доходу.

 SELECT      r.PRVDR_NUM as Provider, 
            r.RPT_REC_NUM as 'Report Record', 
            n.ITM_VAL_NUM as Revenue

FROM        Table 1 r
        left outer join Table 2 n on 
        r.RPT_REC_NUM = n.RPT_REC_NUM

WHERE (n.WKSHT_CD = 'f1') and (n.LINE_NUM = '3')
        and (n.ITM_VAL_NUM > 25) and (n.ITM_VAL_NUM < 50)
        and left(r.PRVDR_NUM, 2) in ('01','04','11','18','25','26','34','44','49')

Итак, наши результаты:

 Provider |  Report Record | Revenue 
 013           1234         30

Но мы также хотим иметь возможность извлекать соответствующие ITM_VAL_NUM для WKSHT_CD f1 и LINE_NUM 9, которые мы будем называть cost.

Таким образом, результаты должны быть:

 Provider |  Report Record | Revenue | Cost
 013           1234          30         3

Заранее большое спасибо.

Редактировать

Я полагаю, что это последний запрос, который я искал после некоторых маневров и добавления новой переменной CLMN_NUM, которая является еще одним столбцом в table2.

 SELECT r.PRVDR_NUM as Provider, 
       r.RPT_REC_NUM as 'Report Record', 
       n.ITM_VAL_NUM as Revenue,
       n2.ITM_VAL_NUM as 'Cost',
       n3.ITM_VAL_NUM as 'Visits'

FROM table1 r
    LEFT OUTER JOIN table2 n on r.RPT_REC_NUM = n.RPT_REC_NUM
    LEFT OUTER JOIN table2 n2 on n.WKSHT_CD = n2.WKSHT_CD
    and n.RPT_REC_NUM = n2.RPT_REC_NUM and n2.LINE_NUM = 9
    LEFT OUTER JOIN table2 n3 on r.RPT_REC_NUM = n3.RPT_REC_NUM 
    and n3.WKSHT_CD = 'e2' and n3.LINE_NUM = 100 and n3.CLMN_NUM = xxxx  

WHERE (n.WKSHT_CD = 'f1') 
AND (n.LINE_NUM = '3')
AND (n.ITM_VAL_NUM > 25) 
AND (n.ITM_VAL_NUM < 50)
AND left(r.PRVDR_NUM, 2) in ('01','04','11','18','25','26','34','44','49')

Ответ №1:

Пара способов:

Подзапрос

 SELECT r.PRVDR_NUM as Provider, 
       r.RPT_REC_NUM as 'Report Record', 
       n.ITM_VAL_NUM as Revenue
       (SELECT ITM_VAL_NUM 
        FROM Table2 
        WHERE WKSHT_CD = n.WKSHT_CD 
        AND LINE_NUM = 9) AS Cost
FROM Table1 r
    LEFT OUTER JOIN Table2 n on r.RPT_REC_NUM = n.RPT_REC_NUM
WHERE (n.WKSHT_CD = 'f1') 
AND (n.LINE_NUM = '3')
AND (n.ITM_VAL_NUM > 25) 
AND (n.ITM_VAL_NUM < 50)
AND left(r.PRVDR_NUM, 2) in ('01','04','11','18','25','26','34','44','49')

Другое ОБЪЕДИНЕНИЕ

 SELECT r.PRVDR_NUM as Provider, 
       r.RPT_REC_NUM as 'Report Record', 
       n.ITM_VAL_NUM as Revenue
       n2.Cost
FROM Table1 r
    LEFT OUTER JOIN Table2 n on r.RPT_REC_NUM = n.RPT_REC_NUM
    LEFT OUTER JOIN Table2 n2 on n.WKSHT_CD = n2.WKSHT_CD
        AND LINE_NUM = 9
WHERE (n.WKSHT_CD = 'f1') 
AND (n.LINE_NUM = '3')
AND (n.ITM_VAL_NUM > 25) 
AND (n.ITM_VAL_NUM < 50)
AND left(r.PRVDR_NUM, 2) in ('01','04','11','18','25','26','34','44','49')

1. Спасибо за быстрые ответы, когда я пробую ваше решение для подзапросов, я получаю сообщение для более чем 1 значения. Когда я пробую ваше решение JOIN после некоторой модификации, я заставляю его работать, но в итоге получается слишком много строк, как будто это потянуло на себя все затраты.

2. В обоих случаях это было бы из-за данных, и я предполагаю, что вы предоставляете только выборку. Вам нужно было бы добавить условия, гарантирующие возврат только 1 записи.

3. Спасибо за вашу помощь, Дастин, я отредактировал свой пост с некоторыми изменениями и дополнениями, будет ли это нормально?

4. да, хотя я бы подумал, что некоторые из этих данных можно нормализовать в лучшую структуру, чтобы помочь организовать эти данные более эффективно.

Ответ №2:

Вы можете, выполнив CASE / WHEN для ваших рассматриваемых столбцов, но разрешив ОБА значения LINE_NUM в вашем where тоже… Использование ОБЪЕДИНЕНИЯ предотвратит нулевые значения в случае отсутствия записей для одной стороны, другой или обеих для доходов / затрат соответственно. Вы могли бы просто использовать то же самое для любых других значений «LINE_NUM», которые вы хотели бы включить.

 SELECT
      r.PRVDR_NUM as Provider,
      r.RPT_REC_NUM as 'Report Record',
      COALESCE( CASE WHEN n.LINE_NUM = 3 THEN n.ITM_VAL_NUM ELSE 0 END, 0 ) as Revenue,
      COALESCE( CASE WHEN n.LINE_NUM = 9 THEN n.ITM_VAL_NUM ELSE 0 END, 0 ) as Costs
   from
      Table_1 r
         left join Table_2 n
            on r.RPT_REC_NUM = n.RPT_REC_NUM
            AND n.WKSHT_CD = 'f1'
            AND n.LINE_NUM in ( '3', '9')
            AND n.ITM_VAL_NUM between 25 and 50
   where
      left(r.PRVDR_NUM, 2) in ('01','04','11','18','25','26','34','44','49')

Ответ №3:

 SELECT
  Provider        = r.PRVDR_NUM,
  [Report Record] = r.RPT_REC_NUM,
  Revenue         = MIN(CASE n.LINE_NUM WHEN '3' THEN n.ITM_VAL_NUM),
  Cost            = MIN(CASE n.LINE_NUM WHEN '9' THEN n.ITM_VAL_NUM)
FROM Table1 r
  INNER JOIN Table2 n ON r.RPT_REC_NUM = n.RPT_REC_NUM
WHERE n.WKSHT_CD = 'f1' AND n.LINE_NUM IN ('3', '9')
  AND n.ITM_VAL_NUM > 25 AND n.ITM_VAL_NUM < 50
  AND LEFT(r.PRVDR_NUM, 2) IN ('01','04','11','18','25','26','34','44','49')
GROUP BY
  r.PRVDR_NUM,
  r.RPT_REC_NUM