我试图建立具有这样的模式的矩阵表的查询：
X | Y | Z | Disabled | OccupiedId |
--------------------------------------------
1 1 1 0 NULL
1 2 1 0 NULL
1 3 1 1 NULL
1 4 1 0 1
1 5 1 0 2
1 6 1 0 3
1 7 1 0 4
1 1 2 0 NULL
1 2 2 0 NULL
1 3 2 0 NULL
1 4 2 0 NULL
1 5 2 0 NULL
1 6 2 0 NULL
1 7 2 0 NULL
我想为X，Z组和找到Y.第一个可用的位置通过所有手段并不失效，而没有被占用。
在提供此查询应返回例如：
X | Z | FreeY
--------------------------------------------
1 1 2
1 2 7
查询应选择第一个自由Y（或最后占用的Y）考虑到每个（X，Z）充满从年底开始（MAX Y为常数）
我已经尝试不同的方法不成功:(任何建议高度赞赏！亲切的问候，D.
为了您的编辑（禁用=位列），这个查询显示lastOccupiedID以及firstFreeY
select x, z,
max(case when disabled=1 or occupiedid is not null
then Y else 0 end) lastOccupiedPosition,
maX(case when disabled=0 AND occupiedid is null
then Y else 0 end) firstFreeY
from matrix
group by x, z
order by x, z;
SQL小提琴
MS SQL Server 2008的架构设置 ：
create table matrix(
X int , Y int , Z int , Disabled varchar(5) , OccupiedId int );
insert matrix values
(1 , 1 , 1 , 'True' , NULL ),
(1 , 1 , 2 , 'False' , NULL ),
(1 , 1 , 3 , 'False' , NULL ),
(1 , 1 , 4 , 'False' , NULL ),
(1 , 2 , 1 , 'False' , NULL ),
(1 , 2 , 2 , 'False' , NULL ),
(1 , 2 , 3 , 'False' , 123 ),
(1 , 2 , 4 , 'False' , NULL );
问题1：
select x, z,
max(case when disabled='true' or occupiedid is not null
then Y else 0 end) lastOccupiedPosition
from matrix
group by x, z
order by x, z
结果 ：
| X | Z | LASTOCCUPIEDPOSITION |
--------------------------------
| 1 | 1 | 1 |
| 1 | 2 | 0 |
| 1 | 3 | 2 |
| 1 | 4 | 0 |
SQL小提琴
CREATE TABLE Coordinate
( X int, Y int,Z int, Disabled bit, OccupiedId int)
INSERT INTO Coordinate VALUES (1,1,1, 1, NULL)
INSERT INTO Coordinate VALUES (1,1,2, 0, NULL)
INSERT INTO Coordinate VALUES (1,1,3, 0, NULL)
INSERT INTO Coordinate VALUES (1,1,4, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,1, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,2, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,3, 0, 123)
INSERT INTO Coordinate VALUES (1,2,4, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,5, 1, NULL)
SELECT X, Z, MIN(Y) AS FirstFreePosition
FROM Coordinate
WHERE Disabled = 0 AND OccupiedId IS NULL
GROUP BY X, Z
OR -- if you need the unavailable combinations too, then something like this:
SELECT X, Z, MIN(CASE
WHEN Disabled = 1 OR OccupiedId IS NOT NULL
THEN 1000 --a big number
ELSE Y END) AS FirstFreePosition
FROM Coordinate
GROUP BY X, Z
