求一条分组查询语句

来源：互联网发布：淘宝卖家互刷交流群编辑：程序博客网时间：2024/05/16 17:01

建表语句

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[poslist]') AND type in (N'U'))BEGINCREATE 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

4501514504291981261601106450317134150

按你的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   合计

134100001532816010000110491981000048744501000010843450300001489450400005514heji000049097

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

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