T-SQL查询一个矩阵表的自由位置

对于你的编辑（disabled=bit列），这个查询显示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 |
--------------------------------