M
MikeZ
Unregistered / Unconfirmed
GUEST, unregistred user!
to oceanwave:
改造后的表结构建议如下:
NAME 名字
BEGINDATE 开始日期
ENDDATE 完成日期
WORK 工作量
SQL语句如下:
SELECT Month([ENDDATE]) AS [Month], NAME, Sum(WORK) AS WORKSUM
FROM 你的表
GROUP BY Month([ENDDATE]), NAME
======================================================
基本同意你的观点,但如果工作量是一月来计算, 工作量的字段可以不要
NAME 名字
BEGINDATE 开始日期
ENDDATE 完成日期
END 完工标志(因为未完工前, ENDDATE无法确定,由他,可以把ENDDATE看成当前日起)
sql:
select Month(ENDDATE) AS [Month], NAME, IIF(END, DATEDIFF('M', BEGINDATE, ENDDATE), 0) AS WORK
FROM TABLEWORK
WHERE YEAR(ENDDATE) = QUERYYEAR AND MONTH(ENDDATE) = QUERYMONTH
如果每月工作量不同:
SELECT MONTH(A.ENDDATE) AS [MONTH], A.NAME, IIF(END, (SELECT SUM(B.WORK) FROM TABLEWORK B WHERE B.ENDDATE BETWEEN A.BEGINDATE AND A.ENDDATE AND B.NAME=A.NAME), 0) AS MWORK
FROM TABLEWORK A
WHERE YEAR(ENDDATE) = QUERYYEAR AND MONTH(ENDDATE) = QUERYMONTH
改造后的表结构建议如下:
NAME 名字
BEGINDATE 开始日期
ENDDATE 完成日期
WORK 工作量
SQL语句如下:
SELECT Month([ENDDATE]) AS [Month], NAME, Sum(WORK) AS WORKSUM
FROM 你的表
GROUP BY Month([ENDDATE]), NAME
======================================================
基本同意你的观点,但如果工作量是一月来计算, 工作量的字段可以不要
NAME 名字
BEGINDATE 开始日期
ENDDATE 完成日期
END 完工标志(因为未完工前, ENDDATE无法确定,由他,可以把ENDDATE看成当前日起)
sql:
select Month(ENDDATE) AS [Month], NAME, IIF(END, DATEDIFF('M', BEGINDATE, ENDDATE), 0) AS WORK
FROM TABLEWORK
WHERE YEAR(ENDDATE) = QUERYYEAR AND MONTH(ENDDATE) = QUERYMONTH
如果每月工作量不同:
SELECT MONTH(A.ENDDATE) AS [MONTH], A.NAME, IIF(END, (SELECT SUM(B.WORK) FROM TABLEWORK B WHERE B.ENDDATE BETWEEN A.BEGINDATE AND A.ENDDATE AND B.NAME=A.NAME), 0) AS MWORK
FROM TABLEWORK A
WHERE YEAR(ENDDATE) = QUERYYEAR AND MONTH(ENDDATE) = QUERYMONTH