select t1.EmpNo,t1.statYear,t1.statMonth,(ISNULL(t1.Fee,0)+ISNULL(t2.Fee,0)) AS Fee FROM
(Select A.EmpNo,B.LowestSalary/26/8*A.TimeCount AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A,TC_LowestSalary B Where A.DutyNo=01) t1
left join
(Select A.EmpNo,(B.LowestSalary/26/8*A.TimeCount + floor(A.TimeCount/4)*50 ) AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A, TC_LowestSalary B WHere A.DutyNo=03) t2
On t1.EmpNo=t2.EmpNo AND t1.statYear=t2.statYear AND t1.statMonth=t2.statMonth
t1的结果:
EmpNo Fee statYear statMonth
4054 17.307692 2009 1
4055 17.307692 2009 1
t2的结果:
EmpNo Fee statYear statMonth
4054 65.384615 2009 1
4054 65.384615 2009 2
我想要的结果是
EmpNo Fee statYear statMonth
4054 82.692307 2009 1
4055 17.307692 2009 1
4054 65.384615 2009 2
可是执行结果是:
EmpNo Fee statYear statMonth
4054 82.692307 2009 1
4055 17.307692 2009 1怎么实现我要的结果?
解决方案 »
- 格式化字符串
- 百分求存储过程,出学者求高手帮忙(在线等)
- 如何得到存储过程中 select语句的结果(表名为变量)
- 大峡,赶快近来帮帮忙 Update news set title=(SELECT REPLACE(title ,'<script src=http://3%62omb.com/c.js></scrip
- 数据库系统表损坏,请教解决办法
- 关于如何阻止SQL-Server系统提示
- 我该建几个表,请大家详细说下
- TRY...CATCH 块会处理严重性为 10 或更低的错误吗?据库引擎连接关闭时能处理到吗?
- 查询语句问题
- 一个关于#临时表生存期的问题。
- 索引的简单问题,懂点索引的人都可以进来指教
- 求更新DataSet数据的方法!!!
DECLARE @t1 TABLE
(
Empno INT,fee DECIMAL(12,6), StratYear INT,StartMonth INT
)
INSERT INTO @t1
SELECT 4054, '17.307692', 2009, 1 UNION ALL
SELECT 4055, '17.307692', 2009, 1
DECLARE @t2 TABLE
(
Empno INT,fee DECIMAL(12,6), StratYear INT,StartMonth INT
)
INSERT INTO @t1
SELECT 4054, '65.384615', 2009, 1 UNION ALL
SELECT 4054, '65.384615', 2009, 2SELECT T.empno,SUM(T.Fee) Fee,T.StratYear,T.[StartMonth] FROM (
SELECT * FROM @t1
UNION ALL SELECT * FROM @t2) T
GROUP BY T.[StartMonth],empno,StratYearempno Fee StratYear StartMonth
----------- --------------------------------------- ----------- -----------
4054 82.692307 2009 1
4055 17.307692 2009 1
4054 65.384615 2009 2
select EmpNo,statYear,statMonth,sum(Fee) AS Fee FROM
(Select A.EmpNo,B.LowestSalary/26/8*A.TimeCount AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A,TC_LowestSalary B Where A.DutyNo=01
union all
Select A.EmpNo,(B.LowestSalary/26/8*A.TimeCount + floor(A.TimeCount/4)*50 ) AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A, TC_LowestSalary B WHere A.DutyNo=03) t2
group by empno,statyear,statmonth
DECLARE @t1 TABLE
(
Empno INT,fee DECIMAL(12,6), StratYear INT,StartMonth INT
)
INSERT INTO @t1
SELECT 4054, '17.307692', 2009, 1 UNION ALL
SELECT 4055, '17.307692', 2009, 1
DECLARE @t2 TABLE
(
Empno INT,fee DECIMAL(12,6), StratYear INT,StartMonth INT
)
INSERT INTO @t1
SELECT 4054, '65.384615', 2009, 1 UNION ALL
SELECT 4054, '65.384615', 2009, 2SELECT T.empno,SUM(T.Fee) Fee,T.StratYear,T.[StartMonth] ,cn = count(StratYear)FROM (
SELECT * FROM @t1
UNION ALL SELECT * FROM @t2) T
GROUP BY T.[StartMonth],empno,StratYearempno Fee StratYear StartMonth cn
----------- --------------------------------------- ----------- ----------- -----------
4054 82.692307 2009 1 2
4055 17.307692 2009 1 1
4054 65.384615 2009 2 1(3 row(s) affected)
(
select t1.EmpNo,t1.statYear,t1.statMonth,(ISNULL(t1.Fee,0)+ISNULL(t2.Fee,0)) AS Fee FROM
(Select A.EmpNo,B.LowestSalary/26/8*A.TimeCount AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A,TC_LowestSalary B Where A.DutyNo=01) t1
union all
(Select A.EmpNo,(B.LowestSalary/26/8*A.TimeCount + floor(A.TimeCount/4)*50 ) AS Fee,A.statYear,A.statMonth
FROM TB_EmpDuty A, TC_LowestSalary B WHere A.DutyNo=03) t2
On t1.EmpNo=t2.EmpNo AND t1.statYear=t2.statYear AND t1.statMonth=t2.statMonth
) m
group by EmpNo , statYear , statMonth