INSERT INTO T_VOTE
SELECT '1' , 'Y' , GETDATE() UNION ALL
SELECT '2' , 'N' , GETDATE() UNION ALL
SELECT '2' , 'N' , GETDATE() UNION ALL
SELECT '1' , 'N' , GETDATE() UNION ALL
SELECT '1' , 'N' , GETDATE()+1
SELECT * FROM T_VOTE WHERE 1 = 1
SELECT VOTE_DATE ,
(SELECT COUNT(*) FROM T_VOTE WHERE IS_MEMBER = 'Y' AND VOTE_DATE = A.VOTE_DATE) AS MEMBER_VOTE,
(SELECT COUNT(*) FROM T_VOTE WHERE IS_MEMBER = 'N' AND VOTE_DATE = A.VOTE_DATE) AS NOT_MEMBER_VOTE,
(SELECT COUNT(*) FROM T_VOTE WHERE VOTE_DATE = A.VOTE_DATE) AS ALL_VOTE
FROM T_VOTE A
WHERE 1 = 1
GROUP BY VOTE_DATE
说明一个邮箱对应一个人,但一个邮箱每天可以投若干次,这样 直接查询就会产生大量的
重复值 把重复值都过滤掉 不就是每天投票的人数了吗?
select distinct riqi,sum( case when 是会员 then 1 else 0) as 会员投票数,
sum( case when 否会员 then 1 else 0) as 非会员投票数,
into #lsb
from 库表
group by riqi
select riqi,会员投票数,非会员投票数,count(1) as 总人数
from #lsb
drop table #lsb
select c.投票日期,isnull (a.投票次数,0) as 会员次数,isnull (b.投票次数,0 ) as 非会员次数 ,(isnull (a.投票次数,0)+isnull (b.投票次数,0 )) as 总次数
from (select 投票日期,count(distinct 投票邮箱) as 投票次数
from tikets
where 是否会员='是'
group by 投票日期) a,
(select 投票日期,count(distinct 投票邮箱)as 投票次数
from tikets
where 是否会员='否'
group by 投票日期) b,
(select distinct 投票日期 from tikets) c
select c.投票日期,isnull (a.投票次数,0) as 会员次数,b.投票次数 as 非会员次数 ,
(会员次数+非会员次数 ) as 总次数
from (select 投票日期,count(投票邮箱)as 投票次数 from tikets where 是否会员 ='是' group by 投票日期) a,
(select 投票日期,count(投票邮箱)as 投票次数 from tikets where 是否会员='否' group by 投票日期) b,
(select distinct 投票日期 from tikets) c
去掉重复值
select distinct riqi,sum( case when 是会员 then 1 else 0) as 会员投票数,
sum( case when 否会员 then 1 else 0) as 非会员投票数,
count(*) as 总人数
from 库表
group by riqi