我有1个表Table1, 其中有1个字段FID, 这个字段的值如下:
FID
1
8
2
46
13
55
5
33
7
9
11
22
我对FID执行Order by 得到序列如下
1
2
5
7
8
9
11
13
22
33
46
55
我想得到的结果集如下:以4个FID值为1个组,将其中的最小值放在FMinID,最大值放在FMaxID.FMinID, FMaxID
1 7
8 13
22 55
FID
1
8
2
46
13
55
5
33
7
9
11
22
我对FID执行Order by 得到序列如下
1
2
5
7
8
9
11
13
22
33
46
55
我想得到的结果集如下:以4个FID值为1个组,将其中的最小值放在FMinID,最大值放在FMaxID.FMinID, FMaxID
1 7
8 13
22 55
解决方案 »
- 修改视图出错
- 请教问题 急!!!
- WinXP 下如何安装SQL SERVER (6.5) 的问题
- 不能对数据类型int指定列宽度!
- SQL Server查询操作如何设置为并发执行
- 结存问题!
- 我想把‘,’号中的每一个知识点提取出来取不重复的插入到另外一个字段怎么处理
- 安装了sql skp3后,原先的网络共享上网不正常了
- 表table字段a,里面是以数字加逗号形式,如1,2 另一个是查询后取得的ID集合, 如何写取得表table中所有符合字段a中数字在查询集合有的记录?
- sqlserver里面,sql语句里面创建表的时候怎么同时加入列描述内容(即字段的描述内容)?
- 求解!如何写这个SQL语句?
- 如何实现:向数据库中插入数据的同时获取其自增字段(ID)
INSERT INTO @tb VALUES (1)
INSERT INTO @tb VALUES (8)
INSERT INTO @tb VALUES (2)
INSERT INTO @tb VALUES (46)
INSERT INTO @tb VALUES (13)
INSERT INTO @tb VALUES (55)
INSERT INTO @tb VALUES (5)
INSERT INTO @tb VALUES (33)
INSERT INTO @tb VALUES (7)
INSERT INTO @tb VALUES (9)
INSERT INTO @tb VALUES (11)
INSERT INTO @tb VALUES (22)select MINID = min(fid),MAXID = max(fid) from (SELECT case when ROW_NUMBER() OVER (ORDER BY fid)%4 >0 THEN
ROW_NUMBER() OVER (ORDER BY fid)/4+1 ELSE ROW_NUMBER() OVER (ORDER BY fid)/4 END AS rn
,* FROM @tb ) a GROUP BY rn
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([FID] INT)
INSERT #tb
SELECT 1 UNION ALL
SELECT 8 UNION ALL
SELECT 2 UNION ALL
SELECT 46 UNION ALL
SELECT 13 UNION ALL
SELECT 55 UNION ALL
SELECT 5 UNION ALL
SELECT 33 UNION ALL
SELECT 7 UNION ALL
SELECT 9 UNION ALL
SELECT 11 UNION ALL
SELECT 22
--------------开始查询--------------------------SELECT MIN([FID]),MAX([FID])
FROM
(
SELECT *,row_id = (ROW_NUMBER() OVER(ORDER BY [FID])-1)/4 FROM #tb
) t
GROUP BY row_id
----------------结果----------------------------
/*
(无列名) (无列名)
1 7
8 13
22 55
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-14 11:28:48
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([FID] int)
insert [table1]
select 1 union all
select 8 union all
select 2 union all
select 46 union all
select 13 union all
select 55 union all
select 5 union all
select 33 union all
select 7 union all
select 9 union all
select 11 union all
select 22
--------------开始查询--------------------------
SELECT MIN(FID)MINFID,MAX(FID)MAXFID
FROM (
SELECT fid,ROW_NUMBER()OVER(PARTITION BY id%4 ORDER BY FID)id2
FROM (
select *,ROW_NUMBER()OVER(ORDER BY FID)id
from [table1])a
)B
GROUP BY ID2----------------结果----------------------------
/*
MINFID MAXFID
----------- -----------
1 7
8 13
22 55*/
if object_id('[tb]') is not null drop table tb
go create table tb([FID] int)
insert tb
select 1 union all
select 8 union all
select 2 union all
select 46 union all
select 13 union all
select 55 union all
select 5 union all
select 33 union all
select 7 union all
select 9 union all
select 11 union all
select 22--本质上就是一个分页的代码
select min(fid) as FMinID, max(fid) asFMaxID
from
(
select *,
(ROW_NUMBER() over(order by fid)-1)/4 as page --这条记录所在的page
from tb
)t
group by page
/*
FMinID asFMaxID
1 7
8 13
22 55
*/
(
SELECT TOP 1000 id=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB ORDER BY fid
)
SELECT
a.fid,b.fid
FROM
(SELECT * FROM f WHERE (ID-1)%4=0) a
LEFT JOIN
(SELECT * FROM f WHERE (ID-1)%4=3) b
ON
a.id=b.id-3