请教1个SQL的写法,多谢!
我有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
[解决办法]
DECLARE @tb TABLE (fid INT)
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
[解决办法]
--> 测试数据：#tb
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
----------------结果----------------------------
/*
(无列名)(无列名)
17
813
2255
*/
[解决办法]
----------------------------------------------------------------
-- 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
/*
FMinIDasFMaxID
1 7
8 13
22 55
*/
;WITH f AS
(
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