A1 A2 A3 A4 A5 … An
B1 1 1 3 4 5 … …
B2 6 7 8 9 10 … …
B3 11 56 11 14 15 … …
B4 20 23 25 11 20 … …
B5 … … … … … … …
… … … … … … … …
Bn … … … … … … …现有An列、Bn行,如果想把里面的全部数据按行用组合来取m(m>=2)个出来,然后每行按照从小到大的顺序排列,用sql如何能实现呢?请教大家,谢谢。举例说明下,比如上面的数据,假设有5行5列,每行取4个数,那根据组合每取一行就相应有5行的数据,分别是B1 1 1 3 4
B1 1 3 4 5
B1 1 3 4 5
B1 1 1 4 5
B1 1 1 3 5
...
...
B1 1 1 3 4 5 … …
B2 6 7 8 9 10 … …
B3 11 56 11 14 15 … …
B4 20 23 25 11 20 … …
B5 … … … … … … …
… … … … … … … …
Bn … … … … … … …现有An列、Bn行,如果想把里面的全部数据按行用组合来取m(m>=2)个出来,然后每行按照从小到大的顺序排列,用sql如何能实现呢?请教大家,谢谢。举例说明下,比如上面的数据,假设有5行5列,每行取4个数,那根据组合每取一行就相应有5行的数据,分别是B1 1 1 3 4
B1 1 3 4 5
B1 1 3 4 5
B1 1 1 4 5
B1 1 1 3 5
...
...
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-11 12:04:41
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A1] varchar(2),[A2] int,[A3] int,[A4] int,[A5] int)
insert [tb]
select 'B1',1,1,3,4 union all
select 'B2',2,3,4,5 union all
select 'B3',4,5,6,7
--------------开始查询--------------------------select A.* from [tb] a cross join tb b order by 1
----------------结果----------------------------
/*A1 A2 A3 A4 A5
---- ----------- ----------- ----------- -----------
B1 1 1 3 4
B1 1 1 3 4
B1 1 1 3 4
B2 2 3 4 5
B2 2 3 4 5
B2 2 3 4 5
B3 4 5 6 7
B3 4 5 6 7
B3 4 5 6 7(9 行受影响)
*/
用动态语句 取3个行转列 然后CROSS JOIN
If object_id('ta') is not null
Drop table ta
Go
Create table ta(A1 nvarchar(2),A2 int,A3 int,A4 int,A5 int,A6 int)
Go
Insert into ta
select 'B1',1,1,3,4,5 union all
select 'B2',6,7,8,9,10
Go
--Start
select distinct a.a1,a.a2,b.a2,c.a2,d.a2
from (
Select a1,a2,'a2' as t from ta union all
Select a1,a3,'a3' from ta union all
Select a1,a4,'a4' from ta union all
Select a1,a5,'a5' from ta union all
Select a1,a6,'a6' from ta ) a
, (
Select a1,a2,'a2' as t from ta union all
Select a1,a3,'a3' from ta union all
Select a1,a4,'a4' from ta union all
Select a1,a5,'a5' from ta union all
Select a1,a6,'a6' from ta ) b
, (
Select a1,a2,'a2' as t from ta union all
Select a1,a3,'a3' from ta union all
Select a1,a4,'a4' from ta union all
Select a1,a5,'a5' from ta union all
Select a1,a6,'a6' from ta ) c
, (
Select a1,a2,'a2' as t from ta union all
Select a1,a3,'a3' from ta union all
Select a1,a4,'a4' from ta union all
Select a1,a5,'a5' from ta union all
Select a1,a6,'a6' from ta ) d
where a.a1 = b.a1 and b.a1 = c.a1 and c.a1 = d.a1
and a.t <> b.t and b.t <> c.t and c.t <> d.t
and a.a2 <= b.a2 and b.a2 < c.a2 and c.a2 < d.a2
--and a.
order by a.a1,a.a2
--Result:
/*
a1 a2 a2 a2 a2
---- ----------- ----------- ----------- -----------
B1 1 1 3 4
B1 1 1 3 5
B1 1 1 4 5
B1 1 3 4 5
B2 6 7 8 9
B2 6 7 8 10
B2 6 7 9 10
B2 6 8 9 10
B2 7 8 9 10(所影响的行数为 9 行)*/
--End
fredrickhu
(小F) 用动态语句 取3个行转列 然后CROSS JOIN能详解么?谢谢
http://topic.csdn.net/u/20080920/15/424c77bf-7610-4888-be85-9a43e70f55c6.html?742585楼
自己加个CROSS JOIN 试下 今天我不太舒服 你自己试下