有2个表表1:项目表project
字段:projectid,projectname表2:任务表task
字段:taskid,state(完成状态:未完成0;已完成1;过期2),projectid要求:得到所有项目名称、该项目下的总任务数、未完成任务数、已完成任务数、过期任务数
谢谢高手!!!
字段:projectid,projectname表2:任务表task
字段:taskid,state(完成状态:未完成0;已完成1;过期2),projectid要求:得到所有项目名称、该项目下的总任务数、未完成任务数、已完成任务数、过期任务数
谢谢高手!!!
select projectname,sum(taskid) as [总任务数],
sum(case when state = 0 then 1 else 0 end) as [未完成],
sum(case when state = 1 then 1 else 0 end) as [已完成],
sum(case when state = 2 then 1 else 0 end) as [已过期]
from project a left join task b on a.id = b.projectid
group by projectname
select
(select projectname from project where t.projectid=projectid) [项目名],
count(state) [总任务数],
sum(case when state=0 then 1 else 0 end) [未完成任务数],
sum(case when state=1 then 1 else 0 end) [已完成任务数],
sum(case when state=2 then 1 else 0 end) [过期任务数]
from task
group by projectid;
sum(case when state = 0 then 1 else 0 end) as [未完成],
sum(case when state = 1 then 1 else 0 end) as [已完成],
sum(case when state = 2 then 1 else 0 end) as [已过期]
from project a left join task b on a.id = b.projectid
group by projectname
,count(1) as '总任务数'
,sum(case when state=0 then 1 else 0 end) as '未完成任务数'
,sum(case when state=1 then 1 else 0 end) as '已完成任务数'
,sum(case when state=2 then 1 else 0 end) as '过期任务数'
From [project] inner join [task] on [project].projectid=[task].projectid
group by projectname
[未完成任务数]=SUM( CASE WHEN B.state = 0 then 1 else 0 end),
[已完成任务数]=SUM( CASE WHEN B.state = 1 then 1 else 0 end),
[过期任务数]=SUM( CASE WHEN B.state = 2 then 1 else 0 end)
FROM project A LEFT JOIN task B ON A.projectid=B.projectid
GROUP BY A.projectname
(
projectid INT,
projectname VARCHAR(10)
)
---DROP TABLE #TASK
CREATE TABLE #task
(
taskid INT IDENTITY,
[state] INT,
projectid INT
)INSERT INTO #project SELECT 1001,'A task'
INSERT INTO #project SELECT 1002,'B task'INSERT INTO #task SELECT 0,1001
INSERT INTO #task SELECT 0,1001
INSERT INTO #task SELECT 1,1001
INSERT INTO #task SELECT 1,1001
INSERT INTO #task SELECT 1,1001
INSERT INTO #task SELECT 2,1001INSERT INTO #task SELECT 1,1002
INSERT INTO #task SELECT 1,1002
INSERT INTO #task SELECT 1,1002
INSERT INTO #task SELECT 0,1002
INSERT INTO #task SELECT 2,1002
INSERT INTO #task SELECT 2,1002
INSERT INTO #task SELECT 2,1002
INSERT INTO #task SELECT 0,1002SELECT P.projectname,总数=([0]+[1]+[2]),[0] 未完成,[1] 已完成,[2]过期
FROM #project p
LEFT JOIN
(SELECT PROJECTID,[0],[1],[2] FROM #task T
PIVOT(max(taskid) FOR [state] IN([0],[1],[2]))pvt
)m
ON p.projectid=m.projectidprojectname 总数 未完成 已完成 过期
----------- ----------- ----------- ----------- -----------
A task 13 2 5 6
B task 36 14 9 13(2 row(s) affected)
count(1) 总任务数,
sum(case n.state when 0 then 1 else 0 end) 未完成任务数,
sum(case n.state when 1 then 1 else 0 end) 已完成任务数,
sum(case n.state when 2 then 1 else 0 end) 过期任务数
from project m, task n
where m.projectid = n.taskid
group by m.projectname
select m.projectname ,
count(1) 总任务数
sum(case n.state when 0 then 1 else 0 end) 未完成任务数,
sum(case n.state when 1 then 1 else 0 end) 已完成任务数,
sum(case n.state when 2 then 1 else 0 end) 过期任务数
from project m, task n
where m.projectid = n.projectid
group by m.projectname
FROM #project p
INNER JOIN
(
SELECT PROJECTID,COUNT([0])[0],COUNT([1])[1],COUNT([2])[2]
FROM #task T
PIVOT(max([state]) FOR [state] IN([0],[1],[2]))pvt
GROUP BY PROJECTID
)m
ON p.projectid=m.projectidprojectname 总数 未完成 已完成 过期
----------- ----------- ----------- ----------- -----------
A task 6 2 3 1
B task 8 2 3 3