SELECT '0.1' AS id
UNION ALL
SELECT '1.2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '3.8' AS id
UNION ALL
SELECT '4.3' AS id我想得把这些数据平均分组4(或者X)组,第一个要求是值ID 相同的必须在同一个组,其次均分
最后要的结果是:
Row id
1 0.1
1 1.2
2 2
2 2
2 2
2 2
3 3.8
4 4.3
UNION ALL
SELECT '1.2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '3.8' AS id
UNION ALL
SELECT '4.3' AS id我想得把这些数据平均分组4(或者X)组,第一个要求是值ID 相同的必须在同一个组,其次均分
最后要的结果是:
Row id
1 0.1
1 1.2
2 2
2 2
2 2
2 2
3 3.8
4 4.3
Trywith leno as (SELECT '0.1' AS id
UNION ALL
SELECT '1.2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '3.8' AS id
UNION ALL
SELECT '4.3' AS id
)
select id,rownumber = case when FLOOR (id) = 0 then FLOOR (id) + 1 else FLOOR (id) end from leno--result
/*
id rownumber
---- ----------------------
0.1 1
1.2 1
2 2
2 2
2 2
2 2
3.8 3
4.3 4(8 行受影响)
*/
我就是要把 数据的结果 分4个组,要求是 尽量均分,但是如果ID值一样的话,必须要分到同一个组
SELECT '0.1' AS id
UNION ALL
SELECT '1.2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '2' AS id
UNION ALL
SELECT '9.8' AS id
UNION ALL
SELECT '40001.3' AS id
UNION ALL
SELECT '112357677.3' AS id-------------------期待结果----------------
0.1 1
1.2 1
2 2
2 2
2 2
2 2
9.8 3
40001.3 3
112357677.3 4
create table [tt]([id] numeric(10,1))
insert [tt]
select 0.1 union all
select 1.2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 9.8 union all
select 40001.3 union all
select 112357677.3
SELECT id,NTILE(4) OVER(ORDER BY (id)) FROM tt
select 0.1 AS id
union all
select 1.2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 9.8 AS id
union all
select 40001.3 AS id
union all
select 112357677.3
)SELECT ID ,CAST((rn - 1) / tilesize + 1 AS INT) tileFROM (SELECT ID, rn, 1.*numrows/3 AS tilesize FROM (
SELECT top 100 PERCENT ID,(SELECT COUNT(*) FROM tt AS S2
WHERE S2.ID < S1.ID OR S2.ID = S1.ID
AND S2.ID <= S1.ID) AS rn,(SELECT COUNT(*) FROM tt) AS numrows
FROM tt AS S1 order by ID) AS D1)AS D2
ORDER BY ID;
--------------------------------------- -----------
0.1 1
1.2 1
2.0 2
2.0 2
2.0 2
2.0 2
9.8 3
40001.3 3
112357677.3 3(9 行受影响)
select 0.1 AS id
union all
select 1.2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 2 AS id
union all
select 9.8 AS id
union all
select 40001.3 AS id
union all
select 112357677.3
)SELECT ID ,cast(CASE
WHEN rn <= (tilesize+1) * tt
THEN (rn-1) / (tilesize+1) + 1
ELSE (rn - tt - 1) / tilesize + 1
END as int) AS tile FROM (
SELECT ID, rn,rn2, 1.*numrows/3 AS tilesize, numrows%3 AS tt FROM (
SELECT top 100 PERCENT ID, (SELECT distinct COUNT( *) FROM tt) AS numrows,
dense_rank() over (order by ID) AS rn2,
(SELECT COUNT(*) FROM tt AS S2 WHERE S2.ID <= S1.ID) AS rn
fROM tt AS S1 order by ID)AS D1)AS D2 ORDER BY ID;