A列数据两两组合，最接近3000的组合是？
A列数据有重复！！！
数据
写法1，A列分为a，b两表，两两组合，减去3000，等于0的就是符合条件的。
Sub 配对()
Dim cnn As Object, rs As Object, LastRow&, SQL$
Set cnn = CreateObject("ADODB.Connection")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
SQL = "select DISTINCT * from (select a.组合,(b.组合,a.组合+b.组合-3000) as 差 from"
SQL = SQL & "[sheet1$a1:a" & LastRow & "] a,[sheet1$a1:a" & LastRow & "] b where a.组合<>b.组合) where 差=0 "
Set rs = cnn.Execute(SQL)
With Sheets("sheet1")
.Range("j1:L100000").ClearContents
.Range("j1").CopyFromRecordset rs
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
- 1、先求两两组合及其减去3000的差值
select a.组合,b.组合,(a.组合+b.组合-3000) as 差 from
[sheet1$a1:a" & LastRow & "] a,
[sheet1$a1:a" & LastRow & "] b
where a.组合<>b.组合
- 2、在上表提取数据，差=0，的就是两两组合等于3000的，再去重复。
select DISTINCT * from
(
select a.组合,b.组合,(a.组合+b.组合-3000) as 差 from
[sheet1$a1:a" & LastRow & "] a,
[sheet1$a1:a" & LastRow &"] b
where a.组合<>b.组合
)
where 差=0
写法2：
- 1、先对原始数据用Distinct去重复
- 2、两两配对，筛选和3000的差等于0的。
为方便引用，用SQL server写了个CTE的,否则要写2个子查询，感觉代码好长。
with CTE_Test as
(
select distinct * from dbo.Sheet1$
)
select c.* from
(
select a.组合 as 组合1 ,b.组合 as 组合2,(a.组合+b.组合-3000) as 差
from CTE_Test as a,CTE_Test as b
where a.组合<>b.组合
) c
where c.差 = 0
order by 组合1
EXCEL示例文件下载：
链接: http://pan.baidu.com/s/1pKXiivD 密码: f2qc
