Множественный выбор против одного CTE

У меня есть запрос CTE, который фильтрует таблицу Student

  Student

   (
    StudentId PK,
    FirstName ,
    LastName,
    GenderId,
    ExperienceId,
    NationalityId,
    CityId
  )

На основе много фильтров (несколько городов, пол, несколько опытов (1, 2, 3), несколько националистов), я создаю CTE, используя динамический sql и соединяющий таблицу ученика с пользовательскими таблицами (CityTable, NationalityTable,...)

После этого мне нужно получить счетчик по каждому фильтру, например

CityId City Count

NationalityId Nationality Count

То же самое относится к другому фильтру.

Могу ли я сделать что-то вроде

  ;With CTE(
         Select
         FROM Student
         Inner JOIN ...
         INNER JOIN ....)
  SELECT CityId,City,Count(studentId)
  FROm CTE
  GROUP BY CityId,City

  SELECT GenderId,Gender,Count
  FROM CTE
  GROUP BY  GenderId,Gender

Я хочу что-то вроде того, что LinkedIn делает с поиском (поиск людей, поиск работы)

http://www.linkedin.com/search/fpsearch?type=people&keywords=sales+manager&pplSearchOrigin=GLHD&pageKey=member-home

Это так быстро и делает то же самое.

Ответ 1

Вы не можете использовать множественный выбор, но вы можете использовать более одного CTE, как это.

WITH CTEA
AS
(
SELECT 'Coulmn1' A,'Coulmn2' B
),
CETB
AS
(
SELECT 'CoulmnX' X,'CoulmnY' Y
)

SELECT * FROM CTEA, CETB

Для получения подсчета используйте RowNumber и CTE, некоторые думают так.

ROW_NUMBER() OVER ( ORDER BY COLUMN NAME )AS RowNumber,
Count(1) OVER() AS TotalRecordsFound

Пожалуйста, дайте мне знать, если вам нужна дополнительная информация об этом.

Пример для справки.

With CTE AS (
         Select StudentId, S.CityId, S.GenderId
         FROM Student S
         Inner JOIN CITY C
         ON S.CityId = C.CityId
         INNER JOIN GENDER G
         ON S.GenderId = G.GenderId)
,
GENDER
AS
(
  SELECT GenderId
  FROM CTE
  GROUP BY  GenderId
  )


SELECT * FROM GENDER, CTE

Ответ 2

Невозможно получить несколько наборов результатов из одного CTE. Тем не менее, вы можете использовать переменную таблицы для кэширования некоторой информации и использовать ее позже, вместо того, чтобы вызывать один и тот же сложный запрос несколько раз:

declare @relevantStudent table (StudentID int);

insert into @relevantStudent
select s.StudentID from Students s
join ...
where ...

-- now issue the multiple queries

select s.GenderID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.GenderID

select s.CityID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.CityID

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

Ответ 3

Сделайте UNION ALL для выполнения нескольких SELECT и объедините результаты вместе в одну таблицу.

  ;WITH CTE AS(
         SELECT
         FROM Student
         INNER JOIN ...
         INNER JOIN ....)
  SELECT CityId,City,Count(studentId),NULL,NULL
         FROM CTE
         GROUP BY CityId,City
  UNION ALL
  SELECT NULL,NULL,NULL,GenderId,Gender,Count
         FROM CTE
         GROUP BY GenderId,Gender

Примечание. Значения NULL выше позволяют только двум результатам иметь соответствующие столбцы, поэтому результаты могут быть объединены.