求一条分组查询语句

建表语句

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[poslist]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[poslist](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[qsrq] [datetime] NULL,
	[jysj] [datetime] NULL,
	[posid] [varchar](50) NULL,
	[shid] [varchar](50) NULL,
	[cardid] [varchar](50) NULL,
	[jylx] [varchar](4) NULL,
	[jyje] [float] NULL,
	[rate] [float] NULL,
	[fengding] [float] NULL,
	[jgdm] [varchar](20) NULL,
	[lbdm] [varchar](20) NULL,
	[yxrdm] [varchar](20) NULL,
	[ticheng] [float] NULL
) ON [PRIMARY]
END

数据已经导出为txt文本，地址：http://download.csdn.net/detail/hu_service/4018834

 

这个是查出的一个小于10的记录：

select jgdm,count(pid) as 小于10 from 
(select jgdm,pid,count (id) as bs from
 (select * from test where jysj between'2011-12-1 00:00:00' and '2012-1-13 23:59:59') t group by jgdm,
pid having count(id)<10) f group by jgdm ;

结果：

jgdm    小于10 

4501	51
4504	29
1981	26
1601	106
4503	17
1341	50

按你的sql语句：

with maco as(
select 
jgdm,
count(pid) as 数值 from test where jysj between'2011-12-1 00:00:00' and '2012-1-13 23:59:59' group by jgdm
)

select 
    jgdm=isnull(jgdm,'heji'),
    [小于10]=sum(case when 数值<10 then  数值 else 0 end),
    [10-30]=sum(case when 数值>=10 and 数值<30 then  数值 else 0 end),
    [30-50]=sum(case when 数值>=30 and 数值<50 then  数值 else 0 end),
    [50-100]=sum(case when 数值>=50 and 数值<100 then  数值 else 0 end),
    [大于100]=sum(case when 数值>=100 then 数值 else 0 end)
from maco group by jgdm with rollup

 

结果不正确
 

jgdm    小于10  10-30   30-50  50-100   大于100   合计

1341	0	0	0	0	15328
1601	0	0	0	0	11049
1981	0	0	0	0	4874
4501	0	0	0	0	10843
4503	0	0	0	0	1489
4504	0	0	0	0	5514
heji	0	0	0	0	49097

按说小于10的记录应该和上边查出来的结果是一样的。  我不知道 having加在哪里。

上边我的语句只是查出小于10的记录，应该像下边这个结果的样子是一样的，小于10  10-30   30-50  50-100   大于100  同时显出来。最后一列最好也可以加一个合计。