SQL 求和问题

Question

SalaryOneDay  是每个人一天的 工资点击刷新之后 将上面那个表的数据 按 名字 年份 月份  插入到  下面那个表中怎样插啊select sum(SalaryOneDay) from [Classes] where CName='小明'    and CreateDate >'2012-08-01' and CreateDate <'2012-09-01'问题是 日期怎样解决 啊！问题补充：我现在要做的是 将7月份的工资 和8月份的工资总和 加到另外一张表中

Answer 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