Блог

Обычно я могу определить любые sql-запросы, которые мне нужны для моих приложений, но недавно я был поставлен в тупик запросом с перекрестными вкладками, который мне нужно создать, и мне было интересно, можете ли вы помочь?

У меня есть 3 таблицы

 Category(catID, catTitle) 
Equipment(equipID, make, model, quantity, catID, siteID)
Site(siteID, title)
 

И я хотел бы создать запрос с перекрестными вкладками для отображения результирующего набора, как показано ниже

 Category   Site1   Site2   Site3   Site4   Site5
PC           2       0       10      3      6
Camera       12      4       2       0      8
Printer      3       2       1       1      2
 

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

Вы должны быть в состоянии сделать это с помощью оператора ‘pivot’. Что-то вроде этого (хотя я уверен, что я заглушил некоторые детали правописания или синтаксиса …):

 select catTitle, [1] as site1, [2] as site2, [3] as site3, [4] as site4, [5] as site5
  from (select category.catTitle, equipment.quantity, site.title
          from equipment
            inner join site
              on (equipment.siteid = site.siteid)
            inner join category
              on (category.catid = equipment.catid)
        ) 
  pivot
  (
  sum (quantity)
    for equipment.siteid in ( [1], [2], [3], [4], [5] )
  ) as pvt
order by pvt.category;
 

Проблема заключается в том, что вам нужно знать точный набор идентификаторов сайтов, которые вы хотите включить в запрос. Если вам нужна более динамичная перекрестная таблица (например, вы можете получить в Excel), вам нужно сгенерировать текст запроса в виде строки и использовать sp_executesql для его запуска. В сгенерированный текст необходимо включить как можно больше «[1], [2], [3], [4], [5]…» и «[1] как site1, [2] как site2 …» все, что вам нужно.

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

Что-то вроде этого :

 EquipmentSite(SiteID, EquipID)
 

Потому что теперь невозможно определить, какой сайт является оборудованием

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

Поскольку SiteID также присутствует в оборудовании, я бы предложил другую таблицу, небольшой рефакторинг вашей базы данных (потому что я действительно не знаю, как это сделать)

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

Итак, я предлагаю эту таблицу

 siteCatCount(CatID, siteID, cnt)
 

Поэтому, когда вы изменяете свои данные (добавляете или удаляете оборудование), вы должны обновить эту таблицу, это будет намного понятнее, и вам не придется каждый раз вычислять количество каждого оборудования