Вектор Vector.down не представляет вектор объекта вниз все время. Вы можете использовать -transform.up
.
Кроме того, если это приемлемо, вы можете добавить некоторое дополнение к начальной точке луча, например rayOrigin.y += 0.0001f;
.
Вы можете пройти иерархию с рекурсивным CTE
. Это классический верхний вниз CTE:
WITH EmployeesHierarchy AS
(
SELECT ID,[Level],Employee,Department,Code,MasterId
FROM T_Employees
WHERE [Level]=1
UNION ALL
SELECT nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
FROM EmployeesHierarchy AS recCall
INNER JOIN T_Employees AS nextLevel ON nextLevel.[Level]=recCall.[Level]+1 AND nextLevel.MasterId=recCall.ID
)
SELECT * FROM EmployeesHierarchy
ORDER BY [Level],MasterId
GO
И теперь наоборот: я начинаю с упомянутых сотрудников в проекте и переместить список до тех пор, пока не будет родительского родителя больше. Данные проекта, полученные в первой части CTE, просто пройдены для отображения во всех строках.
WITH EmployeesHierarchy AS
(
SELECT p.ID AS p_ID,p.ProjectId,e.ID AS e_ID,[Level],e.Employee,e.Department,e.Code,e.MasterId
FROM T_Projects AS p
INNER JOIN T_Employees AS e ON p.EmployeeId=e.ID
UNION ALL
SELECT recCall.p_ID,recCall.ProjectId,nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
FROM EmployeesHierarchy AS recCall
INNER JOIN T_Employees AS nextLevel ON nextLevel.ID=recCall.MasterId
)
SELECT * FROM EmployeesHierarchy
--WHERE ProjectId=456
ORDER BY [Level]
Результат
+------+-----------+------+-------+------------+------------+------+----------+
| p_ID | ProjectId | e_ID | Level | Employee | Department | Code | MasterId |
+------+-----------+------+-------+------------+------------+------+----------+
| 3 | 23 | 1 | 1 | Thomas S. | A | 1-4 | NULL |
+------+-----------+------+-------+------------+------------+------+----------+
| 2 | 456 | 3 | 1 | Simone S. | A | 1-3 | NULL |
+------+-----------+------+-------+------------+------------+------+----------+
| 1 | 456 | 1 | 1 | Thomas S. | A | 1-4 | NULL |
+------+-----------+------+-------+------------+------------+------+----------+
| 2 | 456 | 6 | 2 | Loris P. | B | 2-15 | 3 |
+------+-----------+------+-------+------------+------------+------+----------+
| 1 | 456 | 4 | 2 | Stefan K. | B | 2-18 | 1 |
+------+-----------+------+-------+------------+------------+------+----------+
| 3 | 23 | 4 | 2 | Stefan K. | B | 2-18 | 1 |
+------+-----------+------+-------+------------+------------+------+----------+
| 3 | 23 | 7 | 3 | Lennon I. | B | 2-19 | 4 |
+------+-----------+------+-------+------------+------------+------+----------+
| 1 | 456 | 8 | 3 | Kerim K. | C | 2-66 | 4 |
+------+-----------+------+-------+------------+------------+------+----------+
| 2 | 456 | 9 | 3 | Ilmas Y. | C | 2-59 | 6 |
+------+-----------+------+-------+------------+------------+------+----------+
| 1 | 456 | 10 | 4 | Innes Y. | D | 3-89 | 8 |
+------+-----------+------+-------+------------+------------+------+----------+
| 2 | 456 | 12 | 4 | Fatih O. | I | 3-32 | 9 |
+------+-----------+------+-------+------------+------------+------+----------+
| 3 | 23 | 11 | 4 | Andreas U. | E | 3-63 | 7 |
+------+-----------+------+-------+------------+------------+------+----------+
Наборы данных результатов могут запускать этот запрос:
declare @project int ;
declare cur_project cursor for
select distinct ProjectId from T_Projects;
open cur_project
fetch next from cur_project into @project
while @@FETCH_STATUS = 0
begin
select E.* from [dbo].[T_Projects] P
inner join [dbo].[T_Employees] E
on P.EmployeeId = E.ID
where P.ProjectId = @project
fetch next from cur_project into @project
end
close cur_project;
deallocate cur_project;