一个月中每天的MySQL计数（*）返回[BLOB-2B]而不是数字 [英] MySQL count(*) everyday in a month returns [BLOB-2B] instead of number

问题描述

我要用一个特定的用户id（vwr_tid）来计算一个月中每一天的每一行。一切工作正常 - 结果显示在表中，但一件事。每天的计数没有出现。它变成[BLOB-xx]，而不是那天的行数。这是我的代码：

  SELECT MONTH_v，YEAR_V，
 GROUP_CONCAT（IF（day_v = 1，views，null） ）AS'1'，
 GROUP_CONCAT（IF（day_v = 2，views，null））AS'2'，
 GROUP_CONCAT（IF（day_v = 3，views，null） 
 GROUP_CONCAT（IF（day_v = 4，views，null））AS'4'，
 GROUP_CONCAT（IF（day_v = 5，views，null））AS'5'，
 GROUP_CONCAT IF（day_v = 6，views，null））AS'6'，
 GROUP_CONCAT（IF（day_v = 7，views，null））AS'7'，
 GROUP_CONCAT （如果没有，则返回null）AS'8'，
 GROUP_CONCAT（IF（day_v = 9，views，null））AS'9'，
 GROUP_CONCAT '10'，
 GROUP_CONCAT（IF（day_v = 11，views，null））AS'11'，
 GROUP_CONCAT（IF（day_v = 12，views，null））AS'12'，
 GROUP_CONCAT（IF（day_v = 13，views，null））AS'13'，
 GROUP_CONCAT（IF（day_v = 14，views，null））AS'14'，
 GROUP_CONCAT AS'16'，
 GROUP_CONCAT（IF（day_v = 17，views，null））AS'15'，
 GROUP_CONCAT null'）AS'17'，
 GROUP_CONCAT（IF（day_v = 18，views，null））AS'18'，
 GROUP_CONCAT（IF（day_v = 19，views， '，
 GROUP_CONCAT（IF（day_v = 20，views，null））AS'20'，
 GROUP_CONCAT（IF（day_v = 21，views，null））AS'21'，
 GROUP_CONCAT（IF（day_v = 22，views，null））AS'22'，
 GROUP_CONCAT（IF（day_v = 23，views，null））AS'23'，
 GROUP_CONCAT 24，views，null））AS'24'，
 GROUP_CONCAT（IF（day_v = 25，views，null））AS'25'，
 GROUP_CONCAT（IF（day_v = 26，views，null） ）AS'26'，
 GROUP_CONCAT（IF（day_v = 27，views，null））AS'27'，
 GROUP_CONCAT（IF（day_v = 28，views，null） 
 GROUP_CONCAT（IF（day_v = 29，views，null））AS'29'，
 GROUP_CONCAT（IF（day_v = 30，views，null））AS'30'，
 GROUP_CONCAT IF（day_v = 31，views，null））AS'31'
 FROM 
（
 SELECT DAY（vwr_date）AS day_v，
 MONTH（vwr_date）AS MONTH_v，
 Year（vwr_date）AS YEAR_V，
 date（vwr_date）AS date_v，
 count（vwr_id）AS views 
 FROM car_viewer 
 WHERE Year（vwr_date）='2012'AND vwr_tid = '18'
 GROUP BY date_v 
）as viewz 
 GROUP BY MONTH_v，YEAR_V 
 ORDER BY MONTH_v，YEAR_V DESC 
  

脚本从@rs更新：作为工作示例。

I'm going to count every rows each day in a month with a specific user id(vwr_tid). Everything works fine - the result shows up in a table but one thing. A count each days doesn't comes up. It becomes [BLOB-xx] instead of number of rows that day. Here is my code :

