1. Список говнокодов пользователя ee5620

    Всего: 1

  2. SQL / Говнокод #15606

    −124

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    Дано:
 
CREATE TABLE IF NOT EXISTS `statistic` (
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `user` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user`,`date`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
 
INSERT INTO `statistic` (`type`, `date`, `user`, `count`) VALUES
    (1, '2013-10-14', 1, 6),
    (2, '2013-10-14', 1, 6),
    (2, '2013-10-16', 1, 11),
    (2, '2013-10-15', 1, 1),
    (2, '2013-10-16', 26, 4),
    (2, '2013-10-16', 25, 1),
    (2, '2013-10-16', 29, 3),
    (2, '2013-10-16', 27, 1),
    (2, '2013-10-17', 22, 1),
    (2, '2013-10-21', 1, 2),
    (1, '2013-10-21', 1, 1),
    (1, '2013-10-22', 1, 1),
    (2, '2013-10-22', 1, 1);
 
Задача: выбрать статистики (2 типа) за 30 дней, для текущего пользователя, в разрезе по дням.
 
Решение:
SELECT t.dates,
       SUM(IF(type=1,count,0)) AS type1,
       SUM(IF(type=2,count,0)) AS type2
FROM (
  SELECT SUBDATE(DATE(NOW()),INTERVAL `index` DAY) AS dates
  FROM
  (
     SELECT 0 AS `index` UNION
     SELECT 1 UNION
     SELECT 2 UNION
     SELECT 3 UNION
     SELECT 4 UNION
     SELECT 5 UNION
     SELECT 6 UNION
     SELECT 7 UNION
     SELECT 8 UNION
     SELECT 9 UNION
 
     SELECT 10 UNION
     SELECT 11 UNION
     SELECT 12 UNION
     SELECT 13 UNION
     SELECT 14 UNION
     SELECT 15 UNION
     SELECT 16 UNION
     SELECT 17 UNION
     SELECT 18 UNION
     SELECT 19 UNION
 
     SELECT 20 UNION
     SELECT 21 UNION
     SELECT 22 UNION
     SELECT 23 UNION
     SELECT 24 UNION
     SELECT 25 UNION
     SELECT 26 UNION
     SELECT 27 UNION
     SELECT 28 UNION
     SELECT 29 
   ) AS t
 ) AS t
LEFT JOIN statistic ON t.dates=date AND user=1
GROUP BY dates
ORDER BY dates

    ee5620, 27 Марта 2014

    Комментарии (41)