sql - 如何根据相同的日期范围计算不同表中的行数

Question

编辑 尽管接受的答案与我在写这个问题时所寻找的不符，但它确实显示了我已经拥有的更好的方式(这是他们想要的)，生产表中的数据是错误的，这意味着数字是错误的。

我正在努力在这篇文章的底部创建 sql 语句来创建报告。我已经包含了用于创建表和测试数据的语句。所以在这里开始是picture_stats的Creation语句

CREATE TABLE [picture_stats](
    [PICTURE_STATS_ID] [int] NULL,
    [USER_NAME] [varchar](30) NULL,
    [DATE_TIME] [datetime] NULL,
    [SIZE] [float] NULL,
    [CLICK_COUNT] [int] NULL
) ON [PRIMARY]
GO

将数据放入picture_stats的插入语句

INSERT INTO [picture_stats]
           ([PICTURE_STATS_ID]
           ,[USER_NAME]
           ,[DATE_TIME]
           ,[SIZE]
           ,[CLICK_COUNT])
VALUES
    (1 ,'A','2015-05-18'75,18),
    (2 ,'A','2015-05-18'13,18),
    (3 ,'A','2015-05-18'42,16),
    (4 ,'A','2015-05-18'59,16),
    (5 ,'A','2015-05-18'46,14),
    (6 ,'A','2015-05-18'64,16),
    (7 ,'A','2015-05-18'87,13),
    (8 ,'A','2015-05-18'84,14),
    (9 ,'A','2015-05-18'33,16),
    (10,'A','2015-05-18'59,14),
    (11,'B','2015-05-19'10,17),
    (12,'B','2015-05-19'44,18),
    (13,'B','2015-05-19'29,14),
    (14,'B','2015-05-19'65,19),
    (15,'B','2015-05-19'10,15),
    (16,'B','2015-05-19'55,18),
    (17,'B','2015-05-19'81,11),
    (18,'B','2015-05-19'29,11),
    (19,'B','2015-05-19'58,19),
    (20,'B','2015-05-19'20,17),
    (21,'C','2015-05-20'35,16),
    (22,'C','2015-05-20'70,18),
    (23,'C','2015-05-20'30,13),
    (24,'C','2015-05-20'33,13),
    (25,'C','2015-05-20'43,19),
    (26,'C','2015-05-20'10,15),
    (27,'C','2015-05-20'33,13),
    (28,'C','2015-05-20'23,12),
    (29,'C','2015-05-20'35,18),
    (30,'C','2015-05-20'58,19)
GO

数据的表 View 。

ID USER_NAME DATE_TIME  SIZE CLICK_COUNT
-- --------- ---------- ---- -----------
1  A         2015-05-18 75   18
2  A         2015-05-18 1    18
3  A         2015-05-18 42   16
4  A         2015-05-18 59   16
5  A         2015-05-18 46   14
6  A         2015-05-18 64   16
7  A         2015-05-18 87   13
8  A         2015-05-18 84   14
9  A         2015-05-18 33   16
10 A         2015-05-18 59   14
11 B         2015-05-19 10   17
12 B         2015-05-19 44   18
13 B         2015-05-19 29   14
14 B         2015-05-19 65   19
15 B         2015-05-19 100  15
16 B         2015-05-19 55   18
17 B         2015-05-19 81   11
18 B         2015-05-19 29   11
19 B         2015-05-19 58   19
20 B         2015-05-19 20   17
21 C         2015-05-20 35   16
22 C         2015-05-20 7    18
23 C         2015-05-20 30   13
24 C         2015-05-20 33   13
25 C         2015-05-20 4    19
26 C         2015-05-20 100  15
27 C         2015-05-20 33   13
28 C         2015-05-20 23   12
29 C         2015-05-20 35   18
30 C         2015-05-20 58   19

第二个表 picture_comment_stats 可以这样创建:

CREATE TABLE [picture_comment_stats](
    [PICTURE_STATS_ID] [int] NULL,
    [USER_NAME] [varchar](30) NULL,
    [DATE_TIME] [datetime] NULL,
    [CLICK_COUNT] [int] NULL,
    [LIKES] [int] NULL,
) ON [PRIMARY]
GO

在数据中编写脚本:

INSERT INTO [picture_comment_stats]
    ([PICTURE_STATS_ID]
    ,[USER_NAME]
    ,[DATE_TIME]
    ,[CLICK_COUNT]
    ,[LIKES])
