您的位置: 首页  >  技术问答  >  如何编写一个查询，选择需要的列，并从不同的表

如何编写一个查询，选择需要的列，并从不同的表

分类: 技术问答 • 2022-06-13 13:17:03
问题描述：

我在MVC3工作指望在这里，我需要一些帮助，请如何编写一个查询，选择需要的列，并从不同的表

这里我有一个像（错误ID）

错误ID标题描述专案编号版本的一些表BuildNumber EmployeId类别ID创建日期严重性ID PriorityID发行版ID类型ID

2银行业务应用程序1新版本新建版否1 1 00:00:00。00 1 1 1 1

（项目表）

================================= ======================

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电子商务这是融资项目活动

RealesePhase（表）

== ================================================== ================

ReleasePhaseID ReleasePhase 

1  DEV 
2  QA 
3  Alpha 
4  Beta

5直播

Tostatus（表）

=============================== ====================

ToStatusId  Tostatus 

1  New 
2  Assigned 
3  Fixed 
4  Re-Opened 
5  Closed 
6  Deffered

7不是错误

Bughistory（表）

BugHistoryID错误ID FixedByID AssignedTo分辨率FromStatus ToStatus

5     2   1   1  this is my banking  New   New 
7     2   1   1  this assignto res    km,l

========================================= ================================================== ===========

这里我有这些表现在我必须编写一个查询来选择ProjectName（下拉） 和（ReleasePhase）从它（open）（closed）（fixedBy）

在Bugs表中错误将从它的日志（插入），所以现在我们必须选择项目名称& ReleasePhase作为下拉，如果我们选择项目名称，我应该得到我们对它的错误数（计数）

从它像 （视图）谨此

================================ =====================================

项目名RealesePhase openBugs ClosedBugs fixedBy

    1   New   EmployeName 
       2   Assigned EmployeName 
       3   Fixed  EmployeName 
       4   Re-Opened EmployeName

=============================================== =======================

所以PLZ帮我写一个查询计数和显示Ë多少错误插入的员工，有多少被释放，有多少收

在此先感谢

答

你最好使用this..and存储过程，我认为你需要的bug数量，按您的状态表和Releasephase和ProjectId ..这是我看到你的问题希望它可以帮助你...

Create procedure [dbo].[ProjectReports] 
(
@ProjectID int, 
@ReleasePhaseID int 
) 
as 
begin 
select distinct projectName,ReleasePhase, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='New')) as Newbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Assigned')) as Assignedbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Fixed')) as Fixedbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Re-Opened')) as Reopenedbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Closed')) as Closedbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Deffered')) as Defferedbugs, 
(Select COUNT(1) from Bugs where ProjectId=a.ProjectId and ReleasePhaseID=a.ReleasePhaseID and 
bugid in (select BugID from BugHistory where [status]='Not a Bug')) as NotaBug 
from Bugs a 
inner join Projects p on p.ProjectId=a.ProjectId 
inner join ReleasePhase Rp on rp.ReleasePhaseID=a.ReleasePhaseID 
where [email protected] and [email protected] 
end

相关推荐