問題描述

我正在嘗試為具有如下架構的矩陣表構建查詢:

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模板網！