if object_id(N'Temptable') is not null
drop table Temptableselect id,name,null as countarrive,null as countunarrive,null as countlate into temptable
from tablename where unarrive='yes'update temptable
set b.countunarrive=a.sum(countarrive),b.countunarrive=a.sum(countunarrive )
,b.countlate=a.sum(countlate)
from temptable b,tablename a
where a.id=b.id group by a.idselect * from temptable
drop table Temptableselect id,name,null as countarrive,null as countunarrive,null as countlate into temptable
from tablename where unarrive='yes'update temptable
set b.countunarrive=a.sum(countarrive),b.countunarrive=a.sum(countunarrive )
,b.countlate=a.sum(countlate)
from temptable b,tablename a
where a.id=b.id group by a.idselect * from temptable
a.id,a.name,
countarrive = sum(case arrive='yes' or late='yes' then 1 else 0 end),
countunarrive = sum(case unarrive when 'yes' then 1 else 0 end),
countlate = sum(case late when 'yes' then 1 else 0 end)
from
表 a
where
a.name in (select name from 表 where time = '20050531' and unarrive = 'yes')
group by
a.id,a.name
order by
a.id
time varchar(8) , name varchar(4) , id varchar(4) , arrive varchar(3) , unarrive varchar(3) , late varchar(3))go
insert into test select '20050101', 'aaaa', '0001', 'yes', 'no', 'no' union all
select '20050102', 'aaaa', '0001', 'no', 'yes', 'no' union all
select '20050103', 'aaaa', '0001', 'no', 'no', 'no' union all
select '20050104', 'aaaa', '0001', 'yes', 'no', 'no' union all
select '20050531', 'aaaa', '0001', 'no', 'yes', 'no' union all
select '20050101', 'bbbb', '0002', 'no', 'yes', 'no' go
select id,name
,sum(case arrive when 'yes' then 1 else 0 end ) as countarrive
,sum(case unarrive when 'yes' then 1 else 0 end ) as countunarrive
,sum(case late when 'yes' then 1 else 0 end ) as countlate
from test
group by id,name
go
drop table test
go
select id,name
,sum(case arrive when 'yes' then 1 else 0 end ) as countarrive
,sum(case unarrive when 'yes' then 1 else 0 end ) as countunarrive
,sum(case late when 'yes' then 1 else 0 end ) as countlate
from test
where time ='20050101'
group by id,name
a.id,
a.name,
countarrive = sum(case arrive='yes' or late='yes' then 1 else 0 end),
countunarrive = sum(case unarrive when 'yes' then 1 else 0 end),
countlate = sum(case late when 'yes' then 1 else 0 end)
from
表 a,
表 b
where
a.name = b.name
and
b.time = '20050531'
and
b.unarrive = 'yes'
group by
a.id,a.name
order by
a.id
set b.countunarrive=a.sum(countarrive),b.countunarrive=a.sum(countunarrive )
,b.countlate=a.sum(countlate)
from temptable b,tablename a
where a.id=b.id group by a.id这个没有明白过来
好象有点问题啊
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: '=' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 10
在关键字 'group' 附近有语法错误。
好象是获取的是20050531这一天的已到、未到、迟到次数
SUM(CASE unarrive WHEN 'yes' THEN 1 ELSE 0 END) AS countunarrive,
SUM(CASE late WHEN 'yes' THEN 1 ELSE 0 END) AS countlate
FROM YOURTABLENAME INNER JOIN
(SELECT ID AS ID1
FROM YOURTABLENAME
WHERE TIME = '20050531' AND UNARRIVE = 'YES') NEWTABLE ON
YOURTABLENAME.ID = NEWTABLE.ID1
GROUP BY ID, NAME
Select
ID,
Name,
SUM(Case When arrive='yes' Or late='yes' Then 1 Else 0 End ) As countarrive,
SUM(case unarrive When 'yes' Then 1 Else 0 End ) As countunarrive,
SUM(case late When 'yes' Then 1 Else 0 End ) As countlate
From TEST
Where ID In (Select ID from TEST Where Time='20050531' And unarrive = 'yes')
Group By ID,Name
Create table TEST
(Time Varchar(8),
Name Varchar(4),
ID Varchar(4),
arrive Varchar(3),
unarrive Varchar(3),
late Varchar(3))
GO
--插入数据
Insert Into TEST
Select '20050101', 'aaaa', '0001', 'yes', 'no', 'no' union all
Select '20050102', 'aaaa', '0001', 'no', 'yes', 'yes' union all
Select '20050103', 'aaaa', '0001', 'no', 'no', 'no' union all
Select '20050104', 'aaaa', '0001', 'yes', 'no', 'no' union all
Select '20050531', 'aaaa', '0001', 'no', 'yes', 'no' union all
Select '20050101', 'bbbb', '0002', 'no', 'yes', 'no' union all
Select '20050102', 'bbbb', '0002', 'no', 'no', 'yes' union all
Select '20050103', 'bbbb', '0002', 'yes', 'no', 'no' union all
Select '20050104', 'bbbb', '0002', 'yes', 'no', 'no' union all
Select '20050531', 'bbbb', '0002', 'yes', 'no', 'no' union all
Select '20050101', 'cccc', '0003', 'yes', 'no', 'no' union all
Select '20050102', 'cccc', '0003', 'yes', 'no', 'no' union all
Select '20050103', 'cccc', '0003', 'yes', 'no', 'no' union all
Select '20050104', 'cccc', '0003', 'no', 'yes', 'no' union all
Select '20050531', 'cccc', '0003', 'no', 'yes', 'no' union all
Select '20050101', 'dddd', '0004', 'no', 'no', 'yes' union all
Select '20050102', 'dddd', '0004', 'no', 'no', 'yes' union all
Select '20050103', 'dddd', '0004', 'no', 'no', 'yes' union all
Select '20050104', 'dddd', '0004', 'no', 'no', 'yes' union all
Select '20050531', 'dddd', '0004', 'no', 'yes', 'no'
GO
--测试
Select
ID,
Name,
SUM(Case When arrive='yes' Or late='yes' Then 1 Else 0 End ) As countarrive,
SUM(case unarrive When 'yes' Then 1 Else 0 End ) As countunarrive,
SUM(case late When 'yes' Then 1 Else 0 End ) As countlate
From TEST
Where ID In (Select ID from TEST Where Time='20050531' And unarrive = 'yes')
Group By ID,Name
--删除测试环境
Drop table TEST
--结果
/*
ID Name countarrive countunarrive countlate
0001 aaaa 3 2 1
0003 cccc 3 2 0
0004 dddd 4 1 4
*/