SELECT MONTH_v, YEAR_V, 
GROUP_CONCAT(IF(day_v=1, views, null)) AS '1',
GROUP_CONCAT(IF(day_v=2, views, null)) AS '2',
GROUP_CONCAT(IF(day_v=3, views, null)) AS '3',
GROUP_CONCAT(IF(day_v=4, views, null)) AS '4',
GROUP_CONCAT(IF(day_v=5, views, null)) AS '5',
GROUP_CONCAT(IF(day_v=6, views, null)) AS '6',
GROUP_CONCAT(IF(day_v=7, views, null)) AS '7',
GROUP_CONCAT(IF(day_v=8, views, null)) AS '8',
GROUP_CONCAT(IF(day_v=9, views, null)) AS '9',
GROUP_CONCAT(IF(day_v=10, views, null)) AS '10',
GROUP_CONCAT(IF(day_v=11, views, null)) AS '11',
GROUP_CONCAT(IF(day_v=12, views, null)) AS '12',
GROUP_CONCAT(IF(day_v=13, views, null)) AS '13',
GROUP_CONCAT(IF(day_v=14, views, null)) AS '14',
GROUP_CONCAT(IF(day_v=15, views, null)) AS '15',
GROUP_CONCAT(IF(day_v=16, views, null)) AS '16',
GROUP_CONCAT(IF(day_v=17, views, null)) AS '17',
GROUP_CONCAT(IF(day_v=18, views, null)) AS '18',
GROUP_CONCAT(IF(day_v=19, views, null)) AS '19',
GROUP_CONCAT(IF(day_v=20, views, null)) AS '20',
GROUP_CONCAT(IF(day_v=21, views, null)) AS '21',
GROUP_CONCAT(IF(day_v=22, views, null)) AS '22',
GROUP_CONCAT(IF(day_v=23, views, null)) AS '23',
GROUP_CONCAT(IF(day_v=24, views, null)) AS '24',
GROUP_CONCAT(IF(day_v=25, views, null)) AS '25',
GROUP_CONCAT(IF(day_v=26, views, null)) AS '26',
GROUP_CONCAT(IF(day_v=27, views, null)) AS '27',
GROUP_CONCAT(IF(day_v=28, views, null)) AS '28',
GROUP_CONCAT(IF(day_v=29, views, null)) AS '29',
GROUP_CONCAT(IF(day_v=30, views, null)) AS '30',
GROUP_CONCAT(IF(day_v=31, views, null)) AS '31'
FROM
(
 SELECT DAY(vwr_date) AS day_v, 
 MONTH(vwr_date) AS MONTH_v, 
 Year(vwr_date) AS YEAR_V,
 date(vwr_date) AS date_v, 
 count(vwr_id) AS views 
 FROM car_viewer 
 WHERE Year(vwr_date)='2012' AND vwr_tid='18' 
 GROUP BY date_v 
) as viewz
GROUP BY MONTH_v, YEAR_V 
ORDER BY MONTH_v, YEAR_V DESC

The script is updated from @rs : Count record each day of a month from mysql into html table

The result.

解决方案

I don't see the need for GROUP_CONCAT(), when SUM() will serve your needs:

SELECT MONTH_v, YEAR_V, 
SUM(IF(day_v=1, views, 0)) AS '1',
SUM(IF(day_v=2, views, 0)) AS '2',
SUM(IF(day_v=3, views, 0)) AS '3',
SUM(IF(day_v=4, views, 0)) AS '4',
SUM(IF(day_v=5, views, 0)) AS '5',
SUM(IF(day_v=6, views, 0)) AS '6',
SUM(IF(day_v=7, views, 0)) AS '7',
SUM(IF(day_v=8, views, 0)) AS '8',
SUM(IF(day_v=9, views, 0)) AS '9',
SUM(IF(day_v=10, views, 0)) AS '10',
SUM(IF(day_v=11, views, 0)) AS '11',
SUM(IF(day_v=12, views, 0)) AS '12',
SUM(IF(day_v=13, views, 0)) AS '13',
SUM(IF(day_v=14, views, 0)) AS '14',
SUM(IF(day_v=15, views, 0)) AS '15',
SUM(IF(day_v=16, views, 0)) AS '16',
SUM(IF(day_v=17, views, 0)) AS '17',
SUM(IF(day_v=18, views, 0)) AS '18',
SUM(IF(day_v=19, views, 0)) AS '19',
SUM(IF(day_v=20, views, 0)) AS '20',
SUM(IF(day_v=21, views, 0)) AS '21',
SUM(IF(day_v=22, views, 0)) AS '22',
SUM(IF(day_v=23, views, 0)) AS '23',
SUM(IF(day_v=24, views, 0)) AS '24',
SUM(IF(day_v=25, views, 0)) AS '25',
SUM(IF(day_v=26, views, 0)) AS '26',
SUM(IF(day_v=27, views, 0)) AS '27',
SUM(IF(day_v=28, views, 0)) AS '28',
SUM(IF(day_v=29, views, 0)) AS '29',
SUM(IF(day_v=30, views, 0)) AS '30',
SUM(IF(day_v=31, views, 0)) AS '31'
FROM
(
 SELECT DAY(vwr_date) AS day_v, 
 MONTH(vwr_date) AS MONTH_v, 
 Year(vwr_date) AS YEAR_V,
 date(vwr_date) AS date_v, 
 count(vwr_id) AS views 
 FROM car_viewer 
 WHERE Year(vwr_date)='2012' AND vwr_tid='18' 
 GROUP BY date_v 
) as viewz
GROUP BY MONTH_v, YEAR_V 
ORDER BY MONTH_v, YEAR_V DESC;

See http://sqlfiddle.com/#!2/75aa8/1 for a working example.

这篇关于一个月中每天的MySQL计数（*）返回[BLOB-2B]而不是数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！