create table t1 ( id int, a1 int, a2 int ) insert into t1 select 1, 1, 1 union all select 2, 1, 1 union all select 3, 0, 1 union all select 4, 0, 1 union all select 5, 1, 0 union all select 6, 1, 0 union all select 7, 0, 0 union all select 8, 0, 0 select * from t1select COUNT(1) from (select distinct a1,a2 from t1) as a
正解,我之前也是这么写,就差as a,所以有错!
select COUNT(*) from ( select a1,a2, COUNT(*) as cnt from tab1 group by a1,a2 having COUNT(*)>1) a
select COUNT(*) from ( select a1,a2, COUNT(*) as cnt from tab1 group by a1,a2 having COUNT(*)>1) a
select a1,a2,count(*) as cnt from tb group by a1,a2 having count(*) > 1
select a1,a2,count(*) as cnt
from tb
group by a1,a2
select a1,a2,count(*) as cnt
from tb
group by a1,a2
having count(*) > 1
select count(1)/2 from tb
SQL codeselect count(1)/2 from tb
并不是简单的除以2!假设我再加一条记录
9 0 0
结果仍为4!我的目的或许可以这样理解:先将表进行分组,然后将分组后的记录存入另外的表tab中,然后再统计tab的记录数。我这样描述是否大家理解了!不过这样就显得麻烦!能否不用这个方法,只要一个语句就可以实现!
(
id int,
a1 int,
a2 int
)
insert into t1
select 1, 1, 1 union all
select 2, 1, 1 union all
select 3, 0, 1 union all
select 4, 0, 1 union all
select 5, 1, 0 union all
select 6, 1, 0 union all
select 7, 0, 0 union all
select 8, 0, 0
select * from t1select COUNT(1) from
(select distinct a1,a2 from t1) as a
select COUNT(*) from
(
select a1,a2, COUNT(*) as cnt from tab1 group by a1,a2 having COUNT(*)>1) a
(
select a1,a2, COUNT(*) as cnt from tab1 group by a1,a2 having COUNT(*)>1) a
from tb
group by a1,a2
having count(*) > 1