SQL Server-位列的所有可能组合 [英] SQL Server - all possible combinations of Bit columns

问题描述

我有7个BIT列(A，B，C，D，E，F和G)的空表.我想用代表这些列所有可能组合的行填充该表.理想情况下，将没有行包含完全相同的组合的重复项.尽管我在这里显示了7列，但是列数可能会更多，因此该解决方案应该是可扩展的.

I have an empty table with 7 BIT columns (A, B, C, D, E, F, and G). I am looking to populate this table with rows representing all possible combinations of these columns. Ideally there would be no rows containing duplicates of the exact same combinations. Although I am showing 7 columns here, the number of columns could be greater, so the solution should be scalable.

填充后的表格摘要类似于下图:

A snippet of the table after being populated would look similar to below:

理想情况下，这将基于INSERT语句，但是我也愿意使用T-SQL循环解决方案.我已经尝试过使用CROSS JOINS，但是我有限的知识还不足以使我受益.

Ideally this would be based on an INSERT statement, but I am open to a T-SQL Loop solution as well. I have tried using CROSS JOINS, but my limited knowledge has not gotten me very far.

任何帮助将不胜感激.

推荐答案

使用CTE并对其进行 CROSS JOIN 7次，如下所示:

Use a CTE and CROSS JOIN it with itself 7 times, as in:

with bits as (select 0 as bit union select 1)
select
  row_number() over(
    order by a.bit, b.bit, c.bit, d.bit, e.bit, f.bit, g.bit) as id,
  a.bit, b.bit, c.bit, d.bit, e.bit, f.bit, g.bit
from bits a
cross join bits b
cross join bits c
cross join bits d
cross join bits e
cross join bits f
cross join bits g

这篇关于SQL Server-位列的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！