原数据实现如下数据,每5行一组,最后一组不够条,返回空数据。
解决方案 »
- 麻烦乌龟大哥帮忙看一下
- 请高手帮忙看看
- 这个数据库备份问题怎么解决?http://community.csdn.net/Expert/topic/4561/4561716.xml?temp=.1408045
- datetime类型我任何存入才能更好地判断生日日期?
- 查询视图重复行赋值
- sqlserver2000 重装以后访问出现:该用户与可信 SQL Server 连接无关联
- 关于排名的问题
- 暴力,表中增加一列,SQL应该怎么写。
- !!!!!!!!!!!!!!!!!!!!十万火急!!!!!!!!!!!!!!!在线等待!!!!!!SQl Server安装问题!!!
- 高分奉上! 请问如何在SQL Server 中删除重复的记录。
- SQL面试题 请将上列数据转化成下面的数据,注意每5条一分组,最后一组不够5返回空行,请写书sql语句
- SQL exec获取返回值报错
(
id int,
name varchar(10),
)
INSERT INTO 表1
select '1','a' union all
select '2','a' union all
select '3','a' union all
select '4','a' union all
select '5','a' union all
select '6','a' union all
select '7','a' union all
select '8','a' union all
select '9','a' union all
select '10','a' union all
select '11','a' union all
select '12','a' union all
select '13','a'
select *,t1=case when id>=1 and id <=5 then '1'
when id>=6 and id <=10 then '2'
when id>=11 and id <=15 then '3'
when id>=16 and id <=20 then '4' end
from 表1id name t1
----------- ---------- ----
1 a 1
2 a 1
3 a 1
4 a 1
5 a 1
6 a 2
7 a 2
8 a 2
9 a 2
10 a 2
11 a 3
12 a 3
13 a 3(13 行受影响)
-- 作者:Gaojier
-- 应用:按记录数分段显示数据,且补充不足数据
-- 日期:2012-12-01
--**************************************
CREATE TABLE Tab(id int,name varchar(10))
INSERT Tab
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'a' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'a' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 10,'a' UNION ALL
SELECT 11,'a' UNION ALL
SELECT 12,'a' UNION ALL
SELECT 13,'a' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 15,'a' UNION ALL
SELECT 16,'a' UNION ALL
SELECT 17,'a' UNION ALL
SELECT 18,'a'
--取得表中最大ID号,需要保证ID不断裂
DECLARE @maxid INT
SELECT @maxid=MAX(id) FROM Tab
--创建临时表,保存必须获取的最终结果的行数
IF OBJECT_ID('#TEMP') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
CREATE TABLE #Temp(id INT)
--计算获取的记录数,并存入临时表
DECLARE @id INT
SET @id=1
SET @maxid=(@maxid/5+1)*5
WHILE @id<=@maxid
BEGIN
INSERT #temp
SELECT @id
SET @id=@id+1
END
--得到结果
SELECT T2.id,T2.name,((T1.id-1)/5)+1
FROM #temp T1 LEFT JOIN
Tab T2 ON T1.id=T2.id
--删除测试环境
DROP TABLE tab
DROP TABLE #Temp
id name
----------- ---------- -----------
1 a 1
2 a 1
3 a 1
4 a 1
5 a 1
6 a 2
7 a 2
8 a 2
9 a 2
10 a 2
11 a 3
12 a 3
13 a 3
14 a 3
15 a 3
16 a 4
17 a 4
18 a 4
NULL NULL 4
NULL NULL 4
这个是何解呢!
as(
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'a' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'a' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 10,'a' UNION ALL
SELECT 11,'a' UNION ALL
SELECT 12,'a' UNION ALL
SELECT 13,'a' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 15,'a' UNION ALL
SELECT 16,'a' UNION ALL
SELECT 17,'a' UNION ALL
SELECT 18,'a'),
source as(
select id,name,rowindex=row_number()over (order by id) from tb
)
select id,name,(rowindex-1)/5+1 t1 from source
INSERT #Tab
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'a' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'a' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 10,'a' UNION ALL
SELECT 11,'a' UNION ALL
SELECT 12,'a' UNION ALL
SELECT 13,'a' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 15,'a' UNION ALL
SELECT 16,'a' UNION ALL
SELECT 17,'a' UNION ALL
SELECT 18,'a'Select d.id,d.name,c.t1
from (
SELECT (t1-1)*5+t2 id,t1 FROM
(select DISTINCT ((id-1)/5)+1 T1 from #Tab) a,(SELECT 1 t2 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) b
) c left join #Tab d on c.id=d.id
--数据借用的2楼的
CREATE TABLE Tab(id int,name varchar(10))
INSERT Tab
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'a' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'a' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 10,'a' UNION ALL
SELECT 11,'a' UNION ALL
SELECT 12,'a' UNION ALL
SELECT 13,'a' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 15,'a' UNION ALL
SELECT 16,'a' UNION ALL
SELECT 17,'a' UNION ALL
SELECT 18,'a'--查询
select t.id,t.name,(number-1)/5+1 T1 FROM MASTER..SPT_VALUES
left join Tab t on t.id=number
WHERE TYPE='P' and number<=(select ((max(id)-1)/5+1)*5 from Tab)and number>0--查询结果
/*
id name T1
----------- ---------- -----------
1 a 1
2 a 1
3 a 1
4 a 1
5 a 1
6 a 2
7 a 2
8 a 2
9 a 2
10 a 2
11 a 3
12 a 3
13 a 3
14 a 3
15 a 3
16 a 4
17 a 4
18 a 4
NULL NULL 4
NULL NULL 4(20 行受影响)
*/