【■■▲●★】这样的统计SQL语句该怎么写？？？？？？？？

2003-09-11 09:48:56 69 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

本地部署 Bolt.new - 小学生都能轻松搭建AI编程环境本地部署 Bolt.new - 小学生都能轻松搭建AI编程环境阅读详情

T1: classID clasName
0001 01计网
0002 01计软
.....................

T2 : classID,studentID,studentName
0001 200001 王刚
0002 200002 李华
.....................

T3: studentID nWeek nState (其中nstate值为（0,1,2,3）四种情况)
200001 1 0
200001 2 1
200001 1 1
200001 2 2
200001 2 3
200002 3 0
200002 1 3
.....................

现在需要统计生成一张表为
classname studentName nState(0) nState(1) nState(2) nState(3)

【输入的条件为nWeek,例如：nWeek >= 1 And nWeek <=5】

现在需要统计在某一段时间内，所有人的nState四种状态的分别总和。
例如：

classname studentName nState(0) nState(1) nState(2) nState(3)
01计网王刚 1 2 1 1
01计软李华 1 0 0 1

这样的SQL的语句该怎么写？？？？

...全文

从数据集到产品：聊聊Room-to-Room (R2R) 如何影响扫地机器人、AR导航的未来本文探讨了Room-to-Room (R2R) 数据集如何推动视觉与语言导航（VLN）技术的发展，并重塑扫地机器人和AR导航的未来。通过分析R2R基于Matterport3D的设计及其在真实场景中的应用，揭示了其在提升智能导航精度和自然语言交互能力方面的潜力。文章还讨论了从实验室到产品化的关键技术挑战与解决方案。阅读详情

surfl

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wzh1215 2003-09-11

打赏
举报

Select T1.classname,T2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as 'nState(0)',
sum(case when T3.nState = 1 then 1 else 0 end) as 'nState(1)',
sum(case when T3.nState = 2 then 1 else 0 end) as 'nState(2)',
sum(case when T3.nState = 3 then 1 else 0 end) as 'nState(3)'
from T1 join T2 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
WHERE nWeek >= 1 And nWeek <=5
group by T1.classname,T2.studentName

pengdali 2003-09-11

打赏
举报

select t1.clasName,t2.studentName
,sum(case when nstate=0 then nWeek else 0 end) [nState(0)]
,sum(case when nstate=1 then nWeek else 0 end) [nState(1)]
,sum(case when nstate=2 then nWeek else 0 end) [nState(2)]
,sum(case when nstate=3 then nWeek else 0 end) [nState(3)]
from t3 join t2 on t2.studentID=t3.studentID join t1 on t2.classID=t1.classID
group by t1.classID,t1.classname,t2.studentID,t2.studentName

CrazyFor 2003-09-11

打赏
举报

select studentID
,case when nState=0 then nweek end nState(0)
,case when nState=1 then nweek end nState(1)
,case when nState=2 then nweek end nState(2)
,case when nState=3 then nweek end nState(3)
from T3
group by studentID

这样以后应该没有问题了吧.:)

zjcxc 元老 2003-09-11

打赏
举报

select t1.classname,t2.studentname,
[nState(0)]=sum(case nstate when 0 then 1 end),
[nState(1)]=sum(case nstate when 1 then 1 end),
[nState(2)]=sum(case nstate when 2 then 1 end),
[nState(3)]=sum(case nstate when 3 then 1 end)
from t2
inner join t1 on t2.classid=t1.classid
inner join t3 on t2.studentid=t3.studentid
where t3.nweek between 1 and 5
group by t1.classname,t2.studentname

伍子V5 2003-09-11

打赏
举报

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as nState(0),
sum(case when T3.nState = 1 then 1 else 0 end) as nState(1),
sum(case when T3.nState = 2 then 1 else 0 end) as nState(2),
sum(case when T3.nState = 3 then 1 else 0 end) as nState(3)
from T1,T2,T3 where T2.classID = T1.classID and T2.studentID = T3.studentID and nWeek >= 1 And nWeek <=5
group by T1.classname,T2.studentName

CCEO 2003-09-11

打赏
举报

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as 'nState(0)',
sum(case when T3.nState = 1 then 1 else 0 end) as 'nState(1)',
sum(case when T3.nState = 2 then 1 else 0 end) as 'nState(2)',
sum(case when T3.nState = 3 then 1 else 0 end) as 'nState(3)'
from T2 join T1 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
WHERE T3.nWeek >= 1 And T3.nWeek <=5
group by T1.classname,t2.studentName

tiny_yan 2003-09-11

打赏
举报

txlicenhe 2003-09-11

打赏
举报

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as nState(0),
sum(case when T3.nState = 1 then 1 else 0 end) as nState(1),
sum(case when T3.nState = 2 then 1 else 0 end) as nState(2),
sum(case when T3.nState = 3 then 1 else 0 end) as nState(3)
from T2 join T1 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
group by T1.classname,t2.studentName

【■■▲●★】这样的统计SQL语句该怎么写？？？？？？？？

相关推荐