現在有一張表TT(Tid,Idate,T1,T2,T3,T4,T5,T6),有幾百萬條數據,現在要求查詢出字段T1,T2,T3,T4,T5,T6中至少有兩個字段不為空的數據。 請教有什麼好的算法可能實現!! 先謝謝!!--笨办法 select * from tt where t1 is not null and t2 is not null union all select * from tt where t1 is not null and t3 is not null union all select * from tt where t1 is not null and t4 is not null union all select * from tt where t1 is not null and t5 is not null union all select * from tt where t1 is not null and t6 is not null union all select * from tt where t2 is not null and t3 is not null union all select * from tt where t2 is not null and t4 is not null union all select * from tt where t2 is not null and t5 is not null union all select * from tt where t2 is not null and t6 is not null union all select * from tt where t3 is not null and t4 is not null union all select * from tt where t3 is not null and t5 is not null union all select * from tt where t3 is not null and t6 is not null union all select * from tt where t4 is not null and t5 is not null union all select * from tt where t4 is not null and t6 is not null union all select * from tt where t5 is not null and t6 is not null
select * from tt where decode(t1,null,0,1)+decode(t2,null,0,1)+decode(t3,null,0,1)+ decode(t4,null,0,1)+decode(t5,null,0,1)+decode(t6,null,0,1)>=2
select * , nullnum = case t1 is not null then 1 else 0 end + case t2 is not null then 1 else 0 end + case t3 is not null then 1 else 0 end + case t4 is not null then 1 else 0 end + case t5 is not null then 1 else 0 end + case t6 is not null then 1 else 0 end from tt where nullnum >= 2
晕!~~~ 看错板块了,sqlserver中可以将decode用case when 替换
create table tb (t1 int,t2 int, t3 int) insert into tb values(1 , null , 1) insert into tb values(2 , 1 , null) insert into tb values(1 , null , null) insert into tb values(1 , null , null) insert into tb values(null, null , null) insert into tb values(1 , 1 , 1) go select * from tb /* t1 t2 t3 ----------- ----------- ----------- 1 NULL 1 2 1 NULL 1 NULL NULL 1 NULL NULL NULL NULL NULL 1 1 1 (所影响的行数为 6 行) */ select * from tb where case when t1 is not null then 1 else 0 end + case when t2 is not null then 1 else 0 end + case when t3 is not null then 1 else 0 end >= 2 /* t1 t2 t3 ----------- ----------- ----------- 1 NULL 1 2 1 NULL 1 1 1 (所影响的行数为 3 行) */ drop table tb
乌龟的这写法可以,顶一下` select * , nullnum = case t1 is not null then 1 else 0 end + case t2 is not null then 1 else 0 end + case t3 is not null then 1 else 0 end + case t4 is not null then 1 else 0 end + case t5 is not null then 1 else 0 end + case t6 is not null then 1 else 0 end from tt where nullnum >= 2
create table # (t1 int,t2 int, t3 int) insert into # values(1 , null , 1) insert into # values(2 , 1 , null) insert into # values(1 , null , null) insert into # values(1 , null , null) insert into # values(null, null , null) insert into # values(1 , 1 , 1)select * from # where (case when t1 is null then 1 else 0 end + case when t2 is null then 1 else 0 end + case when t3 is null then 1 else 0 end )>=2
select * , nullnum = case t1 is not null then 1 else 0 end + case t2 is not null then 1 else 0 end + case t3 is not null then 1 else 0 end + case t4 is not null then 1 else 0 end + case t5 is not null then 1 else 0 end + case t6 is not null then 1 else 0 end from tt where nullnum >= 2 ___________ 上面的写法是错的
select T1,T2,T3,T4,T5,T6 from ( select * ,rightitems as case t1 is not null then 1 else 0 end + case t2 is not null then 1 else 0 end + case t3 is not null then 1 else 0 end + case t4 is not null then 1 else 0 end + case t5 is not null then 1 else 0 end + case t6 is not null then 1 else 0 end ) as a where a.rightitems>=2
select T1,T2,T3,T4,T5,T6 from ( select * ,rightitems as case when t1 is not null then 1 else 0 end + case when t2 is not null then 1 else 0 end + case when t3 is not null then 1 else 0 end + case when t4 is not null then 1 else 0 end + case when t5 is not null then 1 else 0 end + case when t6 is not null then 1 else 0 end ) as a where a.rightitems >=2
請教有什麼好的算法可能實現!!
先謝謝!!--笨办法
select * from tt where t1 is not null and t2 is not null
union all
select * from tt where t1 is not null and t3 is not null
union all
select * from tt where t1 is not null and t4 is not null
union all
select * from tt where t1 is not null and t5 is not null
union all
select * from tt where t1 is not null and t6 is not null
union all
select * from tt where t2 is not null and t3 is not null
union all
select * from tt where t2 is not null and t4 is not null
union all
select * from tt where t2 is not null and t5 is not null
union all
select * from tt where t2 is not null and t6 is not null
union all
select * from tt where t3 is not null and t4 is not null
union all
select * from tt where t3 is not null and t5 is not null
union all
select * from tt where t3 is not null and t6 is not null
union all
select * from tt where t4 is not null and t5 is not null
union all
select * from tt where t4 is not null and t6 is not null
union all
select * from tt where t5 is not null and t6 is not null
decode(t1,null,0,1)+decode(t2,null,0,1)+decode(t3,null,0,1)+
decode(t4,null,0,1)+decode(t5,null,0,1)+decode(t6,null,0,1)>=2
case t2 is not null then 1 else 0 end +
case t3 is not null then 1 else 0 end +
case t4 is not null then 1 else 0 end +
case t5 is not null then 1 else 0 end +
case t6 is not null then 1 else 0 end
from tt
where nullnum >= 2
看错板块了,sqlserver中可以将decode用case when 替换
insert into tb values(1 , null , 1)
insert into tb values(2 , 1 , null)
insert into tb values(1 , null , null)
insert into tb values(1 , null , null)
insert into tb values(null, null , null)
insert into tb values(1 , 1 , 1)
go
select * from tb
/*
t1 t2 t3
----------- ----------- -----------
1 NULL 1
2 1 NULL
1 NULL NULL
1 NULL NULL
NULL NULL NULL
1 1 1
(所影响的行数为 6 行)
*/
select * from tb where case when t1 is not null then 1 else 0 end + case when t2 is not null then 1 else 0 end + case when t3 is not null then 1 else 0 end >= 2
/*
t1 t2 t3
----------- ----------- -----------
1 NULL 1
2 1 NULL
1 1 1
(所影响的行数为 3 行)
*/
drop table tb
select * , nullnum = case t1 is not null then 1 else 0 end +
case t2 is not null then 1 else 0 end +
case t3 is not null then 1 else 0 end +
case t4 is not null then 1 else 0 end +
case t5 is not null then 1 else 0 end +
case t6 is not null then 1 else 0 end
from tt
where nullnum >= 2
一条数据 是否T1,T2,T3,T4,T5,T6有两个为空的标志位,然后进是定义一个触发器插入数据时候进行检查设置相应的标志位。
insert into # values(1 , null , 1)
insert into # values(2 , 1 , null)
insert into # values(1 , null , null)
insert into # values(1 , null , null)
insert into # values(null, null , null)
insert into # values(1 , 1 , 1)select * from #
where (case when t1 is null then 1 else 0 end +
case when t2 is null then 1 else 0 end +
case when t3 is null then 1 else 0 end
)>=2
select * , nullnum = case t1 is not null then 1 else 0 end +
case t2 is not null then 1 else 0 end +
case t3 is not null then 1 else 0 end +
case t4 is not null then 1 else 0 end +
case t5 is not null then 1 else 0 end +
case t6 is not null then 1 else 0 end
from tt
where nullnum >= 2
___________
上面的写法是错的
select * ,rightitems as
case t1 is not null then 1 else 0 end +
case t2 is not null then 1 else 0 end +
case t3 is not null then 1 else 0 end +
case t4 is not null then 1 else 0 end +
case t5 is not null then 1 else 0 end +
case t6 is not null then 1 else 0 end
) as a
where a.rightitems>=2
select * ,rightitems as
case when t1 is not null then 1 else 0 end +
case when t2 is not null then 1 else 0 end +
case when t3 is not null then 1 else 0 end +
case when t4 is not null then 1 else 0 end +
case when t5 is not null then 1 else 0 end +
case when t6 is not null then 1 else 0 end
) as a
where a.rightitems >=2
这时T1..T6列假设均已建立索引,那么对T1列null的记录,只需看剩余中是否有1列为null,
只要发现Ti列为null即条件满足,T(i+1)后面的就不必看了。
通过索引可以快速找到null和非null的分离点,
即
find_nulls(Table tbl, int[] rows, int min_count, int start_col, int end_col) {
// 为了算法看上去简洁,当rows==null时表示tbl.all_rows,
// 即凡是 rows 是 (rows == null ? tbl.all_rows : rows) 的简写。
if (min_count == 0) {
print rows;
return;
}
int first_not_null = tbl.index_vector[start_col].index(not null); // 注意这里的intersect和数组的slicing都可以通过iterator组合实现,
// 而不必有大的存储开销,在速度上,因为组合中的call多了很多中间层,
// 但这个相较于算法本身的优越性是可以忽略的。
find_nulls(tbl, intersect(rows, tbl.index_vector[start_col][0..first_not_null-1]),
min_count - 1, start_col + 1, end_col);
find_nulls(tbl, intersect(rows, tbl.index_vector[start_col][first_not_null..END]),
min_count, start_col + 1, end_col);
}find_nulls(tt, null, 2, 1, 6); // T1..T6, min 2 nulls这个算法是无法用sql实现的,因为sql本身不允许你select到它自身的递归,即使你使用游标,也因为不能直接访问索引数据而使算法失去意义。