# SQL 求和问题

0

SalaryOneDay  是每个人一天的 工资

```select sum(SalaryOneDay) from [Classes] where CName='小明'
and CreateDate >'2012-08-01' and CreateDate <'2012-09-01'```

1

--使用group by

select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay) from #table
group by cname,convert(varchar(7),createdate,120)

--测试脚本

CREATE TABLE #table(
cid INT IDENTITY(1,1),
cname VARCHAR(16),
salaryOneDay INT,
createdate DATETIME
)

CREATE TABLE #table2(
cname VARCHAR(16),
salary INT,
years VARCHAR(4),
months varchar(2)
)

INSERT INTO #table ( cname, salaryOneDay, createdate ) VALUES ('小明',40,'2012-06-14')
INSERT INTO #table ( cname, salaryOneDay, createdate ) VALUES ('小明',30,'2012-07-14')
INSERT INTO #table ( cname, salaryOneDay, createdate ) VALUES ('小红',30,'2012-08-14')
INSERT INTO #table ( cname, salaryOneDay, createdate ) VALUES ('小红',40,'2012-08-14')

select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay) from #table
group by cname,convert(varchar(7),createdate,120)

INSERT INTO #table2 ( cname, years, months, salary )
select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay) from #table
group by cname,convert(varchar(7),createdate,120)

SELECT * FROM #table2

DROP TABLE #table
DROP TABLE #table2

acepro | 小虾三级 |园豆：1218 | 2012-08-14 16:49

@IT_ZZY: 如果7月 8月算成一个总和，你的日期采用什么方式保存？

select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay) from

`[Classes]  where`
```CName='小明'
and CreateDate >'2012-08-01' and CreateDate <'2012-09-01'```

group by cname,convert(varchar(7),createdate,120)  就可以了啊 ，分组统计。

acepro | 园豆：1218 (小虾三级) | 2012-08-14 17:07

@acepro:  我说的 7 月的 是一条数据 8月份的是一条数据！ 上面我没说清楚！

INSERT INTO 数据表2 ( cname, years, months, salary )
select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay) from 数据表1
group by cname,convert(varchar(7),createdate,120)

acepro | 园豆：1218 (小虾三级) | 2012-08-14 18:22

@TigerSpringLiu:  往别的表 添加数据 我会了

@acepro: n你是要把工资和奖金合并还是单独列出来？

select cname,left( convert(varchar(7),createdate,120),4), right(convert(varchar(7),createdate,120),2),sum(salaryOneDay)  as 工资,SUM('奖金字段名')  as 奖金SUM(‘奖金字段’+‘工资字段’) as 合计from 数据表1 where  CName='小明'   and CreateDate >'2012-08-01' and CreateDate <'2012-09-01'  group by cname,convert(varchar(7),createdate,120)

--建议楼主看下SQL的 group by ,SUM()  ,日期函数及convert转换函数 的语法及用法

acepro | 园豆：1218 (小虾三级) | 2012-08-15 09:05

@acepro:  恩，我Sql 基本功还是不行 啊！

1

select b.CName N, Year(b.createDate) Y, Month(b.createDate) M,
(select sum(a.SalaryOneDay) from [Classes] a where a.CName = b.CName and Year(a.createDate)=Year(b.createDate) and Month(a.createDate) = Month(b.createDate))
from [Classes] b
group by b.CName,Year(b.createDate),Month(b.createDate)

create table #temp0
(
cname nvarchar(50),
SalaryOneDay int,
createdate datetime
);

insert into #temp0
values('小明',50,'2012-07-01')
,('小明',50,'2012-07-01')
,('小明',50,'2012-08-01')
,('小兰',50,'2012-07-01')
,('小兰',50,'2012-08-01')
,('小兰',50,'2012-08-01')

select * from #temp0

select b.CName N, Year(b.createDate) Y, Month(b.createDate) M,
(select sum(a.SalaryOneDay) from #temp0 a where a.CName = b.CName and Year(a.createDate)=Year(b.createDate) and Month(a.createDate) = Month(b.createDate))
from #temp0 b
group by b.CName,Year(b.createDate),Month(b.createDate)

drop table #temp0

TigerSpringLiu | 园豆：196 (初学一级) | 2012-08-14 17:06

至于怎样插入数据 我还是不太明白

@IT_ZZY: 楼下的两位朋友的建议都蛮好

@TigerSpringLiu:  往别的表 添加数据 我会了

1

1

geass.. | 园豆：1819 (小虾三级) | 2012-08-14 17:29

您需要登录以后才能回答，未注册用户请先注册