1
2
3
4
5
| with temp_table as (
select '1' as f1, null as f2
union all
select '1', '3'
)
|
count distinct 结果是字段均不为null的去重值
所以在通过count distinct判断字段的重复情况时，注意把相关字段的null附上值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| select count(*)
from temp_table
`count`(*)
2
select count(distinct f1, f2)
from temp_table
`count`(DISTINCT `f1`, `f2`)
1
select count(f1, f2)
from temp_table
`count`(`f1`, `f2`)
1
|
关联字段中有null时是关联不上的，即使左右都是null
1
2
3
4
5
6
7
8
9
| select a.f1, a.f2, b.f1, b.f2
from temp_table a
left join temp_table b
on a.f1 = b.f1
and a.f2 = b.f2
f1 f2 f1 f2
1 NULL NULL NULL
1 3 1 3
|
sum中包含null，不会影响sum的值
1
2
3
4
5
6
7
8
| select sum(ct)
from(select 1 as ct
union all
select null)a
`sum`(`ct`)
1
|