建表语句
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 同时显出来。最后一列最好也可以加一个合计。