一个表中有日期型字段如何对该字段实现以年月分组统计？SQL语句如何写？(50分)

I

Ironhero

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #1

例如：有表Mytable，字段有：Field1(销售金额）、Field2(销售日期)，现在我要实现某年
某月共销售多少金额？怎样才能实现？
1、在本地数据库怎样写？
2、Access数据库如何写？
3、SQL Server又如何写？
谢谢。

 

P

players

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #2

用DATEPART函数啊！
SELECT SUM（FIELDS1） FROM MYTABLE
WHERE DATEPART（YEAR，FIELDS2）=？
AND DATEPART（MONTH，FIELDS2）=？

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #3

SELECT EXTRACT(YEAR FROM FIELD2)*100+EXTRACT(MONTH FROM FIELD2) AS YEARMONTH,SUM(FIELD1)
FROM MYTABLE
GROUP BY EXTRACT(YEAR FROM FIELD2)*100+EXTRACT(MONTH FROM FIELD2)

列出年月及其对应的销售金额

 

I

Ironhero

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #4

如果按上面的写我又要带参数，我想要的自动统计全年每月销售额。我需要的是这样：
select sum(Field1) 月销售额，Field2 from mytable group by 如何写？
能办得到吗？

 

P

players

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #5

select datepart（fields2） ，sum（fields1）
from mytable
where datepart（year，fields2）=？
group by datepart（month，fields2）

 

I

Ironhero

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-20

• #6

to players：我是paradox表
to QuickSilver：报错：Capability not supported.

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-21

• #7

1) PARADOX表
无法用一条sql语句达到要求
SELECT EXTRACT(YEAR FROM FIELD2)*100+EXTRACT(MONTH FROM FIELD2) AS YEARMONTH,FIELD1
FROM MYTABLE
将结果存入一临时paradox表temp
SELECT YEARMONTH,SUM(FIELD1)
FROM TEMP
GROUP BY YEARMONTH

2) ACCESS的写法（已验证）

SELECT year(FIELD2)*100+month(field2) as yearmonth,sum(field1) as je
from mytable group by year(FIELD2)*100+month(field2)

3)SQL SERVER
用ado连接SQL SERVER
SQL语句与ACCESS的相同

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-21

• #8

使用Ado连接paradox表
Connection里选择Microsoft Jet4.0 Ole DB->选择paradox所在路径(只是路径)，
然后在所有里面的Extended Properties为Paradox 7.x
再使用ADOQUERY，就可以使用下面的SQL语句了

SELECT year(FIELD2)*100+month(field2) as yearmonth,sum(field1) as je
from mytable group by year(FIELD2)*100+month(field2)

 

I

Ironhero

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-31

• #9

多人接受答案了。