22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('t1')is not null drop table t1
go
create table t1(电线顺序 varchar(10),左端子 varchar(10),右端子 varchar(10))
insert t1
select 'S1','A','B'
union select 'S2','A','C'
union select 'S3','C','B'
union select 'S4','W','C'
union select 'S5','C','B'
union select 'S6','B','A'
union select 'S7','A','C'
union select 'S8','D','E'
union select 'S9','F','B'
union select 'SA','F','C'
union select 'SB','C','F'
union select 'SC','G','B'
select * into #t from (
select 电线顺序,左端子,右端子 from t1
union all
select 电线顺序,右端子,左端子 from t1) v
order by 左端子,右端子
select t1.* from #t a left join t1 on a.电线顺序=t1.电线顺序
where not exists(select 1 from #t b
where b.电线顺序=a.电线顺序 and b.左端子<a.左端子)
/*
电线顺序 左端子 右端子
---------- ---------- ----------
S1 A B
S6 B A
S2 A C
S7 A C
S3 C B
S5 C B
S9 F B
SC G B
SB C F
SA F C
S4 W C
S8 D E
(所影响的行数为 12 行)
*/
drop table #t
create table t1(电线顺序 varchar(2),左端子 varchar(1),右端子 varchar(1))
insert t1
select 'S1','A','B'
union select 'S2','A','C'
union select 'S3','D','B'
union select 'S4','D','C'
union select 'S5','C','B'
union select 'S6','A','B'
union select 'S7','A','C'
union select 'S8','D','C'
union select 'S9','C','B'
select * into #t from (
select 电线顺序,左端子+'左' 左端子 ,右端子+'右' 右端子 from t1
union all
select 电线顺序,右端子+'右',左端子+'左' from t1) v
order by 左端子,右端子
select a.*,t1.* from #t a left join t1 on a.电线顺序=t1.电线顺序
where not exists(select 1 from #t b
where b.电线顺序=a.电线顺序 and b.左端子<a.左端子)
/*
电线顺序 左端子 右端子 电线顺序 左端子 右端子
---- ---- ---- ---- ---- ----
S1 A左 B右 S1 A B
S6 A左 B右 S6 A B
S7 A左 C右 S7 A C
S2 A左 C右 S2 A C
S5 B右 C左 S5 C B
S9 B右 C左 S9 C B
S3 B右 D左 S3 D B
S8 C右 D左 S8 D C
S4 C右 D左 S4 D C
(所影响的行数为 9 行)
*/
drop table #t
create table #t1(电线顺序 varchar(10),左端子 varchar(10),右端子 varchar(10))
insert #t1
select 'S1','A','B'
union select 'S2','A','C'
union select 'S3','C','B'
union select 'S4','W','C'
union select 'S5','C','B'
union select 'S6','B','A'
union select 'S7','A','C'
union select 'S8','D','E'
union select 'S9','F','B'
union select 'SA','F','C'
union select 'SB','C','F'
union select 'SC','G','B'
select *
from #t1
select *
into #t2
from(
select 电线顺序,左端子 as 端子 from #t1
union all
select 电线顺序,右端子 from #t1
) a
order by 1,2
select a.*
from #t1 a
join(
select 电线顺序,
px=(select ''+端子 from #t2 where 电线顺序=t.电线顺序 for xml path(''))
from #t2 t
group by 电线顺序) b
on a.电线顺序=b.电线顺序
order by b.px,a.电线顺序
/**
电线顺序 左端子 右端子
---------- ---------- ----------
S1 A B
S6 B A
S2 A C
S7 A C
S3 C B
S5 C B
S9 F B
SC G B
SA F C
SB C F
S4 W C
S8 D E
(12 行受影响)
**/
create table #t1(电线顺序 varchar(10),左端子 varchar(10),右端子 varchar(10))
insert #t1
select 'S1','A','B'
union select 'S2','A','C'
union select 'S3','C','B'
union select 'S4','W','C'
union select 'S5','C','B'
union select 'S6','B','A'
union select 'S7','A','C'
union select 'S8','D','E'
union select 'S9','F','B'
union select 'SA','F','C'
union select 'SB','C','F'
union select 'SC','G','B'
select case when 左端子 < 右端子 then 左端子 + 右端子 else 右端子 + 左端子 end,* from #t1 order by case when 左端子 < 右端子 then 左端子 + 右端子 else 右端子 + 左端子 end
drop table #t1
CREATE TABLE #tmpJob(
电线顺序 varchar(10) NOT NULL,
左端子 varchar(10) NOT NULL,
右端子 varchar(10) NOT NULL)
GO
INSERT INTO #tmpJob VALUES('S1','A','B')
INSERT INTO #tmpJob VALUES('S2','A','C')
INSERT INTO #tmpJob VALUES('S3','C','B')
INSERT INTO #tmpJob VALUES('S4','W','C')
INSERT INTO #tmpJob VALUES('S5','C','B')
INSERT INTO #tmpJob VALUES('S6','B','A')
INSERT INTO #tmpJob VALUES('S7','A','C')
INSERT INTO #tmpJob VALUES('S8','D','E')
INSERT INTO #tmpJob VALUES('S9','F','B')
INSERT INTO #tmpJob VALUES('SA','F','C')
INSERT INTO #tmpJob VALUES('SB','C','F')
INSERT INTO #tmpJob VALUES('SC','G','B')
GO
--结果
SELECT *
FROM (
SELECT 电线顺序,左端子,右端子
FROM #tmpJob
WHERE 左端子 <= 右端子
UNION ALL
SELECT 电线顺序,右端子,左端子
FROM #tmpJob
WHERE 左端子 > 右端子
) tmp
ORDER BY 左端子,右端子
declare @a table(a VARCHAR(20),b VARCHAR(20),c VARCHAR(20))
INSERT @a select 'S1','A','B'
union select 'S2','A','C'
union select 'S3','C','B'
union select 'S4','W','C'
union select 'S5','C','B'
union select 'S6','B','A'
union select 'S7','A','C'
union select 'S8','D','E'
union select 'S9','F','B'
union select 'SA','F','C'
union select 'SB','C','F'
union select 'SC','G','B'
SELECT *
FROM @a a
ORDER BY
CASE WHEN exists(SELECT 1 FROM @a WHERE b=a.c AND c=a.b) THEN (select min(CASE WHEN c>b THEN b ELSE c END) FROM @a WHERE b=a.c AND c=a.b)+'0' ELSE
a.b+'1'END,b,c
--result
/*a b c
-------------------- -------------------- --------------------
S1 A B
S6 B A
S7 A C
S2 A C
SB C F
SA F C
S5 C B
S3 C B
S8 D E
S9 F B
SC G B
S4 W C
(所影响的行数为 12 行)
*/