---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-12-29 13:26:40 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([place] varchar(5),[name] varchar(4),[time] datetime) insert [tb] select '病房2','张三','2011-12-28 15:19:43.000' union all select '大门','张三','2011-12-28 15:19:45.000' union all select '病房1','张三','2011-12-28 15:19:48.000' union all select '病房1','李四','2011-12-28 15:19:53.000' union all select '病房2','李四','2011-12-28 15:19:56.000' union all select '大门','李四','2011-12-28 15:19:59.000' union all select '病房1','张三','2011-12-29 12:08:42.000' union all select '病房2','张三','2011-12-29 12:08:44.000' union all select '大门','张三','2011-12-29 12:08:46.000' union all select '病房1','李四','2011-12-29 12:08:52.000' union all select '病房2','李四','2011-12-29 12:08:54.000' union all select '大门','李四','2011-12-29 12:08:56.000' --------------开始查询-------------------------- declare @sql varchar(8000) set @sql = 'select isnull(place,''合计'') ' select @sql = @sql + ' , sum(case convert(varchar(10),[time],120) when ''' + [time] + ''' then 1 else 0 end) [' + convert(varchar(10),[time],120) + ']' from (select distinct convert(varchar(10),[time],120) as time from tb) as a set @sql = @sql + ' ,count(1) as 合计 from tb group by place with rollup' exec(@sql) ----------------结果---------------------------- /* 2011-12-28 2011-12-29 合计 ----- ----------- ----------- ----------- 病房1 2 2 4 病房2 2 2 4 大门 2 2 4 合计 6 6 12(4 行受影响)*/
数据库两种中类型 1、Access 2、Sql 2005
--统计张三的这么写:declare @sql varchar(8000) set @sql = 'select isnull(place,''合计'') ' select @sql = @sql + ' , sum(case convert(varchar(10),[time],120) when ''' + [time] + ''' then 1 else 0 end) [' + convert(varchar(10),[time],120) + ']' from (select distinct convert(varchar(10),[time],120) as time from tb) as a set @sql = @sql + ' ,count(1) as 合计 from tb where name=''张三'' group by place with rollup' exec(@sql) 自己可以修改成存储过程。
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-29 13:26:40
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([place] varchar(5),[name] varchar(4),[time] datetime)
insert [tb]
select '病房2','张三','2011-12-28 15:19:43.000' union all
select '大门','张三','2011-12-28 15:19:45.000' union all
select '病房1','张三','2011-12-28 15:19:48.000' union all
select '病房1','李四','2011-12-28 15:19:53.000' union all
select '病房2','李四','2011-12-28 15:19:56.000' union all
select '大门','李四','2011-12-28 15:19:59.000' union all
select '病房1','张三','2011-12-29 12:08:42.000' union all
select '病房2','张三','2011-12-29 12:08:44.000' union all
select '大门','张三','2011-12-29 12:08:46.000' union all
select '病房1','李四','2011-12-29 12:08:52.000' union all
select '病房2','李四','2011-12-29 12:08:54.000' union all
select '大门','李四','2011-12-29 12:08:56.000'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(place,''合计'') '
select @sql = @sql + ' , sum(case convert(varchar(10),[time],120) when ''' + [time] + ''' then 1 else 0 end) [' + convert(varchar(10),[time],120) + ']'
from (select distinct convert(varchar(10),[time],120) as time from tb) as a
set @sql = @sql + ' ,count(1) as 合计 from tb group by place with rollup'
exec(@sql)
----------------结果----------------------------
/* 2011-12-28 2011-12-29 合计
----- ----------- ----------- -----------
病房1 2 2 4
病房2 2 2 4
大门 2 2 4
合计 6 6 12(4 行受影响)*/
2、Sql 2005
set @sql = 'select isnull(place,''合计'') '
select @sql = @sql + ' , sum(case convert(varchar(10),[time],120) when ''' + [time] + ''' then 1 else 0 end) [' + convert(varchar(10),[time],120) + ']'
from (select distinct convert(varchar(10),[time],120) as time from tb) as a
set @sql = @sql + ' ,count(1) as 合计 from tb where name=''张三'' group by place with rollup'
exec(@sql) 自己可以修改成存储过程。
在sql2005 中 完全没有问题
但是在Access中呢