T-SQL 益智游戏 - 给定 N 维空间中的一个单元格，返回沿每个轴与给定单元格内联的单元格

Question

N

维空间中的单元格使用以下2个表进行建模。需要一个脚本来获取单个单元格（通过 CellID）并返回沿每个轴（包括其自身）与给定单元格"内联"的所有其他单元格。

例如，假设空间有 3 个维度 （X， Y， Z），X 有 2 个位置，Y 有 2 个，Z 有 3 个。如果给出坐标为 {1,1,1} 的单元格，则结果应为：

+----------+---------+
| AxisCode |  Cell   |
+----------+---------+
| X        | {1,1,1} |   <- showing coordinates for clarity, but should be CellID
| X        | {2,1,1} |
| Y        | {1,1,1} |
| Y        | {1,2,1} |
| Z        | {1,1,1} |
| Z        | {1,1,2} |
| Z        | {1,1,3} |
+----------+---------+

我花了几个小时在这上面，只提出了针对特定数量的维度硬编码的查询......

请注意：更改 3 个表的架构不是一种选择！该脚本必须适用于 N 个维度，并且不应涉及循环或游标。

兼容性必须是 MS SQL 2008 R2

任何想法都感激不尽！

create table dbo.Cells(
    CellID int not null,
    CellValue int not null,
    constraint PK_Cells primary key (CellID)
    )
create table dbo.AxisPositions(
    AxisCode char(1) not null,      -- X, Y, Z etc
    PositionOnAxis int not null,    -- 1, 2, 3, 4 etc
    constraint PK_AxisPositions primary key (AxisCode, PositionOnAxis)
    )
create table dbo.CellAxes(
    CellID int not null,
    AxisCode char(1) not null,      -- X, Y, Z etc
    PositionOnAxis int not null,    -- 1, 2, 3, 4 etc
    constraint PK_CellAxes primary key (CellID, AxisCode),
    constraint FK_CellAxes_Cells foreign key (CellID) references Cells(CellID),
    constraint FK_CellAxes_AxisPositions foreign key (AxisCode, PositionOnAxis) references AxisPositions(AxisCode, PositionOnAxis)
    )
-- Example data
insert Cells (CellID, CellValue)
values (1, 67), (2, 45), (3, 0), (4, 4), (5, 78), (6, 213), (7, 546), (8, 455), (9, 12), (10, 67), (11, 4), (12, 5)
insert AxisPositions (AxisCode, PositionOnAxis)
values ('X', 1), ('X', 2), ('Y', 1), ('Y', 2), ('Z', 1), ('Z', 2), ('Z', 3)
insert CellAxes (CellID, AxisCode, PositionOnAxis)
values  (1, 'X', 1), (1, 'Y', 1), (1, 'Z', 1),
        (2, 'X', 2), (2, 'Y', 1), (2, 'Z', 1),
        (3, 'X', 1), (3, 'Y', 2), (3, 'Z', 1),
        (4, 'X', 2), (4, 'Y', 2), (4, 'Z', 1),
        (5, 'X', 1), (5, 'Y', 1), (5, 'Z', 2),
        (6, 'X', 2), (6, 'Y', 1), (6, 'Z', 2),
        (7, 'X', 1), (7, 'Y', 2), (7, 'Z', 2),
        (8, 'X', 2), (8, 'Y', 2), (8, 'Z', 2),
        (9, 'X', 1), (9, 'Y', 1), (9, 'Z', 3),
        (10, 'X', 2), (10, 'Y', 1), (10, 'Z', 3),
        (11, 'X', 1), (11, 'Y', 2), (11, 'Z', 3),
        (12, 'X', 2), (12, 'Y', 2), (12, 'Z', 3)

Answer 1

select x.AxisCode, a2.CellID
from CellAxes a1
inner join CellAxes a2 on a2.AxisCode = a1.AxisCode
inner join CellAxes x on x.CellID = a1.CellID
where (a1.AxisCode = x.AxisCode or a1.PositionOnAxis = a2.PositionOnAxis)
and a1.CellID = @CellID -- Cell to match against
group by x.AxisCode, a2.CellID
having count(*) = (select count(distinct AxisCode) from CellAxes where CellID = @CellID)

Answer 2

根据问题中原来的两个表，您可以编写动态 SQL 来创建列，然后可以使用这些列与用于定义"内联"的任何内容进行比较。

-- Build list of column values to pivot
DECLARE @cols NVARCHAR(1000);
SELECT @cols =
STUFF((SELECT N'],[' + axiscode
       FROM (SELECT DISTINCT axiscode FROM CellAxes) AS O(axiscode)
       ORDER BY axiscode
       FOR XML PATH('')
      ), 1, 2, '') + N']';
SELECT @cols;
-- Build dynamic SQL query for pivoting   
DECLARE @sql NVARCHAR(2000);
SET @sql =
N'WITH pivotedData AS (SELECT CellID, ' + @cols +
N'FROM CellAxes ' +
N'PIVOT ' +
N'(MAX(PositionOnAxis) FOR AxisCode IN (' + @cols + N')) AS P)' + 
N'SELECT * from pivotedData'
-- Modify this query with a generated WHERE clause that defines what "inline" means.
;
EXEC(@sql);

请注意，@BillKarwin是正确的 - 没有一种安全的推荐方法可以使用直接 SQL 执行此操作，因为您将架构存储在数据中。

Answer 3

如果我理解您对"内联"的定义，这应该是一种算法来帮助您找到解决方案：

1. 将每个单元格存储为载体。
2. 将所有这些载体与起始单元格的载体进行比较
3. 所有向量仅显示单个差异的单元格都是"内联"的

Answer 4

作为记录 - 我也在SQL Server Central上发布了这个，并获得了一些替代解决方案 - 如果你有兴趣，值得检查。