从一条记录中拆分数据是一个特定的列T-SQL
我正在处理从Oracle导入SQL Server 2012的旧旧数据库。我有下表INSOrders
,其中包括varchar(8)
类型的名为OrderID
的列。从一条记录中拆分数据是一个特定的列T-SQL
插入的数据的一个例子是:
A04-05 | B81-02 | C02-01
A01-01 | B95-01 | C99-05
A02-02 | B06-07 | C03-02
A98-06 | B10-01 | C17-01
A78-07 | B02-03 | C15-03
A79-01 | B02-01 | C78-06
首字母=订单类型,接下来的2位=年 - 和最后2位= ORDERNUM该年份内。
所以我所有的数据拆分成3列:(不存储,只需提交)
select
orderid,
substring(orderid, 0, patindex('%[0-9]%', orderid)) as ordtype,
right(max(datepart(yyyy, '01/01/' + substring(orderid, patindex('%[0-9]-%', orderid) - 1, 2))),2) as year,
max(substring(orderid, patindex('%-[0-9]%', orderid) + 1, 2)) as ordnum
from
ins.insorders
where
orderid is not null
group by
substring(orderid, 0, patindex('%[0-9]%', orderid)), orderid
order by
ordtype
它看起来像这样:
OrderID | OrderType | OrderYear | OrderNum
---------+-------------+-------------+----------
A04-05 | A | 04 | 05
A01-01 | A | 01 | 01
B10-03 | B | 10 | 03
B95-01 | B | 95 | 01
etc....
但现在我只是想选择最大对于所有OrderType
:只显示字母A的最大值,显示字母B的最大值等。我的意思是最大值,我的意思是来自AI的信件需要显示最新的年份和最新的订单号。所以如果我有A04-01和A04-02只是显示A04-02。
我需要修改我的查询中,我可以看到以下内容:
OrderID | OrderType | OrderYear | OrderNum
---------+-------------+-------------+----------
A04-05 | A | 04 | 05
B10-03 | B | 10 | 03
C17-01 | C | 17 | 01
谢谢你,我会真正体会到了帮助。
你可以试试下面。根据订单年份和订单号,将原始查询用作cte并将行号分配给每组订单类型。然后获取所有行号1,它应该是每个订单类型的最大值。
这一点DATEPART(yyyy,('01/01/' + OrderYear))
将确保我们得到正确的一年,使95 1995年和10是在2010年等
;WITH cte
AS (
select orderid,
substring(orderid, 0, patindex('%[0-9]%', orderid)) as ordtype,
right(max(datepart(yyyy,'01/01/' + substring(orderid, patindex('%[0-9]-%', orderid) - 1, 2))),2) as year,
max(substring(orderid, patindex('%-[0-9]%', orderid) + 1, 2)) as ordnum
from ins.insorders
where orderid is not null
group by substring(orderid, 0, patindex('%[0-9]%', orderid)), orderid
)
SELECT *
FROM
(SELECT
*
, ROW_NUMBER() OVER (PARTITION BY OrderType ORDER BY DATEPART(yyyy,('01/01/' + OrderYear)) DESC, OrderNum DESC) AS RowNum
FROM cte) t
WHERE t.RowNum = 1
的数据不佳代表,我只有一个办法“奶酪”它,我们需要做很多假设:
with cte_example
as
(your query)
select OrderID
,OrderType
,OrderYear
,OrderNum
from
(select *, row_number() over(partition by OrderType order by OrderYear DESC) rn
from cte_example
where OrderYear <= right(year(getdate()),2)) t1
where t1.rn = 1
既然你已经有一个查询提取信息我不会打扰改变它。我们总结您的查询的CTE,从中查询和应用row_number
功能来决定哪些OrderType
具有最新的OrderYear
,其OrderNum
和OrderID
现在棘手的问题是,这些年来表示不好(假设沿着我评论你原来的帖子是真实的),然后使用OrderType
B
的任何类型的聚合将返回95
,因为它是数值最大的。
我们假设没有订单日期会大于这个当年,而更大的是在90年代,使用以下语句:where OrderYear < right(year(getdate()),2)
。换句话说,得到今年和它的两个正确的人物。首先从getdate
检索2017
,然后17
与RIGHT
函数。我相信你为什么会看到这是危险的,因为如果你最近的日期是1999年呢?
所以通过筛选出来,我们可以看到每个OrderType
的最新年份...希望这有助于。
Here是我建立的rextester测试,可以在您想要尝试的情况下与您的查询一起玩。
我想你的原始查询几乎正是你所需要的只是你需要使用MAX(OrderID)而不是按它分组。
declare @Something table
(
orderid varchar(6)
)
insert @Something
(
orderid
) values
('A04-05'), ('B81-02'), ('C02-01'),
('A01-01'), ('B95-01'), ('C99-05'),
('A02-02'), ('B06-07'), ('C03-02'),
('A98-06'), ('B10-01'), ('C17-01'),
('A78-07'), ('B02-03'), ('C15-03'),
('A79-01'), ('B02-01'), ('C78-06')
select max(orderid),
substring(orderid, 0, patindex('%[0-9]%', orderid)) as ordtype,
right(max(datepart(yyyy,'01/01/' + substring(orderid, patindex('%[0-9]-%', orderid) - 1, 2))),2) as year,
max(substring(orderid, patindex('%-[0-9]%', orderid) + 1, 2)) as ordnum
from myTable
where orderid is not null
group by substring(orderid, 0, patindex('%[0-9]%', orderid))
order by ordtype
这是一种奇怪的,所以我想在''B10-03'显示OrderYear''10',而不是'95'因为'10'表示为'2010'和'95'表示为'1995?' – Simon
这不就像使用MAX(orderid)和从组中删除orderid一样简单吗? –