數(shù)據(jù)
數(shù)據(jù)
求屬性1和屬性2的差值(絕對值)小于300的數(shù)據(jù)且屬性1的計(jì)數(shù)大于等于2昵宇。
select a.*,abs(a.屬性1-a.屬性2) as 差
from [Sheet1$a1:c] a,
(
select 屬性1 from [Sheet1$a1:c]
group by 屬性1 having count(屬性1)>1
) b
where a.屬性1=b.屬性1
and
abs(a.屬性1-a.屬性2) <300
解釋:
- 1、先求出大于等于2的屬性1有那些數(shù)據(jù),用group by having count
select 屬性1 from [Sheet1$a1:c]
group by 屬性1 having count(屬性1)>1
- 2、然后將原表和上表用where做篩選切黔,篩選出屬性1計(jì)數(shù)大于1的數(shù)據(jù)砸脊,在篩選的時(shí)候具篇,新增一個(gè)‘差值’字段計(jì)算屬性1和屬性2的差,同樣作為篩選的條件凌埂。這樣即可篩選出結(jié)果集驱显。條件:屬性1計(jì)數(shù)大于1的且屬性1和屬性2差值的絕對值小于300的。
如上面代碼
select a.*,abs(a.屬性1-a.屬性2) as 差
from [Sheet1$a1:c] a,
(
select 屬性1 from [Sheet1$a1:c]
group by 屬性1 having count(屬性1)>1
) b
where a.屬性1=b.屬性1 --//條件1
and
abs(a.屬性1-a.屬性2) <300 --//條件2
結(jié)果
示例文件下載
鏈接: http://pan.baidu.com/s/1c1FnUwg 密碼: seq8