sql 2000 表info结构如下
id mingcheng shijian shuliang
1 电动机 2008-2-4 23
2 电动机1 2008-1-8 242
3 电动机2 2008-9-4 166
4 电动机3 2008-10-4 865
5 电动机4 2008-12-4 756
.
.
.用什么sql语句可以查出下面的表上半年 下半年
(数量合计) (数量合计)
想通过分时间段来统计总数,通过一句sql查询。可不可以实现呢~~~
id mingcheng shijian shuliang
1 电动机 2008-2-4 23
2 电动机1 2008-1-8 242
3 电动机2 2008-9-4 166
4 电动机3 2008-10-4 865
5 电动机4 2008-12-4 756
.
.
.用什么sql语句可以查出下面的表上半年 下半年
(数量合计) (数量合计)
想通过分时间段来统计总数,通过一句sql查询。可不可以实现呢~~~
sum(case when shijian < '2008-7-1' then shuliang else 0 end ) 上半年,
sum(case when shijian >= '2008-7-1' then shuliang else 0 end ) 下半年
from info
go
create table info([id] int,[mingcheng] varchar(10),[shijian] varchar(10),[shuliang] int)
insert info select 1,'电动机','2008-2-4',23
union all select 2,'电动机1','2008-1-8',242
union all select 3,'电动机2','2008-9-4',166
union all select 4,'电动机3','2008-10-4',865
union all select 5,'电动机4','2008-12-4',756
goselect
sum(case when shijian < '2008-7-1' then shuliang else 0 end ) 上半年,
sum(case when shijian >= '2008-7-1' then shuliang else 0 end ) 下半年
from info
/*
上半年 下半年
----------- -----------
1886 166(1 行受影响)*/
year(shijian) as [年份],
sum(case when month(shijian) between 1 and 6 then shuliang else 0 end) as [上半年],
sum(case when month(shijian) between 7 and 12 then shuliang else 0 end) as [下半年]
from
info
group by
year(shijian)
from
(
select sum(shuliang) as '上半年'
from tb
where shijian between '2008-01-01' and '2008-07-01'
)T1,
(
select sum(shuliang) '下半年'
from tb
where shijian between ''2008-07-01'' and '2009-01-01'
)T2
sum(case when month(shijian)>=7 then shuliang else 0 end) 下半年 from tb group by year(shijian)
if object_id('info') is not null drop table info
go
create table info([id] int,[mingcheng] varchar(10),[shijian] datetime,[shuliang] int)
insert info select 1,'电动机','2008-2-4',23
union all select 2,'电动机1','2008-1-8',242
union all select 3,'电动机2','2008-9-4',166
union all select 4,'电动机3','2008-10-4',865
union all select 5,'电动机4','2008-12-4',756
goselect
sum(case when shijian < '2008-7-1' then shuliang else 0 end ) 上半年,
sum(case when shijian >= '2008-7-1' then shuliang else 0 end ) 下半年
from info
/*
上半年 下半年
----------- -----------
265 1787(1 行受影响)*/
select sum(case when shijian <='2008-06-30' then shuliang else 0 end) as 上半年數量,
sum(case when shijian >'2008-06-30' then shuliang else 0 end) as 下半年數量
From info
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-20 15:48:31
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(id INT,mingcheng NVARCHAR(4),shijian datetime,shuliang INT)
Go
INSERT INTO tb
SELECT 1,'电动机','2008-2-4',23 UNION ALL
SELECT 2,'电动机1','2008-1-8',242 UNION ALL
SELECT 3,'电动机2','2008-9-4',166 UNION ALL
SELECT 4,'电动机3','2008-10-4',865 UNION ALL
SELECT 5,'电动机4','2008-12-4',756 UNION ALL
SELECT 6,'电动机3','2009-10-4',865
GOSELECT * FROM TB
select
sum(case when shijian < cast(year(shijian)as varchar)+'-7-1' then shuliang else 0 end ) 上半年,
sum(case when shijian >= cast(year(shijian)as varchar)+'-7-1' then shuliang else 0 end ) 下半年
from tb
group by year(shijian)265 1787
0 865
SELECT
ISNULL(SUM(CASE WHEN DATEPART(Year,shijian)='2008' AND DATEPART(MONTH,shijian) IN ('1','2','3','4','5','6') THEN shuliang END),0)AS '上半年',
ISNULL(SUM(CASE WHEN DATEPART(Year,shijian)='2008' AND DATEPART(MONTH,shijian) IN ('7','8','9','10','11','12') THEN shuliang END),0)AS '下半年'
FROM info WITH (NOLOCK)