我想在一个特定的表中显示所有的数据条目(加班)，它有3列(日期，数字和评论)。我想把第1、2、3和4周的所有数字加起来。
日期编号注释
01-07-2015 902-07-2015 0
03-07-2015 10
2015-07-07 8
<27 strong>
05-07-2015 806-07-2015 8
07-07-2015 9
08-07-2015 10
09-07-2015 8
10-07-2015 0
11-07-2015 0
<43 strong>
12-07-2015 813-07-2015 8
14-07-2015 9
15-07-2015 10
16-07-2015 8
17-07-2015 0
2015-07-18 0
<43 strong>
下面是我的代码片段。请问我在哪里注册你们的代码?
$query="SELECT name, timestamp, number, comment FROM table WHERE
name = '$valid_user' and timestamp between '$start_date' and '$end_date'";
$params = array($_REQUEST['query']);
$result = sqlsrv_query($conn, $query, $params);
echo "<p><table id='t01' border='1'>
<th style='width: 10px'>Date </th>
<th style='width: 5px'>Number</th>
<th style='width: 100px'>Comment</th>
</tr>";
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
echo "<tr>";
echo '<td><a href=#.php?id='.strip_tags($row['id']).'>' . date( 'd M Y', strtotime(strip_tags($row['timestamp']))). "</td>";
echo "<td>" . strip_tags($row['number']). "</td>";
echo "<td>" . strip_tags($row['comment']). "</td>";
echo '<td><a href="#.php?id=' . strip_tags($row['id']). '">Edit/Delete</a></td>';
echo "</tr>";
echo "</tr>"; }
非常感谢你的帮助。
这是你可以用来按周分组的模板:
DECLARE
@Year int = 2015,
@Month int = 7
DECLARE @StartDate DATE = dateadd(mm, (@Year - 1900) * 12 + @Month - 1, 0)
DECLARE @EndDate DATE = dateadd(mm, (@Year - 1900) * 12 + @Month, -1)
;WITH tally AS (
SELECT (row_number() OVER(ORDER BY (SELECT 0))) - 1 AS N
FROM master.dbo.syscolumns sc1)
SELECT
--d,
wknr
FROM
tally
CROSS APPLY(
SELECT
d = DATEADD(DAY, N, @StartDate),
wknr = DATEPART(wk, DATEADD(DAY, N, @StartDate))) dates
WHERE d < @EndDate
GROUP BY wknr
ORDER BY wknr
编辑
我建议您根据您的需要定制所提供的查询模板。我看到在你的PHP代码中，你需要字段name, timestamp, number, comment，所以查询应该返回它们。
下一步是用新查询替换旧查询。传递相应的日期参数
我想你可以这样写:
;WITH tally AS (
SELECT (row_number() OVER(ORDER BY (SELECT 0))) - 1 AS N
FROM master.dbo.syscolumns sc1)
SELECT
wknr,
number = SUM(t.number)
FROM
tally
CROSS APPLY(
SELECT
d = DATEADD(DAY, N, @StartDate),
wknr = DATEPART(wk, DATEADD(DAY, N, @StartDate))) dates
JOIN table t
ON t.timestamp = d
WHERE d < @EndDate
GROUP BY wknr
ORDER BY wknr