原帖:
http://topic.csdn.net/u/20090710/16/58712A68-F448-426B-8D41-7D6B2A22246A.html
题目大概如下:
【写一段 sql语句,
表 TAB1 中的id 为自增长,增长量为1,表如下:
id tac tbc 1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4
5 a5 b5 要求输出如下:
id tac tbc id tac tbc 1 a1 b1 2 a2 b2
3 a3 b3 4 a4 b4
5 a5 b5 * (注:* 表示任何字符)】 这段sql语句怎么写啊? 请各位帮帮忙。谢谢啦!!
===================
高手们的答复是:-------------------------------------------
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-10 16:07:15
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 2,'a2','b2' UNION ALL
SELECT 3,'a3','b3' UNION ALL
SELECT 4,'a4','b4' UNION ALL
SELECT 5,'a5','b5'--SQL查询如下:SELECT *
FROM @T AS A
LEFT JOIN @T AS B
ON A.id=B.id-1
WHERE A.id % 2 = 1;/*
id tac tbc id tac tbc
----------- ---- ---- ----------- ---- ----
1 a1 b1 2 a2 b2
3 a3 b3 4 a4 b4
5 a5 b5 NULL NULL NULL(3 row(s) affected)*/
我觉得这个做法是有点问题的,因为没考虑到中间部分行被删除的情况,我觉得正确的答案应该是:DECLARE @T TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 3,'a2','b2' UNION ALL
SELECT 5,'a3','b3' UNION ALL
SELECT 6,'a4','b4' UNION ALL
SELECT 7,'a5','b5'DECLARE @K TABLE (tid INT identity(1,1),id INT,tac VARCHAR(2),tbc VARCHAR(2))
--SQL查询如下:
insert into @K(id,tac,tbc)
select * from @T
SELECT a.id,a.tac,a.tbc,b.id,b.tac,b.tbc
FROM @K AS A
LEFT JOIN @K AS B
ON A.tid=B.tid-1
WHERE A.tid % 2 = 1;
http://topic.csdn.net/u/20090710/16/58712A68-F448-426B-8D41-7D6B2A22246A.html
题目大概如下:
【写一段 sql语句,
表 TAB1 中的id 为自增长,增长量为1,表如下:
id tac tbc 1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4
5 a5 b5 要求输出如下:
id tac tbc id tac tbc 1 a1 b1 2 a2 b2
3 a3 b3 4 a4 b4
5 a5 b5 * (注:* 表示任何字符)】 这段sql语句怎么写啊? 请各位帮帮忙。谢谢啦!!
===================
高手们的答复是:-------------------------------------------
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-10 16:07:15
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 2,'a2','b2' UNION ALL
SELECT 3,'a3','b3' UNION ALL
SELECT 4,'a4','b4' UNION ALL
SELECT 5,'a5','b5'--SQL查询如下:SELECT *
FROM @T AS A
LEFT JOIN @T AS B
ON A.id=B.id-1
WHERE A.id % 2 = 1;/*
id tac tbc id tac tbc
----------- ---- ---- ----------- ---- ----
1 a1 b1 2 a2 b2
3 a3 b3 4 a4 b4
5 a5 b5 NULL NULL NULL(3 row(s) affected)*/
我觉得这个做法是有点问题的,因为没考虑到中间部分行被删除的情况,我觉得正确的答案应该是:DECLARE @T TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 3,'a2','b2' UNION ALL
SELECT 5,'a3','b3' UNION ALL
SELECT 6,'a4','b4' UNION ALL
SELECT 7,'a5','b5'DECLARE @K TABLE (tid INT identity(1,1),id INT,tac VARCHAR(2),tbc VARCHAR(2))
--SQL查询如下:
insert into @K(id,tac,tbc)
select * from @T
SELECT a.id,a.tac,a.tbc,b.id,b.tac,b.tbc
FROM @K AS A
LEFT JOIN @K AS B
ON A.tid=B.tid-1
WHERE A.tid % 2 = 1;
*
FROM
(SELECT *,PX=(SELECT COUNT(1)+1 FROM @T WHERE ID<T.ID) FROM @T AS T) AS A
LEFT JOIN
(SELECT *,PX=(SELECT COUNT(1)+1 FROM @T WHERE ID<T.ID) FROM @T AS T) AS B
ON
A.PX=B.PX-1
WHERE
A.PX % 2 = 1;
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 3,'a2','b2' UNION ALL
SELECT 5,'a3','b3' UNION ALL
SELECT 6,'a4','b4' UNION ALL
SELECT 7,'a5','b5'select
max(case (px - 1)%2 when 0 then id end) id,
max(case (px - 1)%2 when 0 then tac end) tac,
max(case (px - 1)%2 when 0 then tbc end) tbc,
max(case (px - 1)%2 when 1 then id end) id,
max(case (px - 1)%2 when 1 then tac end) tac,
max(case (px - 1)%2 when 1 then tbc end) tbc
from
(
select * , px = (select count(1) from @t where id <t.id ) + 1 from @t t
) m
group by (px - 1)/2/*
id tac tbc id tac tbc
----------- ---- ---- ----------- ---- ----
1 a1 b1 3 a2 b2
5 a3 b3 6 a4 b4
7 a5 b5 NULL NULL NULL(所影响的行数为 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'--SQL查询如下:SELECT t1.序号, t1.数据,
t2.序号, t2.数据
FROM @T t1 left join @T t2
on t1.序号=t2.序号-1
where t1.序号%2=1
DECLARE @T TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 2,'a2','b2' UNION ALL
SELECT 7,'a3','b3' UNION ALL
SELECT 9,'a4','b4' UNION ALL
SELECT 10,'a5','b5';with t as
(select *, ord=(row_number() over (order by id)) from @T)
SELECT A.id,A.tac,A.tbc,B.id,B.tac,B.tbc
FROM T AS A
LEFT JOIN T AS B ON B.ord=A.ord+1
WHERE A.ord % 2 = 1
INSERT INTO @T
SELECT 1,'a1','b1' UNION ALL
SELECT 3,'a2','b2' UNION ALL
SELECT 5,'a3','b3' UNION ALL
SELECT 6,'a4','b4' UNION ALL
SELECT 7,'a5','b5'SELECT *
FROM (select aa.*,ROW_Number() over(order by getdate()) as rn from @T aa) AS A
LEFT JOIN (select bb.*,ROW_Number() over(order by getdate()) as rn from @T bb) AS B
ON A.rn=B.rn-1
WHERE A.rn % 2 = 1;