有两列数据
a b
a c
a d
b c
b d
c a
d b
d c
d a如 a b和 b a 算一条数据请问如何才能求出结果
a b
a c
a d
b c
b d
d c求sql语句
a b
a c
a d
b c
b d
c a
d b
d c
d a如 a b和 b a 算一条数据请问如何才能求出结果
a b
a c
a d
b c
b d
d c求sql语句
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([DDD] varchar(1),[TTT] varchar(1))
insert #tb
select 'a','b' union all
select 'a','c' union all
select 'a','d' union all
select 'b','c' union all
select 'b','d' union all
select 'c','a' union all
select 'd','b' union all
select 'd','c' union all
select 'd','a'
;with a as (
select [DDD],[TTT],
ROW_NUMBER()over(PARTITION by (CHECKSUM(DDD) + CHECKSUM(TTT)) order by [DDD],[TTT]) as px
from #tb)
select [DDD],[TTT] from a where px=1
(select a,b from t1
union all
select b,a from t2)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id1 varchar(1),id2 varchar(1))
insert into #tb
select 'a','b' union all
select 'a','c' union all
select 'a','d' union all
select 'b','c' union all
select 'b','d' union all
select 'c','a' union all
select 'd','b' union all
select 'd','c' union all
select 'd','a'alter table #tb add px int identity( 1,1)select distinct id1,id2 from #tb t
where not exists(select * from #tb where id1=t.id2 and t.id1=id2)
union all
select distinct id1,id2 from #tb t
where exists(select * from #tb where id1=t.id2 and t.id1=id2 and t.px<px)
id1 id2
---- ----
a b
b c
d c
a c
a d
b d(6 行受影响)
GO
CREATE TABLE TB
(
COL1 CHAR(1),
COL2 CHAR(1)
)
INSERT INTO TB
SELECT 'a','b' UNION ALL
SELECT 'a','c' UNION ALL
SELECT 'a','d' UNION ALL
SELECT 'b','c' UNION ALL
SELECT 'b','d' UNION ALL
SELECT 'c','a' UNION ALL
SELECT 'd','b' UNION ALL
SELECT 'd','c' UNION ALL
SELECT 'd','a';WITH CTE AS
(
SELECT DISTINCT COL1,COL2 FROM TB
)
SELECT * FROM CTE A WHERE NOT EXISTS(SELECT 1 FROM CTE B WHERE A.COL1=B.COL2 AND A.COL2=B.COL1)
------------
COL1 COL2
a b
b c
d c
minus
(
select b, a from t1
intersect
select a, b from t1
)
IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GOCREATE TABLE TB
(
COL1 CHAR(1),
COL2 CHAR(1)
)
INSERT INTO TB
SELECT 'a','b' UNION ALL
SELECT 'a','c' UNION ALL
SELECT 'a','d' UNION ALL
SELECT 'b','c' UNION ALL
SELECT 'b','d' UNION ALL
SELECT 'c','a' UNION ALL
SELECT 'd','b' UNION ALL
SELECT 'd','c' UNION ALL
SELECT 'd','a'
goselect a.*
from tb a left join tb b on a.col1 = b.col2 and a.col2 = b.col1
where b.col1 is null or b.col1 < b.col2/*
COL1 COL2
---- ----
a b
b c
c a
d b
d c
d a(6 row(s) affected)
*/楼主,记得结贴哦