为一周中的一天中的每个计数（*）创建一列

这是一种方法。假设此表和样本数据：

USE tempdb;
GO
CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE);

INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150628'),('t0001','Tod','20150628'),
  ('b0002','Ben','20150629'),('b0002','Ben','20150629'),('b0002','Ben','20150628');

然后使用变量或参数来指示您关心的星期：

DECLARE @dt DATE = '20150702';

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt(dt) AS
(
  SELECT TOP (7) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @dt)
  FROM sys.all_objects
  ORDER BY [object_id]
),
x AS 
(
  SELECT 
    s.Name, dt.dt, 
    dd = DATEDIFF(DAY, @dt, dt.dt), 
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  LEFT OUTER JOIN dbo.splunge AS s 
  ON s.Date = dt.dt
  GROUP BY Grouping SETS((s.Name),(s.Name,dt.dt))
)
SELECT 
  Name, 
  [S] = COALESCE(MAX(CASE dd WHEN 0 THEN c END), '-'),
  [M] = COALESCE(MAX(CASE dd WHEN 1 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 2 THEN c END), '-'),
  [W] = COALESCE(MAX(CASE dd WHEN 3 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 4 THEN c END), '-'),
  [F] = COALESCE(MAX(CASE dd WHEN 5 THEN c END), '-'),
  [S] = COALESCE(MAX(CASE dd WHEN 6 THEN c END), '-'),
  TOTAL = MAX(CASE WHEN dt IS NULL THEN c END)
FROM x
WHERE Name IS NOT NULL
GROUP BY Name;

结果：

Name    S   M   T   W   T   F   S   TOTAL
------  --- --- --- --- --- --- --- -----
Ben     1   2   -   -   -   -   -   3
Tod     2   5   -   -   -   -   -   7

另一种方式：

DECLARE @dt DATE = '20150702';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Name, dw = DATEPART(WEEKDAY, Date)
  FROM dbo.splunge
  WHERE Date >= @dt AND Date < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT Name, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 
  GROUP BY GROUPING SETS((Name),(Name,dw))
)
SELECT Name, 
  [S] = COALESCE([1],'-'),
  [M] = COALESCE([2],'-'),
  [T] = COALESCE([3],'-'),
  [W] = COALESCE([4],'-'),
  [T] = COALESCE([5],'-'),
  [F] = COALESCE([6],'-'),
  [S] = COALESCE([7],'-'),
  TOTAL = [8]
FROM x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt;

不要忘记清理：

DROP TABLE dbo.splunge;

您可以在 SQL Server 中使用以下方法将数据转换为您要查找的格式。您也可以使用 PIVOT 运算符，但我更喜欢写出 CASE 语句，因为我发现语法更清晰（在我检查过的所有情况下，它们总是解析为相同的查询计划）。

基于 Aaron 指出 DATENAME 的缺点的更新答案。感谢您指出这一点，亚伦！我们主要使用基于英语的数据库，但这显然是一个更广泛的愚蠢假设。

-- Load test data
CREATE TABLE #reportDates (employeeName VARCHAR(100) NOT NULL, reportDate DATE NOT NULL)
GO
INSERT INTO #reportDates (employeeName, reportDate)
VALUES
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-28'),
    ('Tod', '2015-6-28'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-28')

-- Build the report for each employee
DECLARE @reportStartDate DATE = '2015-6-28' /* Sunday, the start date of the report */
SELECT employeeName,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 0 THEN 1 END) AS S,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 1 THEN 1 END) AS M,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 2 THEN 1 END) AS T,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 3 THEN 1 END) AS W,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 4 THEN 1 END) AS R,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 5 THEN 1 END) AS F,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 6 THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM #reportDates
WHERE reportDate BETWEEN @reportStartDate/*Sunday, report start date*/ AND DATEADD(dd, 6, @reportStartDate)/* Saturday, report end date */
GROUP BY employeeName

作为参考，这是原始回复（如果不使用英语，则不正确！）：

-- Load test data
CREATE TABLE #reportDates (employeeName VARCHAR(100) NOT NULL, reportDate DATE NOT NULL)
GO
INSERT INTO #reportDates (employeeName, reportDate)
VALUES
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-28'),
    ('Tod', '2015-6-28'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-28')

-- Build the report for each employee
SELECT employeeName,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Sunday' THEN 1 END) AS S,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Monday' THEN 1 END) AS M,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Tuesday' THEN 1 END) AS T,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Wednesday' THEN 1 END) AS W,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Thursday' THEN 1 END) AS R,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Friday' THEN 1 END) AS F,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Saturday' THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM #reportDates
WHERE reportDate BETWEEN '2015-6-28'/*Sunday, report start date*/ AND '2015-7-4'/* Saturday, report end date */
GROUP BY employeeName

您也可以考虑使用该DATEPART函数，但请注意，它取决于DATEFIRST配置方式，因此可能并不总是在不同数据库或配置更改时返回相同的结果：

-- Compare DATENAME vs. DATEPART for day of the week
SELECT DATENAME(dw, '2015-6-29')
-- Monday
GO
SELECT DATEPART(dw,'2015-6-29')
-- 2
GO

-- Reconfigure the "first" day of the week to be Thursday
SET DATEFIRST 4
GO

-- Compare DATENAME vs. DATEPART for day of the week after setting DATEFIRST
SELECT DATENAME(dw,'2015-6-29')
-- Monday, which remains consistent
GO
SELECT DATEPART(dw,'2015-6-29')
-- 5, which means that DATEPART is not guaranteed for this usage!
GO

-- Reconfigure the "first" day of the week to Sunday (the default for US English)
SET DATEFIRST 7
GO

我不知道在 SQL Server 上是否相等，我在 MySQL 中使用该WEEKDAY函数进行了此操作。

表UserRecords：

询问：

    SELECT
        user,
        count(case when WEEKDAY(qDate)=6 THEN qDate END) as S,
        count(case when WEEKDAY(qDate)=0 THEN qDate END) as M,
        count(case when WEEKDAY(qDate)=1 THEN qDate END) as T,
        count(case when WEEKDAY(qDate)=2 THEN qDate END) as W,
        count(case when WEEKDAY(qDate)=3 THEN qDate END) as R,
        count(case when WEEKDAY(qDate)=4 THEN qDate END) as F,
        count(case when WEEKDAY(qDate)=5 THEN qDate END) as S,
        count(id) as TOTAL
    FROM UserRecords
    WHERE qDate BETWEEN '2015-06-26' AND '2015-06-29'
    GROUP BY user
    ORDER BY user DESC;

结果：