sql查询语句

显示全部楼层 · 2007-10-20 08:38:44

现有表table1
id
uid isarrived
1
001
签到
2
002
签到
3
001
未到
4
001
签到
5
002
未到
我想得到如下结果
uid
uid出现次数
未到次数
001
3
1
002
2
1

请问以上输出结果。用sql如何实现
附：建立table1的sql如下：
create table table1
(
id varchar(100) not null,
uid varchar(100) null,
isarrived varchar(50) null
)

千问 · 2007-10-20 08:38:44

select uid,uid出现次数=count(*),未到次数=sum(case when isarrived = '未到' then 1 else 0 end)
from table1
group by uid

千问 · 2007-10-20 08:38:44

select A.uid,count(A.uid) as 'uid出现次数' ,
(selectcount(isarrived)-1 from table1 b
where isarrived='未到' group by isarrived ) AS '未到次数' from table1 A
group by A.uid