SQL查询帮助以获取相同的端口详细信息 [英] Sql Query Help to get the same port details

问题描述

我有一个具有以下结构的表

I have a table with the following structure

   id   portid     Daynumber
1   334     1
1   335     2
1   337     3
1   334     4
2   333     1
2   338     2
2   333     3
3   333     1
3   338     2
3   333     3
4   456     1
4   789     2
4   700     3
5   456     1
5   789     2

我需要得到结果，因为planid的第一天和plan ids端口的最后一天是相同的

I need to get the result as where first day of the planid and the last day of the plan ids port is same

id      portid          Daynumber
1   334     1
2   333     1
3   333     1

通过SQl Query

Help me through SQl Query

推荐答案

with minValue as
(
 select id, portid from tbl1 where daynumber = 1
),
maxValue as
(
 select id, portid, daynumber from tbl1 where daynumber = (select max(daynumber) from tbl1 t where tbl1.id = t.id) 
)

select t1.id, t1.portid, t2.daynumber from minValue t1 inner join maxvalue t2 on t1.id = t2.id and t1.portid = t2.portid

帮助我
那应该工作.它获取最小值和最大值，并且仅返回第一天和最后一天的端口ID相同的情况.

That should work. It gets the minimum and maximum values and only returns situations where the port id is the same on the first and last day.

请看一下.

Please take a look.

SELECT * FROM dbo.tabPlan a WITH (NOLOCK)
INNER JOIN (SELECT MIN(Daynumber) as Daynumber ,id FROM dbo.tabPlan WITH (NOLOCK)
            GROUP BY ID ) AS b
            ON a.id = b.id AND a.Daynumber = b.Daynumber

我无法理解这个问题.我看不到要使用这三个值的任何逻辑，这似乎也与您所说的相同. right(cast(portid as varchar(5)，1)将给您最后一位数字，但在您的示例数据中，我看不到日期与日期的匹配方式.

I can''t make sense of this question. I can''t see any logic by which those three values are all you want, nor does this seem to be the same as what you''re saying. right(cast (portid as varchar(5)), 1) will give you the last digit, but I don''t see how the day number is matching that, in your sample data.

这篇关于SQL查询帮助以获取相同的端口详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！