SalaryOneDay 是每個人一天的 工資點擊刷新之后 將上面那個表的數據 按 名字 年份 月份 插入到 下面那個表中怎樣插啊select sum(SalaryOneDay) from [Classes] where CName='小明'
and CreateDate >'2012-08-01' and CreateDate <'2012-09-01'問題是 日期怎樣解決 ??！問題補充：我現在要做的是 將7月份的工資 和8月份的工資總和 加到另外一張表中
2 回答
溫溫醬
TA貢獻1752條經驗 獲得超4個贊
--使用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
- 2 回答
- 0 關注
- 660 瀏覽
添加回答
舉報
0/150
提交
取消