我正在尝试为具有如下架构的矩阵表构建查询:
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 |
这篇关于T-SQL查询矩阵表的自由位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!