求助，一个SQL语句（急）(200分)

J

jiefeij

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #1

如何实现
表
FIELD1 FIELD2 FIELD3
0001 2002-09-02 78.4
0002 2002-09-02 178.4
0001 2002-08-02 100.4
我现在想按FIELD1统计所有FIELD3的数据，再统计当月数据
然后写到一张表
表B
FIELD1 FIELD3 FIELD4：FIELD4为该月份的统计额
请问SQL语句怎么写？

 

墙

墙头草

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #2

select sum(field3) from biao where field2='2002-09-02'
ADOQuery.FieldByName('field3').AsString;//取出field3
ADOQuery.sql.text := 'insert into table(....) values(.......)'

 

J

jiefeij

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #3

TO 墙头草:
你可能有点没有明白我的意思。
我先统计所有FIELD3是没有时间限制，是所有记录
应该为SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
将结果写入B表的FIELD3字段中
然后我统计当月的 SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
WHERE （field2的时间在该月份内）
然后将对应结果写入B表中的FIELD4中

 

D

dhl2001

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #4

你说的如此明白了，
还不快回去做出来

 

J

jiefeij

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #5

我知道这样做的，但是SQL语句怎么写呀

 

S

snjat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #6

SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
将结果写入B表的FIELD3字段中
insert into b (FIELD3) values(SUM（FIELD3）)
然后我统计当月的 SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
WHERE （field2的时间在该月份内）
update b set field4=SUM（FIELD3） where FIELD1 =FIELD1

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #7

Select field1,tmp as field3,(select sum(field3) as field4
from table1
where year(field2)=2002 and
month(field2)=9 and
field1=AAA.field1) as field4
From (select field1,sum(field3) as tmp
from table1
group by field1) AAA

MS SQL下的写法

 

L

lodgue

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-24

• #8

drop Table #b
(select
tbA.Field1,
(Select Sum(Field3) from a) as Field3,
(Select 0) as Field4
into #b
from (select DISTINCT field1 from a) as tbA)
declare @MonthSum float
Select @MonthSum=Sum(Field3)from a as tbA where Field2 like '2002-09%'
insert into #b (Field1,Field3,Field4)
Values('',0,@MonthSum)

 

J

jiefeij

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #9

没有简单一点的方法吗？这样的执行效率会很低吧？

 

W

WebExplorer

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #10

是不是就一个SQL语句?
insert into B as
SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
union all
SELECT SUM（FIELD3） FROM A GROUP BY FIELD1
WHERE （field2的时间在该月份内）

 

E

ericimex01

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #11

insert into b
select field1,sum(field3),sum(case when year(field2)=@year and month(field2)=@month then field3 else 0 end)
from a
group by field1

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #12

根本就不需要一个临时表，一句SQL语句搞定

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #13

select d1.field1,sum(d1.field3) as field3,(select sum(field3) from a d2 where d1,field1=d1.field1 and month(field2)=month(getdate())
and year(field2)=year(getdate())) as field4
from a d1
group by field1
我用SQL SERVER2000 有问题可再讨论，但一定得给分哟

 

J

jiefeij

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #14

谢谢大家！采用ericimex01的方法了。