sql-server - recursive - sql with sql server

Selección múltiple contra un CTE

Question

Tengo una consulta CTE filtrando una tabla Estudiante

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

Basándome en muchos filtros (varias ciudades, género, experiencias múltiples (1, 2, 3), varias nacionalidades), creo un CTE utilizando sql dinámico y uniéndome a la tabla de estudiantes con tablas definidas por el usuario ( CityTable, NationalityTable,... )

Después de eso tengo que recuperar el conteo de alumnos por cada filtro como

CityId City Count
NationalityId Nationality Count

Lo mismo el otro filtro.

Puedo hacer algo como

 ;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

Quiero algo como lo que hace LinkedIn con la búsqueda (búsqueda de personas, búsqueda de empleo)

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

Es tan rápido y hace lo mismo.

Answer 1

Haga un UNION ALL para hacer un SELECT múltiple y concatene los resultados en una tabla.

 ;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

Nota: Los valores NULL anteriores solo permiten que los dos resultados tengan columnas coincidentes, por lo que los resultados se pueden concatenar.

Answer 2

No es posible obtener varios conjuntos de resultados de un solo CTE. Sin embargo, puede usar una variable de tabla para almacenar parte de la información en caché y usarla más tarde en lugar de emitir la misma consulta compleja varias veces:

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

El truco es almacenar solo la información mínima requerida en la variable de tabla.
Al igual que con cualquier consulta, si esto realmente mejorará el rendimiento en comparación con emitir las consultas de manera independiente depende de muchas cosas (qué tan grande es el conjunto de datos de la variable de la tabla, qué tan compleja es la consulta que se utiliza para rellenarla y cuán complejas son las subsiguientes combinaciones / subselecciones contra tabla variable, etc.).

Answer 3

No puede usar la selección múltiple, pero puede usar más de un CTE como este.

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

Para obtener el recuento, utilice RowNumber y CTE, algunos piensan así.

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

Por favor, hágamelo saber si necesita más información sobre esto.

Muestra para su referencia.

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

Answer 4

Sé que esta es una pregunta muy antigua, pero aquí hay una solución que acabo de usar. Tengo un procedimiento almacenado que devuelve una PÁGINA de resultados de búsqueda, y también lo necesito para devolver el recuento total que coincida con los parámetros de consulta.

WITH results AS (...complicated foo here...)
SELECT results.*,
CASE
WHEN @page=0 THEN (SELECT COUNT(*) FROM results)
ELSE -1
END AS totalCount
FROM results
ORDER BY bar
OFFSET @page * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;

Con este enfoque, hay un pequeño "hit" en la primera página de resultados para obtener el recuento, y para las páginas restantes, devuelvo "-1" para evitar el hit (supongo que el número de resultados no cambiará durante el sesión de usuario). A pesar de que se devuelve totalCount para cada fila de la primera página de resultados, solo se calcula una vez.

Mi CTE está haciendo un montón de filtrado basado en argumentos de procedimientos almacenados, por lo que no podría moverlo a una vista y consultarlo dos veces. Este enfoque permite evitar tener que duplicar la lógica del CTE solo para obtener un conteo.