被困扰住了，高手们进来帮下忙！

[rockyvan]

表A：
Name,EmpNo,CardNo,HourlyWage
XXXX,.....
YYYY,.....
表B：
Name,EmpNo,CardNo,Date,1XHour,2XHour,3XHour
XXXX,.....
YYYY,.....
XXXX,.....
YYYY,.....

现在要统计：
Name,EmpNo,CardNo,Month,Sum(1XHour)*HourlyWage，Sum(2XHour)*HourlyWage，Sum(3XHour)*HourlyWage
请问该如何写这个语句啊？

深夜提问，希望明天能有答案。这个问题有点急。

[Limpire]

select a.Name,a.EmpNo,a.CardNo,Year=year(b.Date),Month=month(b.Date),Sum(b.[1XHour]*a.HourlyWage),Sum(b.[2XHour]*a.HourlyWage),Sum(b.[3XHour]*a.HourlyWage)
from 表A as a join 表B as b on a.Name=b.Name and a.EmpNo=b.EmpNo and a.CardNo=b.CardNo
where ?
group by a.Name,a.EmpNo,a.CardNo,year(b.Date),month(b.Date)

[kelph]

select a.Name,a.EmpNo,a.CardNo,[Month]=convert(varchar(7),Date,120),
Sum(b.[1XHour]*a.HourlyWage),Sum(b.[2XHour]*a.HourlyWage),Sum(b.[3XHour]*a.HourlyWage)
from 表A a,表B b where a.Name=b.Name and a.EmpNo=b.EmpNo and a.CardNo=b.CardNo
group by a.Name,a.EmpNo,a.CardNo,convert(varchar(7),Date,120)

[kelph]

select a.Name,a.EmpNo,a.CardNo,[Month]=convert(varchar(7),b.Date,120),
Sum(b.[1XHour]*a.HourlyWage),Sum(b.[2XHour]*a.HourlyWage),Sum(b.[3XHour]*a.HourlyWage)
from 表A a,表B b where a.Name=b.Name and a.EmpNo=b.EmpNo and a.CardNo=b.CardNo
group by a.Name,a.EmpNo,a.CardNo,convert(varchar(7),b.Date,120)

[-狙击手-]

就是固定的列呀

select a.Name,
       a.EmpNo,
       a.CardNo,
       [Month]=convert(varchar(7),b.Date,120),
       Sum(b.[1XHour]*a.HourlyWage),
       Sum(b.[2XHour]*a.HourlyWage),
       Sum(b.[3XHour]*a.HourlyWage)
from 表A a
left join 表B b 
on a.Name=b.Name and a.EmpNo=b.EmpNo and a.CardNo=b.CardNo
group by a.Name,
         a.EmpNo,
         a.CardNo,
         convert(varchar(7),b.Date,120)

[rockyvan]

你們真早！先囘再看。看完結帖。

[lhsxsh]

select a.Name,a.EmpNo,a.CardNo,[Month]=convert(varchar(7),b.Date,120),
Sum(b.[1XHour]*a.HourlyWage),Sum(b.[2XHour]*a.HourlyWage),Sum(b.[3XHour]*a.HourlyWage)
from 表A a,表B b where a.Name=b.Name and a.EmpNo=b.EmpNo and a.CardNo=b.CardNo
group by a.Name,a.EmpNo,a.CardNo,convert(varchar(7),b.Date,120)