如何分组并从特定行获取值 [英] How to group by and getting values from specific rows

问题描述

我有这个原始表格：

hi i have this origianl table:

type---startDate---endDate---eatingDate
  1-----2011--------2012-------1979
  1-----2012--------2013-------1980
  1-----2013--------2014-------
  2-----2014--------2015-------1982 
  3-----2015--------2016--------1983
  1-----2016--------2017-------1984
  1-----2017--------2018-------1985

我想要的结果：

the result i want :

type---startDate---endDate---eatingDate
  1-----2011--------2014--------
  2-----2014--------2015--------1982 
  3-----2015--------2016--------1983
  1-----2016--------2018--------1985

这是我要解决的一种情况，

另一种情况是在eatingDate的origianl表中第3行的值是1981年结果将是

this is one case that i want to solve,
anther case is when in the line 3 in the origianl table in the eatingDate the value is 1981 so the result will be

type--startDate---endDate---eatingDate
  1-----2011--------2014-------1981
  2-----2014--------2015-------1982 
  3-----2015--------2016-------1983
  1-----2016--------2018--------1985

注意：在未知的情况下具有相同类型的行数



i尝试一些事情但我不喜欢我不知道如何解决这个问题

i使用sql server



我尝试了什么：



i尝试一些东西，但我不知道如何解决这个问题

note : the number of rows that have the same type in unknown

i try some things but i don't have any idea how to solve this
i use sql server

What I have tried:

i try some things but i don't have any idea how to solve this

推荐答案

要了解GROUP请按照以下链接：

GROUP BY（Transact-SQL）| Microsoft Docs [ ^ ]

GROUP BY（Transact-SQL）| Microsoft Docs [ ^ ]



这就是我做的方式：

To learn about GROUP BY follow these links:
GROUP BY (Transact-SQL) | Microsoft Docs[^]
GROUP BY (Transact-SQL) | Microsoft Docs[^]

This is how I did it:

declare @table table (
	[type] int,
	startDate int,
	endDate int,
	eatingDate int )

insert into @table ([type],startDate,endDate,eatingDate) values
(1,2011,2012,1979),
(1,2012,2013,1980),
(1,2013,2014,nulll ),
(2,2014,2015,1982 ),
(3,2015,2016,1983)

select [type], min(startDate), max(endDate), max(eatingDate) from @table GROUP BY [type]

注意 [type] - 它是一个保留字，所以当你将它用作列名时需要括号

Note the square brackets around [type] - it is a reserved word so you need the brackets when you use it as a column name

它不好，因为它将在开头按1类型分组最后的1类型

its not good because it will group by the 1 type in the start and also the 1 type in the end

如果我正确理解你的问题，你可以使用分层查询来获取连续范围内的最小值和最大值，并按结果获取组。请考虑以下示例

If I understand your question correctly, you could use a hierarchical query to fetch the minimum and maximum in a continuous range and the group by the results. Consider the following example

WITH Hier (type, startdate, enddate, eatingdate) AS (
   SELECT t.type, t.startdate, t.enddate, t.eatingdate
   FROM TableName t
   WHERE NOT EXISTS (SELECT 1 
                     FROM TableName t2 
                     WHERE t2.enddate = t.startdate 
                     AND   t2.type = t.type)
   UNION ALL
   SELECT t.[type], h.startdate, t.enddate, t.eatingdate
   FROM hier      h, 
        TableName t
   WHERE t.startdate = h.enddate
   AND   t.type = h.type
) 
SELECT h.type, h.startdate, max(h.enddate), max(h.eatingdate)
FROM hier h
GROUP BY h.type, h.startdate
ORDER BY 2, 1

这篇关于如何分组并从特定行获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！