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