34,593
社区成员
发帖
与我相关
我的任务
分享
declare @table table(担保人 varchar(10),被担保人 varchar(10))
insert @table
select 'A','B' union all
select 'AA','BB' union all
select 'B','C' union all
select 'CC','AA' union all
select 'BB','AA' union all
select 'C','A'
select len(担保人),* from @table order by len(担保人),担保人
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 11:28:05
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([担保人] varchar(2),[被担保人] varchar(2))
insert [huang]
select 'A','B' union all
select 'AA','BB' union all
select 'B','C' union all
select 'CC','AA' union all
select 'BB','AA' union all
select 'C','A'
--------------开始查询--------------------------
IF OBJECT_id('tempdb..#t','u')IS NOT NULL
DROP TABLE #t
select IDENTITY(INT,1,1) AS id,* INTO #t
from [huang]
ORDER BY LEN([担保人]),[担保人]
SELECT * FROM #t
----------------结果----------------------------
/*
id 担保人 被担保人
----------- ---- ----
1 A B
2 B C
3 C A
4 AA BB
5 BB AA
6 CC AA
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 11:28:05
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([担保人] varchar(2),[被担保人] varchar(2))
insert [huang]
select 'A','B' union all
select 'AA','BB' union all
select 'B','C' union all
select 'CC','AA' union all
select 'BB','AA' union all
select 'C','A'
--------------开始查询--------------------------
select ROW_NUMBER()OVER(ORDER BY LEN([担保人]),[担保人])id,* from [huang]
ORDER BY id
----------------结果----------------------------
/*
id 担保人 被担保人
-------------------- ---- ----
1 A B
2 B C
3 C A
4 AA BB
5 BB AA
6 CC AA
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 14:01:50
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([客户内码] bigint,[客户名称] varchar(5),[担保客户内码] bigint,[担保客户名称] varchar(5),[担保金额] int)
insert [huang]
select 81013817122,'尤*友',81013819401,'施*生',90000 union all
select 81013970312,'邵*东',81017614955,'刘*岳',160000 union all
select 81015764769,'陈*生',81036103438,'陈*军',200000 union all
select 81013819401,'施*生',81036208949,'尤*存',90000 union all
select 81013833410,'黄*女',81013879268,'柯*英',80000 union all
select 81013833410,'黄*女',81013879268,'柯*英',80000 union all
select 81013879268,'柯*英',81013881120,'林*林',80000 union all
select 81013881120,'林*林',81013833410,'黄*女',80000 union all
select 81013881120,'林*林',81013833410,'黄*女',80000 union all
select 81036208949,'尤*存',81013817122,'尤*友',200000 union all
select 81013842296,'郭*祥',81013970312,'邵*东',80000 union all
select 81017614955,'刘*岳',81013842296,'郭*祥',100000 union all
select 81030410199,'王*锋',81034723541,'卢*平',280000 union all
select 81036103438,'陈*军',81029291410,'陈*锋',120000 union all
select 81034723541,'卢*平',81013816842,'王*和',200000 union all
select 81013816842,'王*和',81030410199,'王*锋',400000 union all
select 81029291410,'陈*锋',81015764769,'陈*生',210000
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t
IF OBJECT_ID('tempdb..#t2','u')IS NOT NULL
DROP TABLE #t2
SELECT DISTINCT a.[客户内码] AS [担保人A],b.[客户内码] AS [担保人B],c.[客户内码] AS [担保人C],a.[担保金额] AS [担保金额A],b.[担保金额] AS [担保金额B],c.[担保金额] AS [担保金额C] INTO #t
FROM huang a INNER JOIN huang b ON a.[担保客户内码]=b.[客户内码]
INNER JOIN huang c ON b.[担保客户内码]=c.[客户内码] AND c.[担保客户内码]=a.[客户内码]
SELECT *,IDENTITY(INT,1,1) id INTO #t2
FROM (
SELECT 担保人A,担保人B,担保金额B
FROM #t
UNION
SELECT 担保人B,担保人C,担保金额C
FROM #t
UNION
SELECT 担保人C,担保人A,担保金额A
FROM #t)a
SELECT *
FROM #t2 a full JOIN #t2 b ON a.担保人A=b.担保人B
----------------结果----------------------------
/*
担保人A 担保人B 担保金额B id 担保人A 担保人B 担保金额B id
-------------------- -------------------- ----------- ----------- -------------------- -------------------- ----------- -----------
81013816842 81030410199 280000 1 81034723541 81013816842 400000 13
81013817122 81013819401 90000 2 81036208949 81013817122 90000 15
81013819401 81036208949 200000 3 81013817122 81013819401 90000 2
81013833410 81013879268 80000 4 81013881120 81013833410 80000 7
81013842296 81013970312 160000 5 81017614955 81013842296 80000 10
81013879268 81013881120 80000 6 81013833410 81013879268 80000 4
81013881120 81013833410 80000 7 81013879268 81013881120 80000 6
81013970312 81017614955 100000 8 81013842296 81013970312 160000 5
81015764769 81036103438 120000 9 81029291410 81015764769 200000 11
81017614955 81013842296 80000 10 81013970312 81017614955 100000 8
81029291410 81015764769 200000 11 81036103438 81029291410 210000 14
81030410199 81034723541 200000 12 81013816842 81030410199 280000 1
81034723541 81013816842 400000 13 81030410199 81034723541 200000 12
81036103438 81029291410 210000 14 81015764769 81036103438 120000 9
81036208949 81013817122 90000 15 81013819401 81036208949 200000 3
*/