SQL 差值运算
AA 表如下：
id type date
zs Y 0501
zs Y 0502
zs N 0501
zs N 0502
ls Y 0503
取出各 id 下的 type 的数量:
(1)包含重复的如下：select id,count(type) Count from AA group by id ,type
(2)不包含重复的如下：select id,count( distinct type) Count from AA group by id ,type
现在我想查询 type 的“包含重复”与“不包含重复”的差值：
我是这样写的;
select id,(count( type) - count( distinct type)) Count from AA group by id ,type
但好像不对啊，请达人解答。
[解决办法]
不過這個語句沒有多大意義
select id,count( distinct type) Count from AA group by id ,type
等同於
select id,1 Count from AA group by id ,type
[解决办法]
现在我想查询 type 的“包含重复”与“不包含重复”的差值：(lz想表达的是不是这个意思阿！？)
select
(select distinct count(1)
from AA
group by id,type
having(count(1)> 1)) - --重复的
(select distinct count(1)
from AA
group by id,type
having(count(1)=1)) --不重复的
[解决办法]
select
(select sum(c.count1) from (select count(*) count1 from AA group by type having count(*)=1) c) -
(select isnull(sum(d.count2),0) from (select count(*) count2 from AA group by type having count(*)> 1) d)