VALUES
    (1 ,'X','2015-05-18',75,18),
    (2 ,'X','2015-05-18',1 ,18),
    (3 ,'X','2015-05-18',42,16),
    (4 ,'X','2015-05-18',59,16),
    (9 ,'X','2015-05-19',34,16),
    (10,'X','2015-05-19',57,14),
    (11,'Y','2015-05-19',11,17),
    (12,'Y','2015-05-19',44,18),
    (17,'Y','2015-05-20',81,11),
    (18,'Y','2015-05-20',29,11),
    (19,'Y','2015-05-20',55,19),
    (21,'Z','2015-05-20',45,16),
    (20,'Y','2015-05-21',20,17),
    (22,'Z','2015-05-21',7 ,18),
    (23,'Z','2015-05-21',30,13),
    (24,'Z','2015-05-21',39,13),
    (25,'Z','2015-05-21',4 ,19),
    (26,'Z','2015-05-21',10,15),
    (27,'Z','2015-05-21',33,13),
    (28,'Z','2015-05-21',23,12),
    (29,'Z','2015-05-21',35,18),
    (30,'Z','2015-05-21',58,19)
GO

该表应如下所示。

ID USER_NAME DATE_TIME  COMMENT_ID LIKES
-- --------- ---------- ---------- -----
1  X         2015-05-18 75         18   
2  X         2015-05-18 1          18   
3  X         2015-05-18 42         16   
4  X         2015-05-18 59         16   
9  X         2015-05-19 34         16   
10 X         2015-05-19 57         14   
11 Y         2015-05-19 11         17   
12 Y         2015-05-19 44         18   
17 Y         2015-05-20 81         11   
18 Y         2015-05-20 29         11   
19 Y         2015-05-20 55         19   
21 Z         2015-05-20 45         16   
20 Y         2015-05-21 20         17   
22 Z         2015-05-21 7          18   
23 Z         2015-05-21 30         13   
24 Z         2015-05-21 39         13   
25 Z         2015-05-21 4          19   
26 Z         2015-05-21 10         15   
27 Z         2015-05-21 33         13   
28 Z         2015-05-21 23         12   
29 Z         2015-05-21 35         18   
30 Z         2015-05-21 58         19   

我想要做的是对数据进行报告，以便我拥有用户名、图片数量和对个人图片的评论数量。两个表中的picture_stats_id实际上是一个外键，指向表'pictures'主键picture_id。我希望能够选择日期或日期范围并使其显示如下的报告:我希望报告能够针对不同的 3 天场景显示以下内容

(Filtered for Date:2015-05-18)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   4

(Filtered for Date:2015-05-19)
USER_NAME PICS COMMENTS
--------- ---- --------
B         10   2

(Filtered for Date:2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
C         10   1

(Filtered for Date:2015-05-18 to 2015-05-19)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   6
B         10   2

(Filtered for Date:2015-05-19 to 2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
B         10   2
C         10   1

(Filtered for Date:2015-05-18 to 2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   6
B         10   5
C         10   1

到目前为止我所拥有的是

SELECT ps.USER_NAME as USER_NAME,
    COUNT(*) as PICTURE_COUNT,
    SUM(1) AS COMMENT_COUNT -- can't figure out this statement for nothing.
from picture_comment_stats pcs
RIGHT OUTER JOIN picture_stats ps ON ps.PICTURE_STATS_ID = pcs.PICTURE_STATS_ID
--WHERE ps.DATE_TIME BETWEEN @beginFilterDate AND @endFilterDate
GROUP BY ps.USER_NAME
ORDER BY ps.USER_NAME

编辑

以下评论中给出的 fiddle 地址: http://sqlfiddle.com/#!3/74e77

为了澄清我试图得到的计数。第二列是用户当天发布的图片数量。第三列是仅在指定日期对用户图片发表的评论数。所以即使 X 对 A 的图片评论了 6 次(在 18 日和 19 日)，但如果我过滤 18 日，我只想数 4，如果我过滤 18-19 日，则数 6。我敢肯定，这有点难以理解，但这就是我们想要的。

Answer 1

    SELECT --ps.USER_NAME as USER_NAME,
    s.user_name,
    count(distinct c.comment_id) as comments,
    count(s.picture_stats_id) as pics
    --COUNT(*) as PICTURE_COUNT,
    --SUM(1) AS COMMENT_COUNT
    from --commenting.transcription_audit_stats pcs
   -- RIGHT OUTER JOIN commenting.transcription_stats ps ON ps.SURVEY_STATS_ID --= pcs.SURVEY_STATS_ID 
    picture_stats s
    left join picture_comment_stats c
    on s.picture_stats_id = c.picture_stats_id
    and s.date_time = c.date_time
    where s.date_time between --specified date range
    --WHERE ps.DATE_TIME BETWEEN @beginFilterDate AND @endFilterDate    
    --GROUP BY ps.USER_NAME
    --ORDER BY ps.USER_NAME
    group by s.user_name,s.date_time

试试这个并从您拥有的查找表中获取用户名。还可以过滤您需要的日期。