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

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我正在尝试为具有如下架构的矩阵表构建查询:

I'm trying to build a query for a matrix table which has a schema like this:

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 上的第一个可用位置.无论如何可用是未禁用和未占用.

I want to group for X, Z and find the first available position on Y. Available by all means is NOT Disabled and NOT Occupied.

在提供的示例中，此查询应返回:

In the example provided this query should return:

X   | Z   | FreeY
--------------------------------------------
 1     1     2
 1     2     7

考虑到每个 (X, Z) 从末尾开始填充(MAX Y 是常数)，查询应该选择第一个空闲的 Y(或最后一个占用的 Y)

The query should select the first free Y (or the last occupied Y) considering that each (X, Z) are filled starting from the end (MAX Y is constant)

我尝试了不同的方法但没有成功:(任何建议都非常感谢！亲切的问候，D.

I've tried different approach unsuccessfully :( Any suggestions is highly appreciated! Kind Regards, D.

推荐答案

对于您的编辑(disabled=bit 列)，此查询显示 lastOccupiedID 和 firstFreeY

For your edit (disabled=bit column), this query shows lastOccupiedID as well as 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 |

相关文章