问题描述
我正在尝试为具有如下架构的矩阵表构建查询:
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 |