select col1, col2 from T where col1 between 1 and 6 union all select 7, sum(col2) from T where col1 between 7 and 9 union all select 10, sum(col2) from T where col1 >=10
1-6不变 大于等于7次但小于10的求和 大于10次以上的求和select * from tb where ID<=6 union all select col,sum(col2) from tb where ID between 7 and 9 union all select col,sum(col2) from tb where ID>10
select col1, col2 from T where col1 between 1 and 6 union all select 7, sum(col2) from T where col1 between 7 and 9 union all select 10, sum(col2) from T where col1 >=10ding...
create table #TT ( Col1 int, Col2 int ) insert into #TT select 1,3 insert into #TT select 2,4 insert into #TT select 3,5 insert into #TT select 4,6 insert into #TT select 5,7 insert into #TT select 6,10 insert into #TT select 7,3 insert into #TT select 8,3 insert into #TT select 9,10 insert into #TT select 11,4 insert into #TT select 10,3select * from #TT where Col1<=6 union all select min(Col1),sum(Col2) from #TT where Col1 between 7 and 9 union all select min(Col1),sum(Col2) from #TT where Col1>=10Col1 Col2 ----------- ----------- 1 3 2 4 3 5 4 6 5 7 6 10 7 16 10 7(8 行受影响)
up SQL code select col1, col2 from T where col1 between 1 and 6 union all select 7, sum(col2) from T where col1 between 7 and 9 union all select 10, sum(col2) from T where col1 >=10
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-07 14:43:39 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([a] INT,[b] INT) INSERT [tb] SELECT 1,3 UNION ALL SELECT 2,4 UNION ALL SELECT 3,5 UNION ALL SELECT 4,6 UNION ALL SELECT 5,7 UNION ALL SELECT 6,10 UNION ALL SELECT 7,3 UNION ALL SELECT 8,3 UNION ALL SELECT 9,10 UNION ALL SELECT 10,3 UNION ALL SELECT 11,4 GO --SELECT * FROM [tb]-->SQL查询如下:select case when a <7 then a when a between 7 and 9 then 9 else 10 end a,SUM(b) b from tb group by case when a <7 then a when a between 7 and 9 then 9 else 10 end/* a b ----------- ----------- 1 3 2 4 3 5 4 6 5 7 6 10 9 16 10 7(8 行受影响) */
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-07 14:43:39 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([a] INT,[b] INT) INSERT [tb] SELECT 1,3 UNION ALL SELECT 2,4 UNION ALL SELECT 3,5 UNION ALL SELECT 4,6 UNION ALL SELECT 5,7 UNION ALL SELECT 6,10 UNION ALL SELECT 7,3 UNION ALL SELECT 8,3 UNION ALL SELECT 9,10 UNION ALL SELECT 10,3 UNION ALL SELECT 11,4 GO --SELECT * FROM [tb]-->SQL查询如下:select MIN(a) a,SUM(b) b from tb group by case when a <7 then a when a between 7 and 9 then 7 else 10 end/* a b ----------- ----------- 1 3 2 4 3 5 4 6 5 7 6 10 7 16 10 7(8 行受影响) */还是用这条吧,代码没那么多.
from T
where col1 between 1 and 6
union all
select 7, sum(col2)
from T
where col1 between 7 and 9
union all
select 10, sum(col2)
from T
where col1 >=10
大于等于7次但小于10的求和
大于10次以上的求和select * from tb where ID<=6
union all
select col,sum(col2) from tb where ID between 7 and 9
union all
select col,sum(col2) from tb where ID>10
from T
where col1 between 1 and 6
union all
select 7, sum(col2)
from T
where col1 between 7 and 9
union all
select 10, sum(col2)
from T
where col1 >=10ding...
(
Col1 int,
Col2 int
)
insert into #TT select 1,3
insert into #TT select 2,4
insert into #TT select 3,5
insert into #TT select 4,6
insert into #TT select 5,7
insert into #TT select 6,10
insert into #TT select 7,3
insert into #TT select 8,3
insert into #TT select 9,10
insert into #TT select 11,4
insert into #TT select 10,3select * from #TT where Col1<=6
union all
select min(Col1),sum(Col2) from #TT where Col1 between 7 and 9
union all
select min(Col1),sum(Col2) from #TT where Col1>=10Col1 Col2
----------- -----------
1 3
2 4
3 5
4 6
5 7
6 10
7 16
10 7(8 行受影响)
SQL code
select col1, col2
from T
where col1 between 1 and 6
union all
select 7, sum(col2)
from T
where col1 between 7 and 9
union all
select 10, sum(col2)
from T
where col1 >=10
-- Author : htl258(Tony)
-- Date : 2010-04-07 14:43:39
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT)
INSERT [tb]
SELECT 1,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,5 UNION ALL
SELECT 4,6 UNION ALL
SELECT 5,7 UNION ALL
SELECT 6,10 UNION ALL
SELECT 7,3 UNION ALL
SELECT 8,3 UNION ALL
SELECT 9,10 UNION ALL
SELECT 10,3 UNION ALL
SELECT 11,4
GO
--SELECT * FROM [tb]-->SQL查询如下:select case when a <7 then a when a between 7 and 9 then 9 else 10 end a,SUM(b) b
from tb
group by case when a <7 then a when a between 7 and 9 then 9 else 10 end/*
a b
----------- -----------
1 3
2 4
3 5
4 6
5 7
6 10
9 16
10 7(8 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-07 14:43:39
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT)
INSERT [tb]
SELECT 1,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,5 UNION ALL
SELECT 4,6 UNION ALL
SELECT 5,7 UNION ALL
SELECT 6,10 UNION ALL
SELECT 7,3 UNION ALL
SELECT 8,3 UNION ALL
SELECT 9,10 UNION ALL
SELECT 10,3 UNION ALL
SELECT 11,4
GO
--SELECT * FROM [tb]-->SQL查询如下:select MIN(a) a,SUM(b) b
from tb
group by case when a <7 then a when a between 7 and 9 then 7 else 10 end/*
a b
----------- -----------
1 3
2 4
3 5
4 6
5 7
6 10
7 16
10 7(8 行受影响)
*/还是用这条吧,代码没那么多.