如果有一张表如下
code type time value
1 1 2000-1-1 10
1 2 2000-1-1 5
1 1 2000-1-2 12
1 2 2000-1-2 6
2 2 2000-1-1 2
2 3 2000-1-1 6
2 2 2000-1-2 2
2 3 2000-1-2 7type=1代表总量,type>1的代表分量
现在对上面的表每天按总的量进行统计,如果type=1的就用对应的值,如果没有type=1的code,就把code当天所以的分量相加统计完的表中内容为
code time value
1 2000-1-1 10
1 2000-1-2 12
2 2000-1-1 8
2 2000-1-2 9
怎么写查询的SQL语句啊,谢谢各位了
code type time value
1 1 2000-1-1 10
1 2 2000-1-1 5
1 1 2000-1-2 12
1 2 2000-1-2 6
2 2 2000-1-1 2
2 3 2000-1-1 6
2 2 2000-1-2 2
2 3 2000-1-2 7type=1代表总量,type>1的代表分量
现在对上面的表每天按总的量进行统计,如果type=1的就用对应的值,如果没有type=1的code,就把code当天所以的分量相加统计完的表中内容为
code time value
1 2000-1-1 10
1 2000-1-2 12
2 2000-1-1 8
2 2000-1-2 9
怎么写查询的SQL语句啊,谢谢各位了
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] int,[type] int,[time] datetime,[value] int)
insert [tb]
select 1,1,'2000-1-1',10 union all
select 1,2,'2000-1-1',5 union all
select 1,1,'2000-1-2',12 union all
select 1,2,'2000-1-2',6 union all
select 2,2,'2000-1-1',2 union all
select 2,3,'2000-1-1',6 union all
select 2,2,'2000-1-2',2 union all
select 2,3,'2000-1-2',7
---查询---
select code,[time],value from tb where type=1
union all
select code,[time],sum(value) as value from tb t
where not exists(select 1 from tb where code=t.code and type=1)
group by code,[time]---结果---
code time value
----------- ----------------------- -----------
1 2000-01-01 00:00:00.000 10
1 2000-01-02 00:00:00.000 12
2 2000-01-01 00:00:00.000 8
2 2000-01-02 00:00:00.000 9(4 行受影响)
use PracticeDB
go
if object_id('tb') is not null
drop table tb
go
create table tb (code int,type int,time date,value int)
insert into tb
select 1, 1, '2000-1-1', 10 union all
select 1, 2, '2000-1-1', 5 union all
select 1, 1, '2000-1-2', 12 union all
select 1, 2, '2000-1-2', 6 union all
select 2, 2, '2000-1-1', 2 union all
select 2, 3, '2000-1-1', 6 union all
select 2, 2, '2000-1-2', 2 union all
select 2, 3, '2000-1-2', 7select code,time , value= case when MIN(type)=1 then (select value from tb
where a.code=code and a.time=time and type=1)
else SUM(value) end
from tb a
group by code,time code time value
1 2000-01-01 10
2 2000-01-01 8
1 2000-01-02 12
2 2000-01-02 9