34,576
社区成员
发帖
与我相关
我的任务
分享
select hFrom as 城市, 总数据量=count(hFrom),
未审核的数量=sum(case when hBszt=0 then 1 else 0 end),
已接收的数量=sum(case when hBszt=1 then 1 else 0 end),
未接收的数量=sum(case when hBszt=2 then 1 else 0 end)
from wenZhangTongJi
where left(hTDate,4)='2008'
group by hFrom
select hFrom as 城市, 总数据量=count(*),
未审核的数量=sum(case when hBszt=0 then 1 else 0 end),
已接收的数量=sum(case when hBszt=1 then 1 else 0 end),
未接收的数量=sum(case when hBszt=2 then 1 else 0 end)
from wenZhangTongJi
where left(hTDate,4)='2008'
group by hFrom
-->生成测试数据
declare @tb table([hId] int,[hFrom] nvarchar(2),[hTDate] Datetime,[hAdmin] int,[hBszt] nvarchar(1))
Insert @tb
select 26,N'邯郸','2008-8-1 10:49:25',2,N'1' union all
select 27,N'邯郸','2008-8-1 10:49:29',2,N'1' union all
select 28,N'邯郸','2008-8-1 10:49:33',2,N'2' union all
select 29,N'邯郸','2008-8-1 10:49:37',2,N'1' union all
select 30,N'邯郸','2008-7-1 10:49:48',2,N'1' union all
select 31,N'邯郸','2008-7-1 10:49:51',2,N'2' union all
select 32,N'邯郸','2007-7-16 10:50:04',2,N'1' union all
select 33,N'邯郸','2007-7-16 10:50:09',2,N'0' union all
select 34,N'邯郸','2008-8-16 10:59:59',2,N'2' union all
select 35,N'邯郸','2008-8-1 11:01:12',2,N'0' union all
select 36,N'沧州','2008-8-1 11:48:24',5,N'1' union all
select 37,N'沧州','2008-8-1 11:49:25',5,N'2'
Select [hFrom], count(1) as 总数据量,
sum(case when [hBszt] =0 then 1 else 0 end ) 未审核的数量,
sum(case when [hBszt] =1 then 1 else 0 end ) 已接收的数量,
sum(case when [hBszt] =2 then 1 else 0 end ) 未接收的数量
from @tb group by [hFrom]
/*
hFrom 总数据量 未审核的数量 已接收的数量 未接收的数量
----- ----------- ----------- ----------- -----------
沧州 2 0 1 1
邯郸 10 2 5 3
*/
CREATE TABLE [wenZhangTongJi] (
[hId] [int] NOT NULL ,
[hFrom] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hTDate] [datetime] NOT NULL CONSTRAINT [DF_wenZhangTongJi_hTDate] DEFAULT (getdate()),
[hAdmin] [int] NOT NULL ,
[hBszt] [int] NOT NULL CONSTRAINT [DF_wenZhangTongJi_hBszt] DEFAULT (0),
CONSTRAINT [PK_wenZhangTongJi] PRIMARY KEY CLUSTERED
(
[hId]
) ON [PRIMARY]
) ON [PRIMARY]
Insert [wenZhangTongJi]
select 26,N'邯郸','2008-8-1 10:49:25',2,1 union all
select 27,N'邯郸','2008-8-1 10:49:29',2,1 union all
select 28,N'邯郸','2008-8-1 10:49:33',2,2 union all
select 29,N'邯郸','2008-8-1 10:49:37',2,1 union all
select 30,N'邯郸','2008-7-1 10:49:48',2,1 union all
select 31,N'邯郸','2008-7-1 10:49:51',2,2 union all
select 32,N'邯郸','2007-7-16 10:50:04',2,1 union all
select 33,N'邯郸','2007-7-16 10:50:09',2,0 union all
select 34,N'邯郸','2008-8-16 10:59:59',2,2 union all
select 35,N'邯郸','2008-8-1 11:01:12',2,0 union all
select 36,N'沧州','2008-8-1 11:48:24',5,1 union all
select 37,N'沧州','2008-8-1 11:49:25',5,2
go
--drop table [wenZhangTongJi]
select
[hFrom] as 城市,
count(*) as 總数量,
sum(case when hBszt=0 then 1 else 0 end) as 未审核的数量,
sum(case when hBszt=1 then 1 else 0 end) as 已接收的数量,
sum(case when hBszt=2 then 1 else 0 end) as 未接收的数量
from
[wenZhangTongJi]
where
year([hTDate])=2008--加上年份
group by [hFrom]
(12 個資料列受到影響)
城市 總数量 未审核的数量 已接收的数量 未接收的数量
-------------------------------------------------- ----------- ----------- ----------- -----------
沧州 2 0 1 1
邯郸 8 1 4 3
(2 個資料列受到影響)
select hFrom AS 城市
, count(1) AS 总数据量
,sum(case hbszt when '0' then 1 else 0 end) as 未审核的数量
,sum(case hbszt when '0' then 1 else 0 end) as 已接收的数量
,sum(case hbszt when '0' then 2 else 0 end) as 未接收的数量
from wenZhangTongJi
where hTdate between '2008-1-1' and '2008-12-31'
group by hfrom
CREATE TABLE [wenZhangTongJi] (
[hId] [int] NOT NULL ,
[hFrom] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hTDate] [datetime] NOT NULL CONSTRAINT [DF_wenZhangTongJi_hTDate] DEFAULT (getdate()),
[hAdmin] [int] NOT NULL ,
[hBszt] [int] NOT NULL CONSTRAINT [DF_wenZhangTongJi_hBszt] DEFAULT (0),
CONSTRAINT [PK_wenZhangTongJi] PRIMARY KEY CLUSTERED
(
[hId]
) ON [PRIMARY]
) ON [PRIMARY]
Insert [wenZhangTongJi]
select 26,N'邯郸','2008-8-1 10:49:25',2,1 union all
select 27,N'邯郸','2008-8-1 10:49:29',2,1 union all
select 28,N'邯郸','2008-8-1 10:49:33',2,2 union all
select 29,N'邯郸','2008-8-1 10:49:37',2,1 union all
select 30,N'邯郸','2008-7-1 10:49:48',2,1 union all
select 31,N'邯郸','2008-7-1 10:49:51',2,2 union all
select 32,N'邯郸','2007-7-16 10:50:04',2,1 union all
select 33,N'邯郸','2007-7-16 10:50:09',2,0 union all
select 34,N'邯郸','2008-8-16 10:59:59',2,2 union all
select 35,N'邯郸','2008-8-1 11:01:12',2,0 union all
select 36,N'沧州','2008-8-1 11:48:24',5,1 union all
select 37,N'沧州','2008-8-1 11:49:25',5,2
go
--drop table [wenZhangTongJi]
select
[hFrom] as 城市,
count(*) as 總数量,
sum(case when hBszt=0 then 1 else 0 end) as 未审核的数量,
sum(case when hBszt=1 then 1 else 0 end) as 已接收的数量,
sum(case when hBszt=2 then 1 else 0 end) as 未接收的数量
from
[wenZhangTongJi]
group by [hFrom]
城市 總数量 未审核的数量 已接收的数量 未接收的数量
-------------------------------------------------- ----------- ----------- ----------- -----------
沧州 2 0 1 1
邯郸 10 2 5 3
(2 個資料列受到影響)