EDIT although the accepted answer doesn't match what I was looking for at the moment I wrote this question, it does show a better way of what I had already (which is what they want) the data in the production tables are what is wrong, meaning that the numbers are wrong.
编辑虽然接受的答案与我在写这个问题时所寻找的不一致，但它确实显示了我已经拥有的更好的方式（这是他们想要的）生产表中的数据是错误的，这意味着数字是错误的。
I'm struggling to create the sql statment to create the report at the bottom of this post. I've included the statments to create my tables and test data. So to get started here is the Creation statement for the picture_stats
我正在努力创建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
Insert statement that puts data into picture_stats
插入将数据放入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
Table view of the data.
表视图的数据。
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
The second table picture_comment_stats can be created with this:
可以使用以下命令创建第二个表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
To script in the data:
要在数据中编写脚本：
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
The table should look like this.
该表应如下所示。
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
What I want to do is make a report of the data such that I have the user name, the count of the pictures, and the count of comments on an individuals picture. The picture_stats_id in both tables is actually a foreign key which points to the table 'pictures' primary key picture_id. The report I want to be able to choose a date or a date range and have it show like so: I'm hoping the report would show the following for the different 3 day scenarios
我想要做的是报告数据，以便我拥有用户名，图片数和个人图片的评论数。两个表中的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
so far all I have is
到目前为止，我所拥有的只是
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
EDIT
编辑
The fiddle address as was given in the below comments: http://sqlfiddle.com/#!3/74e77
下面的评论中给出了小提琴地址：http：//sqlfiddle.com/#！3 / 74e77
To clarify the counts I'm try to get. The second column, would be the number of pictures said user posted that day. the third column is how many comments were made on a users picture for that specified day ONLY. So even though X commented on A's picture a total of 6 times (on the 18th, and 19th) I only want to count 4 if I filter for the 18th, and 6 if i filter on the 18th-19th. It's a little hard to follow I'm sure, but that's what is wanted.
澄清我试图获得的计数。第二列，将是当天用户发布的图片数量。第三列是仅在指定日期对用户图片进行了多少评论。因此，即使X对A的图片总共评论了6次（在18日和19日），我只想在第18次过滤时计算4次，如果我在18日到19日过滤则为6次。我确信这有点难以理解，但这就是我想要的。
1 个解决方案
#1
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
Try this and get the user name from the lookup table you have. Also filter on the dates you need.
试试这个并从你拥有的查找表中获取用户名。同时过滤您需要的日期。
#1
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
Try this and get the user name from the lookup table you have. Also filter on the dates you need.
试试这个并从你拥有的查找表中获取用户名。同时过滤您需要的日期。