SELECT temptable.CHE_ID, sum(isnull(temptable.JIAN_ID*danjiatable.DANGJIA,0)) FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID) GROUP BY temptable.CHE_ID
SELECT temptable.CHE_ID, sum(isnull(temptable.JIAN_ID*danjiatable.DANGJIA,0)) [tall] FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID) GROUP BY temptable.CHE_ID
在我的电脑上测试通过,语法是没错的,楼主检查其他地方:create table temptable(che_id int,jian_id int,kuan_id int,gong_id int) create table danjiatable(dangjia int,kuan_id int,gong_id int)SELECT temptable.CHE_ID, [tall]=sum(isnull(temptable.JIAN_ID*danjiatable.DANGJIA,0)) FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID) GROUP BY temptable.CHE_ID
SELECT temptable.CHE_ID, sum(isnull(temptable.JIAN_ID,0)*isnull(danjiatable.DANGJIA,0)) [tall] FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID) GROUP BY temptable.CHE_ID;
谢谢各位,我试一下,再问一个,如果我想加where子句应该放在哪里?
SELECT temptable.CHE_ID, sum(isnull(temptable.JIAN_ID,0)*isnull(danjiatable.DANGJIA,0)) [tall] FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID) where ... GROUP BY temptable.CHE_ID;
select 員工號,sum(件數*單價) from 計件表 a left join 單價表 b on a.款號=b.款號 and a.工序=b.工序 group by 員工號
select a.员工号,总工资=sum(a.件数*b.单价) from 计件表 a inner join 单价表 b on a.款号=b.款号 and a.工序=b.工序 group by a.员工号
--下面是数据测试: --数据测试环境 declare @计件表 table(员工号 varchar(2),款号 varchar(4),工序 int,件数 int) insert into @计件表 select 'A1','101',1,20 union all select 'B1','101',2,10 union all select 'C1','101',3,23 union all select 'A1','102',1,25 union all select 'B1','102',2,33declare @单价表 table(款号 varchar(4),工序 int,单价 int) insert into @单价表 select '101',1,5 union all select '101',2,3 union all select '101',3,8 union all select '102',1,2 union all select '102',2,4--统计结果 select a.员工号,总工资=sum(a.件数*b.单价) from @计件表 a inner join @单价表 b on a.款号=b.款号 and a.工序=b.工序 group by a.员工号
select count(*) from test1 t where [id] in (select id from test1 where (select de from test1 where id='a01') like '%'+rtrim(id)+'%')
FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID)
GROUP BY temptable.CHE_ID
sum(isnull(temptable.JIAN_ID*danjiatable.DANGJIA,0)) [tall]
FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID)
GROUP BY temptable.CHE_ID
create table danjiatable(dangjia int,kuan_id int,gong_id int)SELECT temptable.CHE_ID,
[tall]=sum(isnull(temptable.JIAN_ID*danjiatable.DANGJIA,0))
FROM temptable
LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID)
AND (danjiatable.GONG_ID=temptable.GONG_ID)
GROUP BY temptable.CHE_ID
FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID)
GROUP BY temptable.CHE_ID;
FROM temptable LEFT JOIN danjiatable ON (danjiatable.KUAN_ID=temptable.KUAN_ID) AND (danjiatable.GONG_ID=temptable.GONG_ID)
where ...
GROUP BY temptable.CHE_ID;
员工号 款号 工序 件数
A1 101 1 20
B1 101 2 10
C1 101 3 23
A1 102 1 25
B1 102 2 33单价表
款号 工序 单价
101 1 5
101 2 3
101 3 8
102 1 2
102 2 4
有上面两个表,要算出每个员工的计件总工资,查询语句应怎样写?按照上面的写法执行结果不对啊
from 计件表 a inner join 单价表 b on a.款号=b.款号 and a.工序=b.工序
group by a.员工号
--数据测试环境
declare @计件表 table(员工号 varchar(2),款号 varchar(4),工序 int,件数 int)
insert into @计件表
select 'A1','101',1,20
union all select 'B1','101',2,10
union all select 'C1','101',3,23
union all select 'A1','102',1,25
union all select 'B1','102',2,33declare @单价表 table(款号 varchar(4),工序 int,单价 int)
insert into @单价表
select '101',1,5
union all select '101',2,3
union all select '101',3,8
union all select '102',1,2
union all select '102',2,4--统计结果
select a.员工号,总工资=sum(a.件数*b.单价)
from @计件表 a inner join @单价表 b on a.款号=b.款号 and a.工序=b.工序
group by a.员工号
(select de from test1 where id='a01') like '%'+rtrim(id)+'%')