如何计算和选择不同表中的所需列 [英] how count and select required columns from different tables
问题描述
我正在mvc3中工作，我需要一些帮助
这里我有一些像(BugId)
的表
================================================== ================================================== =========
BugID标题说明ProjectId版本BuildNumber EmployeId类别ID CreatedDate严重性ID PriorityID ReleasePhaseID TypeID
2银行业务银行业务应用程序1新版本新版本号1 1 00:00:00.00 1 1 1 1
================================================== ================================================== ================================================== ====
(项目表)
================================================== =====
Projectid ProjectName说明状态
1财务这是财务项目，活动中
2 Uniformatic，这是金融项目活动中
3产品这是财务项目处于活动状态
4 Cloud这是金融项目INActive
5银行业务，这是金融项目的进展
6电子商务这是活跃的金融项目
================================================== ==================
RealesePhase(表格)
================================================== ==================
ReleasePhaseID ReleasePhase
1个DEV
2个质量检查
3 Alpha
4 Beta
5 Live
====================================== <= >
Tostatus(表格)
================================================== =
ToStatusId Tostatus
1个新
2已分配
3固定
4重新打开
5封闭
6递减
7不是错误
================================================== ===
错误历史记录(表)
================================================== ===============================
BugHistoryID BugID已固定由ID分配给分辨率FromStatus ToStatus
5 2 1 1这是我的银行业务New New
7 2 1 1此分配结果res km，l
================================================== ================================================== ==
这里我有这些表，现在我必须写一个查询来选择ProjectName(dropdown)
并且(ReleasePhase)从它(打开)(关闭)(fixedBy)
在Bugs表中，错误将从中记录(插入)，因此如果我们选择项目名称，现在我们必须选择Project Name&ReleasePhase作为下拉列表，我应该得到我们拥有的Bug数量(计数)
就像
(查看)应该这样
================================================== ===================
项目名称RealesePhase openBugs ClosedBugs固定By
1个新的EmployeName
2分配的EmployeName
3固定的EmployeName
4重新打开EmployeName
================================================== ===================
因此plz可以帮助我编写查询计数，并显示Employee插入了多少个bug，释放了多少个bug，以及关闭了几个
在此先感谢
i am working in mvc3 here i need some help please
here i have some tables like(BugId)
=============================================================================================================
BugID Title Description ProjectId Version BuildNumber EmployeId CategoryID CreatedDate SeverityID PriorityID ReleasePhaseID TypeID
2 Banking bankingapplication 1 new version new build no 1 1 00:00:00.00 1 1 1 1
==========================================================================================================================================================
(Projects Table)
=======================================================
Projectid ProjectName Description Status
1 Finance This is Finance Project Active
2 Uniformatic This is Finance Project Active
3 ProDuct This is Finance Project InActive
4 Cloud This is Finance Project INActive
5 Banking This is Finance Project Progress
6 Ecommerce This is Finance Project Active
====================================================================
RealesePhase (table)
====================================================================
ReleasePhaseID ReleasePhase
1 DEV
2 QA
3 Alpha
4 Beta
5 Live
===============================================
Tostatus(table)
===================================================
ToStatusId Tostatus
1 New
2 Assigned
3 Fixed
4 Re-Opened
5 Closed
6 Deffered
7 Not a Bug
=====================================================
Bughistory (Table)
==================================================================================
BugHistoryID BugID FixedByID AssignedTo Resolution FromStatus ToStatus
5 2 1 1 this is my banking New New
7 2 1 1 this assignto res km,l
======================================================================================================
here i have these tables now i have to write a query to select ProjectName(dropdown)
And (ReleasePhase) from it (open)(closed)(fixedBy)
in Bugs table Bugs will be log (insert) from it so now we have to select Project Name & ReleasePhase as dropdown if we select the project name i should get the No of Bugs we have for it (counting)
from it like
(view) should like this
=====================================================================
ProjectName RealesePhase openBugs ClosedBugs fixedBy
1 New EmployeName
2 Assigned EmployeName
3 Fixed EmployeName
4 Re-Opened EmployeName
======================================================================
so plz help me to write a query count and show the how many bugs Employee inserted and how many are released and how many closed
Thanks In Advance
推荐答案
我知道您用来识别打开的bug和关闭的bug的逻辑是什么.也不确定您将如何解决.当您要计算打开/关闭的bug数时，就无法按列进行修复..
我提供了示例，以获取项目名称，发布阶段和已关闭的错误计数.
I know what is the logic you will used to identify open bugs and closed bugs. also am not sure how you will get fixed by. When you are getting count of open/closed bugs, its not possible to get fixed by column..
I have provided sample, to get projectname, releasephase and closed bugs count.
<pre lang="sql">select distinct projectname,releasephase,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Closed')) as Closedbugs
from bug a
inner join projects p on p.projectid=a.projectid
inner join ReleasePhase Rp on rp.releasephaseid=a.releasephaseid
select distinct projectname,releasephase,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='New')) as NewBugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Assigned')) as Assignedbugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Fixed')) as FixedBugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Re-Opened')) as ReopenedBugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Closed')) as Closedbugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Deffered')) as Deferredbugs,
(Select COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Not a Bug')) as NotBugs
from bug a
inner join projects p on p.projectid=a.projectid
inner join ReleasePhase Rp on rp.releasephaseid=a.releasephaseid
这篇关于如何计算和选择不同表中的所需列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
