有表 ID VALUE
1 A
2 B
3 C
4 D
.
.
.
要从value选择3个来组合 例如 ID VALUE
1 ABC
2 ABD
3 ACD
4 BCD
只需查到其中一种组合,不需要重复的，如(ABC跟ACB归为一行ABC或ACB)请问如何查询
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[VALUE] varchar(1))insert [test]select 1,'A' union allselect 2,'B' union allselect 3,'C' union allselect 4,'D'select px=ROW_NUMBER()over(order by (select 1)), a.[VALUE]+b.VALUE+c.VALUE as VALUE from test across join test bcross join test cwhere a.VALUE<>b.VALUE and a.VALUE<>c.VALUE and b.VALUE<>c.VALUE/*px VALUE------------1 BAC2 BAD3 CAB4 CAD5 DAB6 DAC7 ABC8 ABD9 CBA10 CBD11 DBA12 DBC13 ACB14 ACD15 BCA16 BCD17 DCA18 DCB19 ADB20 ADC21 BDA22 BDC23 CDA24 CDB*/