---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-11-04 17:03:19 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([age] int,[name] varchar(1)) insert [tb] select 1,'q' union all select 1,'w' union all select 3,'r' union all select 2,'h' union all select 2,'c' union all select 3,'d' --------------开始查询-------------------------- select '1~2', sum(case when age between 1 and 2 then 1 else 0 end) as [name] from tb union all select '3~4', sum(case when age between 3 and 4 then 1 else 0 end ) as [name] from tb----------------结果---------------------------- /* (6 行受影响) name ---- ----------- 1~2 4 3~4 2(2 行受影响)*/
当然可以了 select cast(t1.age as varchar)+'~'+cast(t1.age+1 as varchar),name_count=(select count(1) from tb where age between t1.age and t1.age+1) from tb
if object_id('[tb]') is not null drop table [tb] go create table [tb]([age] int,[name] varchar(1)) insert [tb] select 1,'q' union all select 1,'w' union all select 3,'r' union all select 2,'h' union all select 2,'c' union all select 3,'d' select age = case when age between 1 and 2 then '1~2' when age between 3 and 4 then '3~4' end, name = count(*)
from tb group by case when age between 1 and 2 then '1~2' when age between 3 and 4 then '3~4' end
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([age] int,[name] varchar(1)) insert [tb] select 1,'q' union all select 1,'w' union all select 3,'r' union all select 2,'h' union all select 2,'c' union all select 3,'d' --------------开始查询-------------------------- SELECT [age],SUM(cntt) cnt FROM (SELECT [age]= CASE WHEN [age] BETWEEN 1 AND 2 THEN '1~2' WHEN [age] BETWEEN 3 AND 4 THEN '3~4' END , COUNT([age]) cntt FROM [tb] GROUP BY [age]) aa GROUP BY [age]
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-04 17:03:19
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([age] int,[name] varchar(1))
insert [tb]
select 1,'q' union all
select 1,'w' union all
select 3,'r' union all
select 2,'h' union all
select 2,'c' union all
select 3,'d'
--------------开始查询--------------------------
select
'1~2',
sum(case when age between 1 and 2 then 1 else 0 end) as [name]
from
tb
union all
select
'3~4',
sum(case when age between 3 and 4 then 1 else 0 end ) as [name]
from
tb----------------结果----------------------------
/* (6 行受影响)
name
---- -----------
1~2 4
3~4 2(2 行受影响)*/
select cast(t1.age as varchar)+'~'+cast(t1.age+1 as varchar),name_count=(select count(1) from tb where age between t1.age and t1.age+1) from tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([age] int,[name] varchar(1))
insert [tb]
select 1,'q' union all
select 1,'w' union all
select 3,'r' union all
select 2,'h' union all
select 2,'c' union all
select 3,'d'
select age = case when age between 1 and 2 then '1~2'
when age between 3 and 4 then '3~4'
end, name = count(*)
from tb
group by case when age between 1 and 2 then '1~2'
when age between 3 and 4 then '3~4'
end
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([age] int,[name] varchar(1))
insert [tb]
select 1,'q' union all
select 1,'w' union all
select 3,'r' union all
select 2,'h' union all
select 2,'c' union all
select 3,'d'
--------------开始查询--------------------------
SELECT [age],SUM(cntt) cnt FROM
(SELECT [age]= CASE WHEN [age] BETWEEN 1 AND 2 THEN '1~2'
WHEN [age] BETWEEN 3 AND 4 THEN '3~4'
END ,
COUNT([age]) cntt
FROM [tb]
GROUP BY [age]) aa
GROUP BY [age]