如何使用两个条件在sql server中选择？ [英] How can I do select in sql server using two conditions ?

问题描述

我有一个表ProductStatusHistory包含产品请求的历史记录。

 ID ID_Product ID_Request状态类别ID_Lab Created_ON 
 1 40 5 Clinic Requested Cat1 2 02-09-2015 08:00:00 
 2 40 5实验室请求Cat1 2 03-09-2015 08:00:00 
 3 40 5已开始Cat1 2 04-09-2015 08:00:00 
 4 40 5已完成Cat1 2 05-09-2015 08:00:00 
 5 41 5诊所申请Cat1 2 02-09-2015 08： 00:00 
 6 41 5实验室请求Cat1 2 03-09-2015 09:00:00 
 7 41 5开始Cat1 2 04-09-2015 09:00:00 
 8 41 5已取消Cat1 2 05-09-2015 09:00:00 

我需要选择产品每个请求的uct（一个请求（ID_Request）可以有一个或多个产品请求（ID_Product））与最后一个状态相关联。我需要它的输出应该是这样的：

 ID_Request ID_Product状态类别ID_Lab Created_ON 
 5 40完成Cat1 2 05-09-2015 08:00 ：00 
 5 41已取消Cat1 2 05-09-2015 09:00:00 

我试过这个查询：

 选择 IDProduct 
，SelectedProd 
 ，类别
，CreatedOn 
，状态
 来自 ProductStatusHistory 
 其中 ID_Req = 5 和 CreatedOn =（ select  max（CreatedOn）来自 ProductStatusHistory）

但它总是给我表中指示请求ID的最后一行。



ANY解决方案请

解决方案

SELECT P.ID，P.ID_Product，P.ID_Request，P.Status，P.Category，P.ID_Lab，P.Created_ON

来自产品P

INNER JOIN（

选择ID_Request，ID_Product，max（id）ID

来自产品WHERE ID_Request = ID_Request = 5组，ID_Product



）T.ID = P.ID上的T

实现这一目标的最佳方法是再创建一个类似SNo的列，对于新状态，您可以针对产品增加Sno，同时获取时间获得最大SNo的结果。

I have a table "ProductStatusHistory" witch contain the history of a Product request.

ID   ID_Product   ID_Request   Status            Category   ID_Lab   Created_ON
1     40           5         Clinic Requested   Cat1       2       02-09-2015 08:00:00
2     40           5         Lab Request        Cat1       2       03-09-2015 08:00:00
3     40           5         Started            Cat1       2       04-09-2015 08:00:00
4     40           5         Completed          Cat1       2       05-09-2015 08:00:00
5     41           5         Clinic Request     Cat1       2       02-09-2015 08:00:00
6     41           5         Lab Request        Cat1       2       03-09-2015 09:00:00
7     41           5         Started            Cat1       2       04-09-2015 09:00:00
8     41           5         Cancelled          Cat1       2       05-09-2015 09:00:00

I need to select the product for each request (a Request (ID_Request) can have 1 or more product request (ID_Product)) with the last status associated. The output that I need it should be like this:

ID_Request    ID_Product    Status       Category     ID_Lab      Created_ON   
5               40          Completed      Cat1         2       05-09-2015 08:00:00
5               41          Cancelled      Cat1         2       05-09-2015 09:00:00

I have tried this query:

select   IDProduct 
      ,  SelectedProd
      ,  Category
      ,  CreatedOn
      ,  Status
from ProductStatusHistory
where ID_Req=5 and CreatedOn=(select  max(CreatedOn) from ProductStatusHistory) 

But it gives me always the last row in the table for the indicated request ID.

ANY SOLUTION PLEASE ?

解决方案

SELECT P.ID , P.ID_Product , P.ID_Request , P.Status , P.Category, P.ID_Lab , P.Created_ON
FROM PRODUCT P
INNER JOIN (
select ID_Request, ID_Product, max(id) ID
from product WHERE ID_Request = 5 group by ID_Request, ID_Product

) T on T.ID = P.ID

Hi,

The Best Approach to achieve this is create one more column like SNo, and for new status you can increment the Sno against product and while fetching time get result with max SNo.

这篇关于如何使用两个条件在sql server中选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！