如何编写查询以一次从两个表中选择所需的列 [英] How to write a query to select required columns from two tables at a time

问题描述

我正在使用mvc3.
我有两个表Bugs和Bughistory.
我需要选择值，并需要在前端显示为标签，请帮助编写查询.

看看这是我的表格及其字段

Hi,


I am working with mvc3.
I have two tables Bugs and Bughistory.
I need to select the values and need to show as lables in front end, plz help to write a query.

Look this are my tables and their fields

1. Bugs(
BugId int identity primary key,Title varchar(50),
Description varchar(max),
ProjectId int foreign key (ProjectId) references Projects(ProjectId),
Version varchar(50),
BuildNumber varchar(50),
CreatedByID int,
Category varchar(50),
CreatedDate date,
Severity varchar(50),
Priority varchar(50),
ReleasePhase varchar(50),
Type varchar(50))

2. BugHistory(
BugHistoryID int identity primary key,
BugId int foreign key (BugId) references Bugs(BugId),
FixedByID int,
AssignedToID int,
Resolution varchar(50),
FromStatus varchar(50),
ToStatus varchar(50))

首先，我在表格中插入了必要的详细信息.

因此，我有这两个表和一些数据.
现在我需要选择一些值，例如

First I inserted in the tables with necessary details.

So, I have these two tables with some data.
Now I need to select some values like

(BugId ,Description ,Version ,BuildNumber ,Category,CreatedDate,Severity,Priority,ReleasePhase ) from Bugs

和

and

(AssignedToID ,Resolution ,ToStatus ) from Bughistory 

现在，我需要显示这些详细信息(在前端应用程序中查看)作为标签

如何选择这些(查看)这些值，请帮助我.
预先谢谢您

Now I need show these details (view in front end application) as labels

How to select these (View) these values, plz help me.
Thank you in advance

推荐答案

您可以使用简单的INNER JOIN同时从两个表中选择值:

You can select the values from both tables at the same time using a simple INNER JOIN:

SELECT a.BugId,
      a.Description,
      a.Version,
      a.BuildNumber,
      a.Category,
      a.CreatedDate,
      a.Severity,
      a.Priority,
      a.ReleasePhase,
      b.AssignedToID,
      b.Resolution,
      b.ToStatus
FROM Bugs a
INNER JOIN Bughistory b ON a.BugId = b.BugId;

另外，您也可以使用视图永久拥有这些视图:

Also alternately, you can use a view to have these permanently:

CREATE VIEW buginfofull AS
SELECT a.BugId,
      a.Description,
      a.Version,
      a.BuildNumber,
      a.Category,
      a.CreatedDate,
      a.Severity,
      a.Priority,
      a.ReleasePhase,
      b.AssignedToID,
      b.Resolution,
      b.ToStatus
FROM Bugs a
INNER JOIN Bughistory b ON a.BugId = b.BugId;

然后使用以下命令从中进行选择:

And then select from it using:

SELECT BugId, Description, Resolution, ToStatus FROM buginfofull

至于标签(我假设您在这里指的是表格列)，您可以这样操作:

As for the labels (I am assuming you are referring to table columns here), you can do it as such:

SELECT a.BugId as id,
      a.Description as desc,
      a.Version as version,
      a.BuildNumber as buildnumber,
      a.Category as category,
      a.CreatedDate as createddate,
[...And so on]

然后，在结果集中，将具有上面定义的列标签.

And then, in the result set, you''ll have the column labels as defined above.

这篇关于如何编写查询以一次从两个表中选择所需的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！