Блог

Главная - Вопросы по программированию - MySQL — Отображение месяцев динамически в соответствии с пользовательским вводом

MySQL — Отображение месяцев динамически в соответствии с пользовательским вводом

#php #mysql

#php #mysql

Вопрос:

Я не уверен, как я собираюсь это сделать. Я нахожусь в тупике и исчерпал все ресурсы, которые у меня есть прямо сейчас.

Хорошо, у меня есть одна база данных со столбцом, monthname, fiscal_year и amount. 

 DROP TABLE IF EXISTS `savings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `savings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(10) NOT NULL,
  `monthname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fiscal_year` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `amount` decimal(30,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `net_savings_id_unique` (`id`,`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=841 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

И вот содержимое:

 LOCK TABLES `savings` WRITE;
/*!40000 ALTER TABLE `net_savings` DISABLE KEYS */;
INSERT INTO `net_savings` VALUES (1,1,'January','2016',-1291.47,'2016-09-23 06:45:00','2016-09-23 06:45:00'),(2,1,'February','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(3,1,'March','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(4,1,'April','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(5,1,'May','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(6,1,'June','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(7,1,'July','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(8,1,'August','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(9,1,'September','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(10,1,'October','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(11,1,'November','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(12,1,'December','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(13,1,'January','2017',389296.02,'2016-09-23 06:45:00','2016-09-23 06:45:00'),(14,1,'February','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(15,1,'March','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(16,1,'April','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(17,1,'May','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(18,1,'June','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(19,1,'July','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(20,1,'August','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(21,1,'September','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(22,1,'October','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(23,1,'November','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(24,1,'December','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(25,1,'January','2018',216143.70,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(26,1,'February','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(27,1,'March','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(28,1,'April','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(29,1,'May','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(30,1,'June','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(31,1,'July','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(32,1,'August','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(33,1,'September','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(34,1,'October','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(35,1,'November','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(36,1,'December','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(37,1,'January','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(38,1,'February','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(39,1,'March','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(40,1,'April','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(41,1,'May','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(42,1,'June','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(43,1,'July','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(44,1,'August','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(45,1,'September','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(46,1,'October','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(47,1,'November','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(48,1,'December','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(49,1,'January','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(50,1,'February','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(51,1,'March','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(52,1,'April','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(53,1,'May','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(54,1,'June','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(55,1,'July','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(56,1,'August','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(57,1,'September','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(58,1,'October','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(59,1,'November','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(60,1,'December','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(61,2,'January','2016',53840.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(62,2,'February','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(63,2,'March','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(64,2,'April','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(65,2,'May','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(66,2,'June','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(67,2,'July','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(68,2,'August','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(69,2,'September','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(70,2,'October','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(71,2,'November','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(72,2,'December','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(73,2,'January','2017',1669693.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(74,2,'February','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(75,2,'March','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(76,2,'April','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(77,2,'May','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(78,2,'June','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(79,2,'July','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(80,2,'August','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(81,2,'September','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(82,2,'October','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(83,2,'November','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(84,2,'December','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(85,2,'January','2018',1980328.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(86,2,'February','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(87,2,'March','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(88,2,'April','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(89,2,'May','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(90,2,'June','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(91,2,'July','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(92,2,'August','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(93,2,'September','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(94,2,'October','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(95,2,'November','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(96,2,'December','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(97,2,'January','2019',2428559.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(98,2,'February','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(99,2,'March','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(100,2,'April','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(101,2,'May','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(102,2,'June','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(103,2,'July','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(104,2,'August','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(105,2,'September','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(106,2,'October','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(107,2,'November','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(108,2,'December','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(109,2,'January','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(110,2,'February','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(111,2,'March','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(112,2,'April','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(113,2,'May','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(114,2,'June','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(115,2,'July','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(116,2,'August','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(117,2,'September','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(118,2,'October','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(119,2,'November','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(120,2,'December','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(181,4,'January','2016',963.32,'2016-09-23 07:11:42','2016-09-23 07:11:42'),(182,4,'February','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(183,4,'March','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(184,4,'April','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(185,4,'May','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(186,4,'June','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(187,4,'July','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(188,4,'August','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(189,4,'September','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(190,4,'October','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(191,4,'November','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(192,4,'December','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(193,4,'January','2017',3960.93,'2016-09-23 07:11:42','2016-09-23 07:11:42'),(194,4,'February','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(195,4,'March','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(196,4,'April','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(197,4,'May','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(198,4,'June','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(199,4,'July','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(200,4,'August','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(201,4,'September','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(202,4,'October','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(203,4,'November','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(204,4,'December','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(205,4,'January','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(206,4,'February','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(207,4,'March','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(208,4,'April','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(209,4,'May','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(210,4,'June','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(211,4,'July','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(212,4,'August','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(213,4,'September','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(214,4,'October','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(215,4,'November','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(216,4,'December','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(217,4,'January','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(218,4,'February','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(219,4,'March','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(220,4,'April','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(221,4,'May','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(222,4,'June','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(223,4,'July','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(224,4,'August','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(225,4,'September','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(226,4,'October','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(227,4,'November','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(228,4,'December','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(229,4,'January','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(230,4,'February','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(231,4,'March','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(232,4,'April','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(233,4,'May','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(234,4,'June','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(235,4,'July','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(236,4,'August','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(237,4,'September','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(238,4,'October','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(239,4,'November','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(240,4,'December','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(241,5,'January','2016',-261.74,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(242,5,'February','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(243,5,'March','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(244,5,'April','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(245,5,'May','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(246,5,'June','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(247,5,'July','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(248,5,'August','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(249,5,'September','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(250,5,'October','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(251,5,'November','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(252,5,'December','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(253,5,'January','2017',24160.80,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(254,5,'February','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(255,5,'March','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(256,5,'April','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(257,5,'May','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(258,5,'June','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(259,5,'July','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(260,5,'August','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(261,5,'September','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(262,5,'October','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(263,5,'November','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(264,5,'December','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(265,5,'January','2018',24160.80,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(266,5,'February','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(267,5,'March','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(268,5,'April','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(269,5,'May','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(270,5,'June','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(271,5,'July','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(272,5,'August','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(273,5,'September','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(274,5,'October','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(275,5,'November','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(276,5,'December','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(277,5,'January','2019',8053.60,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(278,5,'February','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(279,5,'March','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(280,5,'April','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(281,5,'May','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(282,5,'June','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(283,5,'July','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(284,5,'August','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(285,5,'September','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(286,5,'October','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(287,5,'November','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(288,5,'December','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(289,5,'January','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(290,5,'February','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(291,5,'March','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(292,5,'April','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(293,5,'May','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(294,5,'June','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(295,5,'July','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(296,5,'August','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(297,5,'September','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(298,5,'October','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(299,5,'November','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(300,5,'December','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(301,6,'January','2016',8846.54,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(302,6,'February','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(303,6,'March','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(304,6,'April','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(305,6,'May','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(306,6,'June','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(307,6,'July','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(308,6,'August','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(309,6,'September','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(310,6,'October','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(311,6,'November','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(312,6,'December','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(313,6,'January','2017',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(314,6,'February','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(315,6,'March','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(316,6,'April','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(317,6,'May','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(318,6,'June','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(319,6,'July','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(320,6,'August','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(321,6,'September','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(322,6,'October','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(323,6,'November','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(324,6,'December','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(325,6,'January','2018',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(326,6,'February','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(327,6,'March','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(328,6,'April','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(329,6,'May','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(330,6,'June','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(331,6,'July','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(332,6,'August','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(333,6,'September','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(334,6,'October','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(335,6,'November','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(336,6,'December','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(337,6,'January','2019',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(338,6,'February','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(339,6,'March','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(340,6,'April','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(341,6,'May','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(342,6,'June','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(343,6,'July','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(344,6,'August','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(345,6,'September','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(346,6,'October','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(347,6,'November','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(348,6,'December','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(349,6,'January','2020',27427.56,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(350,6,'February','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(351,6,'March','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(352,6,'April','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(353,6,'May','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(354,6,'June','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(355,6,'July','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(356,6,'August','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(357,6,'September','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(358,6,'October','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(359,6,'November','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(360,6,'December','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11');
/*!40000 ALTER TABLE `net_savings` ENABLE KEYS */;
UNLOCK TABLES;

Как вы можете видеть, это в значительной степени усложняется для меня как пользователя, который хотел ввести четыре значения.

Откуда: месяц и финансовый год
Кому: месяц и финансовый год

Теперь, если бы я был пользователем, я бы хотел ввести июнь 2016 и март 2017. Мой результирующий набор будет отображаться:

Июнь, июль, август, сентябрь, октябрь, ноябрь, декабрь (2016), январь, февраль, март (2017)

Так что это будет очень динамично, и у меня есть только два столбца для использования, и оба они хранят строки.

Это мой текущий код, и я застрял здесь.

 select a.id, a.account, a.region, sum(n.amount) as 'TotalNet',
  sum(case when n.monthname = "November" then n.amount else 0 end) as `Nov`,
  sum(case when n.monthname = "December" then n.amount else 0 end) as `Dec`,
  sum(case when n.monthname = "January" then n.amount else 0 end) as `Jan`,
  sum(case when n.monthname = "February" then n.amount else 0 end) as `Feb`,
  sum(case when n.monthname = "March" then n.amount else 0 end) as `Mar`,
  sum(case when n.monthname = "April" then n.amount else 0 end) as `Apr`,
  sum(case when n.monthname = "May" then n.amount else 0 end) as `May`,
  sum(case when n.monthname = "June" then n.amount else 0 end) as `Jun`,
  sum(case when n.monthname = "July" then n.amount else 0 end) as `Jul`,
  sum(case when n.monthname = "August" then n.amount else 0 end) as `Aug`,
  sum(case when n.monthname = "September" then n.amount else 0 end) as `Sep`,
  sum(case when n.monthname = "October" then n.amount else 0 end) as `Oct`
from 
savings n left outer join accounts a
on a.id = n.account_id
where fiscal_year = 2016
group by a.account
order by a.account desc;

Пожалуйста, помогите экспертам по sql! Заранее спасибо.

Комментарии:

1. Хорошая идея ввести некоторые данные, чтобы мы могли протестировать ответ. Но у вас есть Create для savings и INSERT для net_savings Можем ли мы предположить опечатку?

2. Я думаю, вам нужна отдельная таблица с надписью «Календарь», чтобы справиться с этим. Это может быть лучшим динамическим решением. Наконец, преобразуйте строки в DateTime для года или месяца.

3. И нет accounts таблицы?

4. пожалуйста, не обращайте внимания на таблицу accounts, я могу в конечном итоге присоединиться к ней позже, так как там можно увидеть только имя.

5. @AT-2016 можете ли вы показать нам, как это сделать? пожалуйста.

Ответ №1:

Общая информация

Вы не можете динамически генерировать столбцы. Итак, в единственном решении MySQL мы фокусируемся на создании строк. В решении PHP мы выполняем транспозицию в PHP.

Подход PHP

Идея

Давайте запрашивать нашу таблицу строка за строкой. Затем перенесите его. Мы также генерируем запись для каждого месяца между start и end для того, чтобы всегда иметь запись.

Реализация

 SELECT a.account_id, fiscal_year, monthname, SUM(n.amount) as ThisMonth
FROM  savings n
LEFT OUTER JOIN accounts a
     ON .....
WHERE fiscal_year >= YEAR($start_date)
     AND monthname >= MONTHNAME($start_date)
     AND fiscal_year <= YEAR($end_date)
     AND monthname <= MONTHNAME($end_date)
GROUP BY c.date;

где мы генерируем

 $start_date = DateTime::createFromFormat('%M %Y', $input_start);
$end_date = DateTime::createFromFormat('%M %Y', $input_end);
$interval = DateInverall:createFromDateString('1 month');

Теперь давайте перенесем его из строк в столбцы:

 // QUERY OUR MYSQL USING PREPARED STATEMENTS
$dates = [];
for ($date = $start_date; $date <= $end_date; $date->add($interval)) {
   $dates[] = $date;
}

$accounts = [];
foreach($rows as $row) {
    if (!isset($accounts[$row['account_id']])) {
        $entry = []
        $accounts[$row['account_id']] = $entry;
    }
    $fyear = $row['fiscal_year'];
    $month = $row['monthname'];
    $accounts[$row['account_id']][$fyear][$month] = $row['TotalMonth'];
}

//print header. We are doing this very stupid here ,
//you want some sort of table layout
echo "Account ID";
foreach($dates as $date) {
    echo $date;
}
echo "n";
// output our data
// at this point accounts is a multidimensional array: 
foreach($account as $aid => $account) {
   echo $aid;
   foreach($dates as $date) {
      $value = 0;
      if (!isset($account[$date->format('%Y')][$date->format('%M')])) {
         $value = $account[$date->format('%Y')][$date->format('%M')];
      }
      echo $value;
   }
   echo "n";
}

Подход только для MySQL (для каждой строки)

Идея

Это решение ориентировано на решение для каждой строки. Впоследствии транспонирование может быть выполнено в PHP.

Я предполагаю, что мы хотим генерировать каждый месяц независимо от того, есть запись savings или нет.

Для успешного выполнения любого оператора select нам нужна таблица для выбора. Хотя мы можем генерировать произвольные значения для каждой записи, нам все равно нужны доступные записи.

В нашем случае проще всего сгенерировать таблицу с датами.

Таблица календаря

Давайте создадим таблицу, содержащую записи с 1900 по 2100:

 DELIMITER //
CREATE PROCEDURE gen_cal()
  BEGIN
    CREATE TABLE `calendar` (`date` DATE NOT NULL);
    SET @start = MAKEDATE(1900, 1);
    WHILE @start <= MAKEDATE(2100, 1) DO
      INSERT INTO `calendar` (`date`) VALUES (@start);
      SET @start = ADDDATE(@start, interval 1 month);
    END WHILE;
  END //
DELIMITER ;

Давайте сгенерируем его с помощью CALL . Он генерирует таблицу с 2401 записью.

 CALL gen_cal();

Выбор

Давайте используем нашу новую calendar таблицу для выбора (я удалил account часть для простоты).

 SELECT c.date, MONTHNAME(c.date) as month, SUM(n.amount) as ThisMonth
FROM calendar c
LEFT OUTER JOIN savings n
   ON fiscal_year = YEAR(c.date)
     AND monthname = MONTHNAME(c.date)
WHERE c.date
   BETWEEN STR_TO_DATE('1 March 2016', '%d %M %Y')
   AND STR_TO_DATE('1 March 2017', '%d %M %Y')
GROUP BY c.date;

Обратите внимание на ту часть STR_TO_DATE('1 % %', '%d %M %Y') , где мы преобразуем формат «1
Март 2017» на дату. Я предполагаю, что вы можете заполнить ‘1% %’ с помощью подготовленного оператора данными, заданными пользователем.

Использование YEAR и MONTHNAME позволяет выбрать правильную дату, которую мы затем можем легко использовать для сопоставления с нашим пользовательским вводом.

Использование LEFT OUTER JOIN гарантирует, что мы создаем запись для КАЖДОГО месяца, независимо от того, есть ли у нас запись savings или нет.

Недостатки

Мы не можем в MySQL динамически генерировать столбцы. Таким образом, мы ограничены строками.

Комментарии:

1. Спасибо, у меня появилась идея!