创建表 6 x 6 并从上线自动溢出 [英] Create table 6 x 6 with automatic spill from upline

问题描述

我正在为 MMN 公司创建代码.这个想法是一个具有自动溢出功能的 6 x 6 表的系统.

I am creating a code to company MMN. the idea is a system which has a table 6 x 6 with automatic spill.

例如.

我注册了 6 个新人.

I register 6 new persons.

1. 约翰
2. 彼得
3. 玛丽
4. 拉里
5. 安德森
6. 保罗

当我注册我的第7个系统时，系统会自动按照我下面的顺序放入约翰网络.当我注册第8个时，系统自动按照我下面的命令加入彼得网络.

When I register my 7th the system automatic follow the order below me and put into John network. When I register the 8th the system automatic follow the order below me and put into Peter network.

表 6 x 6第一等级:6第二级:36

Table 6 x 6 Firt level: 6 Second level: 36

我正在尝试使用 sqlserver 中的存储过程创建测试.

I am trying to creating a test with stored procedure in sqlserver.

当我达到表的限制时，我被困在如何自动将新注册的人放在我下面的部分.

I am stuck in the part how I can do automatically put the new person registered to below me when I reach the limit of the table.

推荐答案

创建矩阵会使您的数据非规范化.通常最好的做法是不这样做，因为这会使数据操作变得更加困难，还有其他原因.您将如何防止行数超过 6?你必须像这样添加一个奇怪的约束:

Creating a Matrix would be denormalizing your data. It is usually best practice NOT to do this, as it makes data manipulation a lot more difficult, among other reasons. How would you prevent the rows from being more than 6? You'd have to add a weird constraint like so:

create table #matrix ( ID int identity(1,1),
                        Name1 varchar(64),
                        Name2 varchar(64),
                        Name3 varchar(64),
                        Name4 varchar(64),
                        Name5 varchar(64),
                        Name6 varchar(64),
            CONSTRAINT ID_PK PRIMARY KEY (ID),
            CONSTRAINT Configuration_SixRows CHECK (ID <= 6))

我敢打赌你不会这样做，因此，你不能确保"不超过 6 行插入到你的表中.如果您这样做，那么您必须一次一行地插入数据，这与 SQL Server 的所有内容背道而驰.这将是检查第一列是否已满，然后移动到第二列，然后是第三列，等等......这没有意义.

I'm betting you aren't doing this, and thus, you can't "ensure" no more than 6 rows is inserted into your table. If you are doing this, then you'd have to insert data one row at a time which goes against everything SQL Server is about. This would be to check if the first column is full yet, then move to the second, then the third, etc... it just doesn't make sense.

相反，我会创建一个 ParentID 列，将您的姓名与其各自的网络联系起来，正如您所说的.这可以通过像这样的计算列来完成:

Instead, I would create a ParentID column to relate your names to their respective network as you stated. This can be done with a computed column like so:

declare @table table (ID int identity(1,1), 
                      Names varchar(64), 
                      ParentID as case 
                                     when ID <= 6 then null 
                                     else replace(ID % 6,0,6) 
                                  end)

insert into @table
values
('John')
,('Peter')
,('Mary')
,('Lary')
,('Anderson')
,('Paul')
,('Seven')
,('Eight')
,('Nine')
,('Ten')
,('Eleven')
,('Twelve')
,('Thirteen')
,('Fourteen')


select * from @table

然后，如果您想在矩阵中显示它，您可以使用PIVOT()，特别是动态透视.Stack Overflow 上有很多关于如何执行此操作的示例.这也解释了如果您希望矩阵大于 6 X N ……也许网络会增长，因此每个成员有 50 个人……因此 6(行)X 51(列)

Then, if you wanted to display it in a matrix you would use PIVOT(), specifically Dynamic Pivot. There are a lot of examples on Stack Overflow on how to do this. This also accounts for if you want the matrix to be larger than 6 X N... perhaps the network grows so each member has 50 individuals... thus 6 (rows) X 51 (columns)

如果它只会是 6 列，或者不会更多，那么您也可以使用简单的连接逻辑...

IF it's only going to be 6 columns, or not many more, then you can also use a simple join logic...

select
    t.ID
    ,t.Names
    ,t2.Names
    ,t3.Names
from @table t
left join
    @table t2 on t2.ParentID = t.ID and t2.ID = t.ID + 6
left join
    @table t3 on t3.ParentID = t.ID and t3.ID = t.ID + 12
--continue on
where
    t.ParentID is null

您可以通过这个在线演示

You can see this in action with This OnLine DEMO

这里有一些关于规范化的信息

这篇关于创建表 6 x 6 并从上线自动溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！