declare @rowCount int = 5;
with cte as (
select *,( (IN_InvoiceID-1) / @rowCount ) % 2 group1
,( (IN_InvoiceID-1) / @rowCount ) group2
,IN_InvoiceID % @rowCount group3
from T
)
select * from cte
select T1.INID,T1.IN_InvoiceID,T1.IN_InvoiceAmount,T2.INID,T2.IN_InvoiceID,T2.IN_InvoiceAmount
from CTE T1
left join CTE T2 on T2.group1 = 1 and T1.group2 = T2.group2-1 and T1.group3 = T2.group3
where T1.group1 = 0
Declare @table table(id int identity, invoice_id int)
; WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 50
)
insert into @table SELECT n
FROM Numbers
Select (a.id )%5 ,* from @table a join @table b on a.id+5 = b.id and a.id != b.id
;WITH Numbers AS
(
SELECT n = 1, o = 5
UNION ALL
SELECT n + 10, o = o+10
FROM Numbers
WHERE n+1 <= 50
)
select a.id ParentId,a.invoice_id ParentInvoiceId, --b.n, b.o,
c.invoice_id childInvoiceID from @table a
join Numbers b on a.id between b.n and b.o
left join @table c on a.id + 5 = c.id
create table Invoicetable(IN_ID varchar(100), IN_InvoiceID int)
INSERT INTO Invoicetable (IN_ID, IN_InvoiceID)
VALUES
('2345-BCDE-6645-1DDF', 1),
('2345-BCDE-6645-3DDF', 2),
('2345-BCDE-6645-4DDF', 3),
('2345-BCDE-6645-5DDF', 4),
('2345-BCDE-6645-6DDF', 5),
('2345-BCDE-6645-7DDF', 6),
('2345-BCDE-6645-aDDF', 7),
('2345-BCDE-6645-sDDF', 8),
('2345-BCDE-6645-dDDF', 9),
('2345-BCDE-6645-dDDF', 10),
('2345-BCDE-6645-dDDF', 11),
('2345-BCDE-6645-dDDF', 12);
with data
as (
select *
,(in_invoiceid-1)/5 as grp
,case when ((in_invoiceid-1)/5)%2=0 then '1' else '0' end as category
,dense_rank() over(partition by case when ((in_invoiceid-1)/5)%2=0 then '1' else '0' end
order by in_invoiceid) as rnk
from invoicetable a
)
select *
from data a
left join data b
on a.rnk=b.rnk
and b.category=0
where a.category=1
您可以尝试使用以下方法 . 使用 (((ROW_NUMBER() OVER (ORDER BY IN_InvoiceID) - 1) / 5) % 2 = 0) 对表进行除法，该组将记录分组在左侧和右侧组中 .
CREATE TABLE #InvoiceTable(
IN_ID varchar(24),
IN_InvoiceID int
)
INSERT INTO #InvoiceTable (IN_ID, IN_InvoiceID)
VALUES
('2345-BCDE-6645-1DDF', 1),
('2345-BCDE-6645-3DDF', 2),
('2345-BCDE-6645-4DDF', 3),
('2345-BCDE-6645-5DDF', 4),
('2345-BCDE-6645-6DDF', 5),
('2345-BCDE-6645-7DDF', 6),
('2345-BCDE-6645-aDDF', 7),
('2345-BCDE-6645-sDDF', 8),
('2345-BCDE-6645-dDDF', 9),
('2345-BCDE-6645-dDDF', 10),
('2345-BCDE-6645-dDDF', 11),
('2345-BCDE-6645-dDDF', 12);
WITH cte AS (
SELECT
IN_ID,
IN_InvoiceID,
CASE
WHEN (((ROW_NUMBER() OVER (ORDER BY IN_InvoiceID) - 1) / 5) % 2 = 0) THEN 'L'
ELSE 'R'
END AS IN_Position
FROM #InvoiceTable
),
cteL AS (
SELECT IN_ID, IN_InvoiceID, ROW_NUMBER() OVER (ORDER BY IN_InvoiceID) AS IN_RowNumber
FROM cte
WHERE IN_Position = 'L'
),
cteR AS (
SELECT IN_ID, IN_InvoiceID, ROW_NUMBER() OVER (ORDER BY IN_InvoiceID) AS IN_RowNumber
FROM cte
WHERE IN_Position = 'R'
)
SELECT cteL.IN_ID, cteL.IN_InvoiceID, cteR.IN_ID, cteR.IN_InvoiceID
FROM cteL
LEFT JOIN cteR ON (cteL.IN_RowNumber = cteR.IN_RowNumber)
6 回答
看起来好像要将表拆分为2个表，交替5行 . 一个简单的方法是：
将数据带入具有额外列的临时表（比如grouping_id）
更新分组ID，以便每5行具有相同的ID . 您可以使用
in_invoiceId % 5（nod函数） . 在此步骤之后，前5行将具有grouping_id 0，接下来的5将具有1，接下来将具有2（假设您的发票ID对于所有行递增1） .你可以用奇数和偶数grouping_id的where子句进行正常选择
测试DDL
结果：
您能否查看文章Display Data in Multiple Columns using SQL以示例情况显示数据库开发人员如何使用Row_Number（）函数和模式算术表达式以列式模式显示数据行列表
您需要从示例中不同的行添加其他列
理想情况下，您可以使用2个表主表和详细信息表进行管理 .
但由于我的好奇心，我能够解决并给出答案
这是我的解决方案
首先，我根据in_invoiceid是否可以被5整除来创建grps . （忽略余数）
之后，我创建一个类别来指示备选组之间（即通过检查余数是否为0或其他）
然后，它是基于in_invoiceid排序的类别字段密集查找记录的问题
最后是category = 1行的连接，具有与category = 0中的记录相同的dense_rank
这是db fiddle链接 .
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=287f101737c580ca271940764b2536ae
您可以尝试使用以下方法 . 使用
(((ROW_NUMBER() OVER (ORDER BY IN_InvoiceID) - 1) / 5) % 2 = 0)对表进行除法，该组将记录分组在左侧和右侧组中 .输出：