---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-04-09 12:20:04 -- Version: -- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) -- May 3 2005 23:18:38 -- Copyright (c) 1988-2003 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb([a] int,[b] int) insert #tb select 1,2 union all select 3,5 union all select 1,7 union all select 2,1 union all select 5,8 union all select 5,3 union all select 1,2 --------------开始查询-------------------------- SELECT DISTINCT CASE WHEN A>=B THEN B ELSE A END A, CASE WHEN A>=B THEN A ELSE B END B FROM #TB -------------- /* (所影响的行数为 7 行)A B ----------- ----------- 1 2 1 7 3 5 5 8(所影响的行数为 4 行) */
select * from tb t where not exists(select 1 from tb where a=t.a and b=t.b) or not exists(select 1 from tb where a=t.b and b=t.a)
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-04-09 12:20:04 -- Version: -- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) -- May 3 2005 23:18:38 -- Copyright (c) 1988-2003 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb([a] int,[b] int) insert #tb select 1,2 union all select 3,5 union all select 1,7 union all select 2,1 union all select 5,8 union all select 5,3 union all select 1,2 --------------开始查询-------------------------- SELECT DISTINCT CASE WHEN A>=B THEN B ELSE A END A, CASE WHEN A>=B THEN A ELSE B END B FROM #TB ORDER BY CASE WHEN A>=B THEN A ELSE B END -------------- /* (所影响的行数为 7 行)A B ----------- ----------- 1 2 3 5 1 7 5 8(所影响的行数为 4 行)*/--修改排序
如果列值不是数字,而是杂乱的字符串,还能用 A>=B 吗
太聪明了,我原来是 select a,b from t union select b,a from t 效果可想而知,呵呵 没想过可以直接左右排序后DISTINCT,真是开阔思路了谢谢哦,如果是字符也可以,效果差不多,只是顺序会不一样,我想是因为 >= 让结果有了按大小优先筛选的效果 我改成了excel的 SELECT DISTINCT IIF (A>=B,B,A), IIF (A>=B,A,b) FROM T2
-- Author :SQL77(只为思齐老)
-- Date :2010-04-09 12:20:04
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([a] int,[b] int)
insert #tb
select 1,2 union all
select 3,5 union all
select 1,7 union all
select 2,1 union all
select 5,8 union all
select 5,3 union all
select 1,2
--------------开始查询--------------------------
SELECT
DISTINCT
CASE WHEN A>=B THEN B ELSE A END A,
CASE WHEN A>=B THEN A ELSE B END B
FROM #TB
--------------
/* (所影响的行数为 7 行)A B
----------- -----------
1 2
1 7
3 5
5 8(所影响的行数为 4 行)
*/
*
from
tb t
where
not exists(select 1 from tb where a=t.a and b=t.b)
or
not exists(select 1 from tb where a=t.b and b=t.a)
-- Author :SQL77(只为思齐老)
-- Date :2010-04-09 12:20:04
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([a] int,[b] int)
insert #tb
select 1,2 union all
select 3,5 union all
select 1,7 union all
select 2,1 union all
select 5,8 union all
select 5,3 union all
select 1,2
--------------开始查询--------------------------
SELECT
DISTINCT
CASE WHEN A>=B THEN B ELSE A END A,
CASE WHEN A>=B THEN A ELSE B END B
FROM #TB
ORDER BY
CASE WHEN A>=B THEN A ELSE B END
--------------
/*
(所影响的行数为 7 行)A B
----------- -----------
1 2
3 5
1 7
5 8(所影响的行数为 4 行)*/--修改排序
select a,b from t
union
select b,a from t
效果可想而知,呵呵
没想过可以直接左右排序后DISTINCT,真是开阔思路了谢谢哦,如果是字符也可以,效果差不多,只是顺序会不一样,我想是因为 >= 让结果有了按大小优先筛选的效果
我改成了excel的
SELECT DISTINCT
IIF (A>=B,B,A),
IIF (A>=B,A,b)
FROM T2