表结构以及数据:
id mobile status date
1 1231321 1 2011-04-02
2 4646546 1 2011-04-05
3 4646546 0 2011-04-05
4 4679464 0 2011-04-05
5 4679464 0 2011-04-06
6 7896461 1 2011-04-06
7 1231321 0 2011-04-06
8 1231321 0 2011-04-07
9 7964431 0 2011-04-06
10 7964431 0 2011-04-05
11 7964431 0 2011-04-05
12 4646546 1 2011-04-06 其中:status是表示的是扣费状态,0为扣费失败,1为扣费成功,只有这一张表我想要的查询结果:
扣费成功的手机号 扣费成功条数 扣费失败的条数 扣费失败的手机号码
1231321 1 2 1231321
4646546 2 1 4646546
NULL NULL 2 4679464
NULL NULL 3 7964431
7896461 1 NULL NULL
如果匹配的,就显示出来,
如果不匹配,还是要显示出来,不匹配的地方输出NULL
谢谢了
id mobile status date
1 1231321 1 2011-04-02
2 4646546 1 2011-04-05
3 4646546 0 2011-04-05
4 4679464 0 2011-04-05
5 4679464 0 2011-04-06
6 7896461 1 2011-04-06
7 1231321 0 2011-04-06
8 1231321 0 2011-04-07
9 7964431 0 2011-04-06
10 7964431 0 2011-04-05
11 7964431 0 2011-04-05
12 4646546 1 2011-04-06 其中:status是表示的是扣费状态,0为扣费失败,1为扣费成功,只有这一张表我想要的查询结果:
扣费成功的手机号 扣费成功条数 扣费失败的条数 扣费失败的手机号码
1231321 1 2 1231321
4646546 2 1 4646546
NULL NULL 2 4679464
NULL NULL 3 7964431
7896461 1 NULL NULL
如果匹配的,就显示出来,
如果不匹配,还是要显示出来,不匹配的地方输出NULL
谢谢了
select mobile 手机号,
sum(case status when 1 then 1 else 0 end) 扣费成功数量,
sum(case status when 0 then 1 else 0 end) 扣费失败数量
from tb
group by mobile
扣费成功条数,
扣费不成功的手机号=case when 扣费不成功条数 is not null then mobible end,
扣费不成功条数
(select mobible,扣费成功条数=sum(case when status 1 then 1 end ),
扣费不成功条数=sum(case when status 1 then 1 end )
from 表 group by mobible) a
扣费成功条数,
扣费不成功的手机号=case when 扣费不成功条数 is not null then mobible end,
扣费不成功条数
(select mobible,扣费成功条数=sum(case when status 1 then 1 end ),
扣费不成功条数=sum(case when status 0 then 1 end )
from 表 group by mobible) a
上面有一个小地方注意,改好了,用这个
select mobile,count(1) as successcount from 表 group by mobile where status =1) a
left join (select count(1) as falsecount from 表 group by mobile where status =0) b on a.mobile=b.mobile
供参考 不知道是不是你理解的意思
select case when status =1 mobile end as 扣费成功手机号,
sum(case status when 1 then 1 else 0 end) 扣费成功数量,
sum(case status when 0 then 1 else 0 end) 扣费失败数量,
case when status =0 mobile end as 扣费失败手机号,
from tb
group by mobile
create table t1
(
id int,
mobile varchar(20),
[status] int,
[date] datetime
)
insert into t1
select 1, '1231321', 1, '2011-04-02' union all
select 2, '4646546', 1, '2011-04-05' union all
select 3, '4646546', 0, '2011-04-05' union all
select 4, '4679464', 0, '2011-04-05' union all
select 5, '4679464', 0, '2011-04-06' union all
select 6, '7896461', 1, '2011-04-06' union all
select 7, '1231321', 0, '2011-04-06' union all
select 8, '1231321', 0, '2011-04-07' union all
select 9, '7964431', 0, '2011-04-06' union all
select 10, '7964431', 0, '2011-04-05' union all
select 11, '7964431', 0, '2011-04-05' union all
select 12, '4646546', 1, '2011-04-06'
--select * from t1select * from
(select [mobile] as [mobile1],COUNT(*) as sum1 from t1 where [status]=1 group by [mobile]) a
full join
(select COUNT(*) as sum2,[mobile] as [mobile2] from t1 where [status]=0 group by [mobile]) b
on a.mobile1=b.mobile2
insert into tb select 1,'1231321',1,'2011-04-02'
insert into tb select 2,'4646546',1,'2011-04-05'
insert into tb select 3,'4646546',0,'2011-04-05'
insert into tb select 4,'4679464',0,'2011-04-05'
insert into tb select 5,'4679464',0,'2011-04-06'
insert into tb select 6,'7896461',1,'2011-04-06'
insert into tb select 7,'1231321',0,'2011-04-06'
insert into tb select 8,'1231321',0,'2011-04-07'
insert into tb select 9,'7964431',0,'2011-04-06'
insert into tb select 10,'7964431',0,'2011-04-05'
insert into tb select 11,'7964431',0,'2011-04-05'
insert into tb select 12,'4646546',1,'2011-04-06'
go
select a.mobile,a.cnt,b.cnt,b.mobile from(
select mobile,count(*)cnt from tb where status=1 group by mobile
)a full join (
select mobile,count(*)cnt from tb where status=0 group by mobile
)b on a.mobile=b.mobile
go
drop table tb
/*
mobile cnt cnt mobile
----------- ----------- ----------- -----------
1231321 1 2 1231321
4646546 2 1 4646546
NULL NULL 2 4679464
7896461 1 NULL NULL
NULL NULL 3 7964431(5 行受影响)*/
insert into #tb select 1,'1231321',1,'2011-04-02'
insert into #tb select 2,'4646546',1,'2011-04-05'
insert into #tb select 3,'4646546',0,'2011-04-05'
insert into #tb select 4,'4679464',0,'2011-04-05'
insert into #tb select 5,'4679464',0,'2011-04-06'
insert into #tb select 6,'7896461',1,'2011-04-06'
insert into #tb select 7,'1231321',0,'2011-04-06'
insert into #tb select 8,'1231321',0,'2011-04-07'
insert into #tb select 9,'7964431',0,'2011-04-06'
insert into #tb select 10,'7964431',0,'2011-04-05'
insert into #tb select 11,'7964431',0,'2011-04-05'
insert into #tb select 12,'4646546',1,'2011-04-06'
go
--SQL:
SELECT
扣费成功的手机号 = A.mobile,
扣费成功条数 = a.cnt,
扣费失败的条数 = b.cnt,
扣费失败的手机号码 = b.mobile
FROM
(SELECT id = MAX(id),mobile, cnt=COUNT(1) FROM #tb WHERE [status] = 1 GROUP BY mobile) A
FULL JOIN
(SELECT id = MAX(id),mobile, cnt=COUNT(1) FROM #tb WHERE [status] = 0 GROUP BY mobile) B
ON A.mobile = B.mobile
/*
扣费成功的手机号 扣费成功条数 扣费失败的条数 扣费失败的手机号码
----------- ----------- ----------- -----------
1231321 1 2 1231321
4646546 2 1 4646546
NULL NULL 2 4679464
7896461 1 NULL NULL
NULL NULL 3 7964431
*/