TSQL procedure which would perform multiple selects and join them into one result
I need to write a TSQL procedure which would perform 4 selects and join results int 开发者_Go百科one dataset. Can this be done?
P.S.
Selects are performed in a loop
EDIT
Here is the schema. I wan't to get N latest points from each Unit which belongs to SOME map.
Maps
Id
Maps2Units
MapId
UnitId
Units
Id
Points
Id
UnitId
Timestamp
Edit: another solution using top
SELECT
*
FROM
Maps2Units MU
CROSS APPLY
(
SELECT TOP (@n) --or TOP 3 etc
*
FROM
Points P
WHERE
P.UnitId = MU.UnitId
) P2
Guessing from your previous comment...
;WITH myCTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY P.UnitId ORDER BY P.Timestamp DESC) AS PointsRank
FROM
Points P
JOIN
Maps2Units MU ON P.UnitId = MU.UnitId
)
SELECT
*
FROM
myCTE
WHERE
PointsRank <= @M;
Original:
;WITH myCTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY SpeciesID ORDER BY AnimalID /* ? */) AS AnimalRank,
DENSE_RANK() OVER (ORDER BY SpeciesID /* ? */) AS SpeciesRank
FROM
MyAnimals
)
SELECT
*
FROM
myCTE
WHERE
SpeciesRank <= @M
AND
AnimalRank <= @N
rather than select in loops, I'd first try to refactor it into a single select to pull back all the information.
Outside of that, dump all the select information into a temporary table (or a table variable, depending on performance impact) and then select from that table to return all the information is one statement.
Use temporary table
select col1 from tableA
union
select col2 from tableB
union
select col3 from tableC
union
select col4 from tableD
The columns selected must match datatype
Declare a temp (#) table or a table variable (@) and select into it. then just select * from your temp when you are done.
If all 4 datasets share common values to link them into rows, use INNER JOIN.
If all 4 datasets have the same column definitions, but just retrieve different data, use UNION
EDIT - based on your updated question, sounds like you need something like the following: http://allenbrowne.com/subquery-01.html#TopN to get top N per group
From the information you provided:
SELECT Id, NULL AS MapID, NULL AS UnitID, NULL AS Timestamp FROM Maps
union all
SELECT NULL AS ID, MapID, UnitID, NULL AS Timestamp FROM Maps2Units
union all
SELECT ID, NULL, NULL, NULL FROM Units
union all
SELECT id, null AS MapID, UnitID, null AS Timestamp FROM Points
精彩评论