22,209
社区成员
发帖
与我相关
我的任务
分享
[Quote=引用 6 楼 zsh0809 的回复:]
修正6#
[/Quote]
SELECT P.projectname,总数=([0]+[1]+[2]),([0]) 未完成,([1]) 已完成,([2])过期
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.projectid
projectname 总数 未完成 已完成 过期
----------- ----------- ----------- ----------- -----------
A task 6 2 3 1
B task 8 2 3 3
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
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.taskid
group by m.projectname
CREATE TABLE #project
(
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,1001
INSERT 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,1002
SELECT 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.projectid
projectname 总数 未完成 已完成 过期
----------- ----------- ----------- ----------- -----------
A task 13 2 5 6
B task 36 14 9 13
(2 row(s) affected)
SELECT A.projectname,[总任务数]=COUNT(B.taskid),
[未完成任务数]=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
select 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
select projectname,count(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;
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