Question

我有一个内联网网站,只有登记用户能够观看录像。用户可以观看诸如Pipe、iPad Anders或Web等多个平台的录像,并跟踪所有这些信息(关于问题的简单性,请不考虑其他平台)。我试图在行政小组展示一些视频观察报告。

表结构如下(我有其他栏目,但对于这一问题并不重要);

CREATE TABLE IF NOT EXISTS `history` (
  `history_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `video_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `platform_id` int(11) unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`history_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

INSERT INTO `history` (`history_id`, `video_id`, `user_id`, `platform_id`, `created`) VALUES
(1, 1, 1, 1,  2012-05-06 08:13:57 ),
(2, 2, 1, 1,  2012-05-06 13:23:57 ),
(3, 1, 1, 4,  2012-05-06 18:16:39 ),
(4, 4, 2, 3,  2012-05-07 08:14:19 ),
(5, 1, 2, 3,  2012-05-07 08:14:55 ),
(6, 2, 1, 1,  2012-05-07 15:14:55 ),
(7, 3, 2, 1,  2012-05-07 18:05:14 ),
(8, 3, 1, 4,  2012-05-07 18:15:24 );

CREATE TABLE IF NOT EXISTS `sys_list_of_values` (
  `value_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_text` text COLLATE utf8_unicode_ci NOT NULL,
  `list_value` text COLLATE utf8_unicode_ci NOT NULL,
  `list_group` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`value_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `sys_list_of_values` (`value_id`, `display_text`, `list_value`, `list_group`) VALUES
(1,  iPhone ,  iphone ,  platform ),
(2,  Android ,  android ,  platform ),
(3,  iPad ,  ipad ,  platform ),
(4,  Website ,  web ,  platform ),
(5,  Active ,  active ,  status ),
(6,  Passive ,  passive ,  status ),
(7,  Waiting for approvement ,  waiting ,  status ),
(8,  Spam ,  spam ,  status ),
(9,  Deleted ,  deleted ,  status );

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

INSERT INTO `users` (`user_id`, `username`) VALUES
(1,  test_user_1 ),
(2,  test_user_2 );

CREATE TABLE IF NOT EXISTS `videos` (
  `video_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int(11) unsigned NOT NULL,
  PRIMARY KEY (`video_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO `videos` (`video_id`, `user_id`, `title`, `created`, `status`) VALUES
(1, 1,  Test Video 1 ,  2012-05-07 08:12:52 , 7),
(2, 1,  Test Video 2 ,  2012-05-07 08:12:52 , 5),
(3, 1,  Test Video 3 ,  2012-05-07 08:13:17 , 5),
(4, 1,  Test Video 4 ,  2012-05-07 08:13:17 , 6);

我试图发表如下报告:

Platform      |     Watch_Count
===============================
iPhone                  20
iPad                     0
Android                  2
Website                120
Total                  142

我有一些过滤选择,如<代码>video_id ,created,platform等。例如,我想对所有录像或特定录像(录像)或日期(日期)之间显示总观察报告。我也想展示所有平台,无论它们是零还是什么。

下面的问询只显示“i”和“网站”,但我想显示所有平台。如果没有观察点,就不得不将其显示为0(零)。我还可以显示一个最后一行,总和为<代码>watch_count和显示。

SELECT sys_list_of_values.display_text, COUNT(history.history_id) AS  total_watch  FROM history
JOIN sys_list_of_values ON sys_list_of_values.value_id = history.platform_id
WHERE history.video_id = 1
AND history.created <   2012-05-07 
GROUP BY history.platform_id

Answer 1

保留<条码>FROM HISTORY, 修改<条码>JOIN至<条码>

SELECT sys_list_of_values.display_text, COUNT(history.history_id) AS  total_watch  
FROM history
RIGHT JOIN sys_list_of_values 
   ON sys_list_of_values.value_id = history.platform_id
   AND history.created <   2012-05-07 
   AND history.video_id = 1

GROUP BY sys_list_of_values.value_id 
order by sys_list_of_values.display_text;

如果您希望使用<条码>LEFT JOIN,将历史遗址和玩具_list_of_ Values:

SELECT sys_list_of_values.display_text, COUNT(history.history_id) AS  total_watch  
FROM sys_list_of_values 
LEFT JOIN history
   ON sys_list_of_values.value_id = history.platform_id
   AND history.created <   2012-05-07 
   AND history.video_id = 1

GROUP BY sys_list_of_values.value_id 
order by sys_list_of_values.display_text;

Live test: http://www.sqlfiddle.com/#!2/495d1/15

西非经济和货币联盟所属的集团与欧洲复兴开发银行不相符合,任何东西都自动对名单进行分类,只是删除欧安会:

SELECT sys_list_of_values.display_text, 
  -- sys_list_of_values.value_id,
  COUNT(history.platform_id) AS  total_watch  
FROM sys_list_of_values 
LEFT JOIN history
   ON sys_list_of_values.value_id = history.platform_id
   AND history.created <   2012-05-07 
   AND history.video_id = 1

where sys_list_of_values.list_group =  platform 
GROUP BY 
 sys_list_of_values.display_text with rollup

实况测试:。

Answer 2

SELECT
  sys_list_of_values.value_id     as VID,
  sys_list_of_values.display_text,
  COUNT(history.history_id)       AS  total_watch 
FROM sys_list_of_values
   JOIN history
    ON sys_list_of_values.value_id = history.platform_id
where sys_list_of_values.list_group =  platform 
    and history.created <  2012-05-07 
GROUP BY history.platform_id
order by VID

Answer 3

将“权利”表的条件从“权利”表上移至“准则”上。内容提要

SELECT s.display_text
     , COUNT(h.history_id) AS total_watch 
FROM 
    sys_list_of_values AS s 
  LEFT JOIN 
    history AS h
      ON  h.platform_id = s.value_id 
      AND h.video_id = 1
      AND h.created <   2012-05-07 
GROUP BY s.value_id
  WITH ROLLUP                     --- for an additional final row with total sum

如果您希望ORDER,按显示_text 和>使用:



GROUP BY s.display_text
  WITH ROLLUP

Answer 4

您可使用分局加入对平台清单的计数:

SELECT display_text, IFNULL(total_watch, 0)
FROM sys_list_of_values
LEFT JOIN (
    SELECT platform_id AS value_id, COUNT(history_id) AS total_watch
    FROM history
    WHERE video_id = 1
    AND history.created <  2012-05-07 
    GROUP BY platform_id
) AS watch_counts USING (value_id)
WHERE list_group =  platform

这样一来,它通常更需要完成应用法中的平台清单,而不是在非行这样做,特别是如果假设应用法具有所有现有平台的长期切身之处。

友情链接