34,593
社区成员
发帖
与我相关
我的任务
分享
select a.*,isnull(cast(b.序号 as varchar(5)),'') as 序号,isnull(b.数据,'') as 数据
from(select * from tb where (序号-1)/3=0)a
left join(select * from tb where (序号-1)/3=1)b
on a.序号%3=b.序号%3
/**
序号 数据 序号 数据
----- ---- ----- ----
1 x1 4 x4
2 x2 5 x5
3 x3
(所影响的行数为 3 行)
**/
-------------------------------------
-- Author : Luoyoumou
-- Comment: 三月红梨
-- Date : 2009-10-14 09:43:51
-------------------------------------
-- 根据你的记录行,数据对半分
--> 生成测试数据: @T
DECLARE @T TABLE (序号 int,数据 varchar(2))
INSERT INTO @T
SELECT 1,'x1' UNION ALL
SELECT 2,'x2' UNION ALL
SELECT 3,'x3' UNION ALL
SELECT 4,'x4' UNION ALL
SELECT 5,'x5'
DECLARE @B INT
SELECT @B=((MAX(序号)+1)/2) FROM @T
PRINT @B;
--SQL查询如下:
SELECT t1.序号, t1.数据,
t2.序号, t2.数据
FROM @T t1 left join @T t2
on (t1.序号%@B)=(t2.序号%@B) and t1.序号<=@B and t2.序号>@B
where t1.序号<=@B
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-13 22:01:51
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (序号 int,数据 varchar(2))
INSERT INTO @T
SELECT 1,'x1' UNION ALL
SELECT 2,'x2' UNION ALL
SELECT 3,'x3' UNION ALL
SELECT 4,'x4' UNION ALL
SELECT 5,'x5'
--SQL查询如下:
SELECT
MAX(CASE WHEN (序号-1)/3 = 0 THEN LTRIM(序号) ELSE '' END),
MAX(CASE WHEN (序号-1)/3 = 0 THEN 数据 ELSE '' END),
MAX(CASE WHEN (序号-1)/3 = 1 THEN LTRIM(序号) ELSE '' END),
MAX(CASE WHEN (序号-1)/3 = 1 THEN 数据 ELSE '' END)
FROM @T
GROUP BY (序号-1)%3 + 1
/*
------------ ---- ------------ ----
1 x1 4 x4
2 x2 5 x5
3 x3
(3 行受影响)
*/