有sql数据表tb
no select text 1 1 温度
1 1 气压
1 0 天气
1 1 水分
1 0 压力
26 1 气压
26 1 水分
26 0 天气
14 1 水分
14 0 湿度
14 1 温度
14 1 气压
2 1 湿度
2 0 天气
…… (其中select中1代表条件,0代表结论)
用语句表达冗余是:
if a then b and d
-----if a then c
-----if c then b也就是说1的条件推出天气和压力,相同条件中14推出湿度,而2中湿度推出1的结论可能是天气,也可能是气压得到的结果是id rongyu1 rongyu2
1 14 2
……
no select text 1 1 温度
1 1 气压
1 0 天气
1 1 水分
1 0 压力
26 1 气压
26 1 水分
26 0 天气
14 1 水分
14 0 湿度
14 1 温度
14 1 气压
2 1 湿度
2 0 天气
…… (其中select中1代表条件,0代表结论)
用语句表达冗余是:
if a then b and d
-----if a then c
-----if c then b也就是说1的条件推出天气和压力,相同条件中14推出湿度,而2中湿度推出1的结论可能是天气,也可能是气压得到的结果是id rongyu1 rongyu2
1 14 2
……
对于no1来说,就是温度,气压,水分推出天气和压力
对于no14来说,就是温度,气压,水分推出湿度
对于no2来说,就是湿度推出天气这样判no14和no2对于no1造成了冗余
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (N int,S int,T varchar(10))
insert into #T
select 1,1,'温度' union all
select 1,1,'气压' union all
select 1,0,'天气' union all
select 1,1,'水分' union all
select 1,0,'压力' union all
select 26,1,'气压' union all
select 26,1,'水分' union all
select 26,0,'天气' union all
select 14,1,'水分' union all
select 14,0,'湿度' union all
select 14,1,'温度' union all
select 14,1,'气压' union all
select 2,1,'湿度' union all
select 2,0,'天气'--> 添加聚集索引,控制顺序,以便合并比较:
create clustered index IX_#T on #T (N,S,T);--> 2005写法,2000相对复杂:
with T as
(
select
*
from
(select distinct N from #T) a
outer apply
(select Con=stuff((select ','+T from #T where N=a.N and S<>0 for xml path('')),1,1,'')) b
outer apply
(select Res=stuff((select ','+T from #T where N=a.N and S=0 for xml path('')),1,1,'')) c
)
select
id=a.N,
rongyu1=b.N,
rongyu2=c.N
from
T a
join
T b
on a.Con=b.Con
join
T c
on b.Res=c.Con
where
a.Con like '%'+c.Res+'%' or a.Res like '%'+c.Res+'%'/*
id rongyu1 rongyu2
----------- ----------- -----------
1 14 2
*/
2000不支持 for xml
create table tl(nn int,ss int,tt varchar(10))
insert tl select 1, 1, '温度'
union select 1, 1, '气压'
union select 1, 0, '天气'
union select 1, 1, '水分'
union select 1, 0, '压力'
union select 26, 1, '气压'
union select 26, 1, '水分'
union select 26, 0, '天气'
union select 14, 1, '水分'
union select 14, 0, '湿度'
union select 14, 1, '温度'
union select 14, 1, '气压'
union select 2 , 1, '湿度'
union select 2 , 0, '天气'
go
select distinct nn,ann,bnn from tl a,
(select distinct a.nn ann,b.nn bnn from tl a,tl b
where a.nn<>b.nn and not exists(select 1 from
(select * from tl where ss = 0 and nn = a.nn) c full join
(select * from tl where ss = 1 and nn = b.nn) d on c.tt = b.tt
where c.nn is null or d.nn is null)) b
where nn <> ann and nn <> bnn
and not exists(select 1 from
(select * from tl where ss = 1 and nn=a.nn) c full join
(select * from tl where ss = 1 and nn =b.ann) d on c.tt = d.tt
where c.nn is null or d.nn is null)
and not exists(select 1 from
(select * from tl where ss = 0 and nn=a.nn) c right join
(select * from tl where ss = 0 and nn =b.bnn) d on c.tt = d.tt
where d.nn is null)
/*
nn ann bnn
----------- ----------- -----------
1 14 2
*/
go
drop table tl