dreljie602951 2016-09-30 10:41

已采纳

MySQL - 根据用户输入动态显示月份

I am not sure how I am going to do this. I'm on my wit's end and exhausted all the resources I have right now.

Alright, I have one database with column, monthname, fiscal_year, and 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 */;

And here's the content:

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;

As you can see, this is pretty much getting more complicated for me as the user, wanted to input four values.

From: Month and Fiscal Year
To: Month and Fiscal Year

Now If I were the user, I would like to input June 2016 and March 2017. My result set will be displaying:

June, July, August, Sept, October, November, December(2016), January, February, March (2017)

So this is going to be very dynamic, and I only have two columns to use and both of them are storing strings.

This is my current code and I stucked here.

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;

Please help sql experts! Thanks in advance.

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

1条回答默认最新

duanrebo3559 2016-09-30 13:37

关注

General

You cannot dynamically generate columns. So in the MySQL only solution we focus on creating rows. On the PHP solution we are doing the transposition in PHP.

PHP Approach

Idea

Let us query our table row by row. Then transpose it. We also generate an entry for every month between start and end in order to always have an entry.

Implementation

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;

where we generate

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

Now let's transpose it, from rows to columns:

// 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 "
";
// 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 "
";
}

MySQL only (per-row) approach

The idea

This solution focuses on per-row solution. Transposition can be done in PHP afterwards.

I assume we want to generate every month no matter if there is an entry in savings or not.

In order for any select statement to succeed we need a table to select from. While we can generate arbitrary values for every entry, we still need entries available.

In our case the easiest is to generate a table with dates.

The calendar table

Let's generate a table that contains entries from 1900 to 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 ;

Let's generate it using CALL. It generates a table with 2401 entries.

CALL gen_cal();

The select

Let's use our brand new calendar table to select (I removed the account part for simplicity).

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;

Note the part STR_TO_DATE('1 % %', '%d %M %Y') where we convert the format "1 March 2017" to a date. I assume that you can fill in '1 % %' via a prepared statement with the user given data.

The usage of YEAR and MONTHNAME makes it possible to select the correct date which we can then easily use to match against our user given input.

The usage of LEFT OUTER JOIN ensures we generate an entry for EVERY month, no matter if we have an entry in savings or not.

Drawbacks

We cannot in MySQL generate columns dynamically. So we are restricted to rows.

本回答被题主选为最佳回答 , 对您是否有帮助呢?

报告相同问题？

关注问题

mysql安装mysqld --initialize -console什么都没有显示 mysql 数据库
2022-07-03 07:40

回答 1 已采纳 1)重启2)看看自己的visual C++ 2015要不要跟新
Mysql为什么在命令行输入mysql -version查看不了版本呢？ mysql
2022-01-07 23:26

回答 3 已采纳不只是 mysql -v 这种方法可以查看mysql的版本，但是-version 肯定是没有的。至于其他的查看方法，可参考查看mysql版本的四种方法 - 风生水起 -
第一次配置mysql，已经启动后输入mysql -u root -p，出现enter password 是什么意思 mysql
2022-03-26 17:12

回答 3 已采纳让你写密码输入正确后就进入黑窗口
【大数据面试】MySQL面试题与答案
2023-12-20 17:36

话数Science的博客数据库中的事务是什么，MySQL中是怎么实现的 MySQL事务的特性? 数据库事务的隔离级别?解决了什么问题?默认事务隔离级别? 脏读，幻读，不可重复读的定义 MySQL怎么实现可重复读? 数据库第三范式和第四范式区别? ...
[mysql-cj-abandoned-connection-cleanup] ？ java maven mysql tomcat
2020-07-13 21:59

回答 1 已采纳 https://blog.csdn.net/www646288178/article/details/79391940/
yum install mysql-community-server 安装mysql 报错求大神帮忙！！！！ mysql
2019-05-07 12:32

回答 2 已采纳可能是 gcc 编译工具的问题，目标机器是虚拟机还是实体机呢，安装的时候选择开发工具了吗？参考看看这个：http://www.mamicode.com/info-detail-1663865.ht
CDH集群安装 yum -y install MySQL-python不成功 java mysql 大数据
2023-01-12 11:05

回答 1 已采纳如果您在Centos8上安装MySQL-python时遇到“No match for argument: MySQL-python”错误，可能是由于以下原因：您尝试安装的MySQL-python软件
python-大数据分析-基于大数据的QQ音乐数据分析系统设计与实现
2023-10-12 14:30

跟着AI学编程的博客【python-大数据分析-毕业设计】基于大数据的QQ音乐数据分析系统设计与实现讲解+源码+论文+ppt 详细咨询V：chen177396或yyhh1234nn
centos6安装mysql5.7出错：yum install mysql-community-server出错 linux mysql
2019-07-25 00:56

回答 4 已采纳报的什么错呢, 可以参考下https://www.jianshu.com/p/7b8c4dea6829
求mysql语句：根据自定义月份显示自定义和后两个月内容 mysql
2021-08-06 13:58

回答 2 已采纳这个要写一个存储过程吧。纯sql有输入的函数吗。CREATE PROCEDURE BD_GetMonthQuery@Month INT=NULLWITH ENCRYPTIONAS BEGIN
配置MySQL输入net start mysql出现“net”不是内部或外部命令 mysql python
2021-07-03 16:26

回答 1 已采纳一般都是环境没配置好, 首先分析问题,是什么文件的路径找不到, 然后找到对应文件所在的完整路径, 配置路径, 另外, 对系统环境中的其他配置的环境路径, 不要轻易去修改或者删除.
大数据-案例-离线数仓-电商：【MySQL(业务)-ETL(Kettle)】+【前端JS埋点-＞日志-＞Flume-＞HDFS-＞ETL(SparkRDD)】-＞Hive数仓-＞MySQL-＞可视化
2021-01-30 23:12

u013250861的博客 大数据-BI案例（二）-电商：数据仓库+OLAP【MySQL-ETL(Kettle)-＞ Hive（ODS层-数据清洗-＞DW层（DWD-统计分析-＞DWS））-导出结果-＞MySQL】-＞可视化
MySQL - 在到期日期前30天获取数据 mysql php
2018-04-28 06:14

回答 2 已采纳 BETWEEN is not a function, it's an operator. The syntax is: BETWEEN low_value AND high_value Yo
大数据-案例-离线数仓-在线教育：MySQL(业务数据)-ETL(Sqoop)-＞Hive数仓【ODS层-数据清洗-＞DW层(DWD-统计分析-＞DWS)】-导出(Sqoop)-＞MySQL-＞可视化
2021-07-13 13:51

u013250861的博客 2、大数据技术的应用 大数据技术的应用可以从海量的用户行为数据中进行挖掘分析，根据分析结果优化平台的服务质量，最终满足用户的需求。教育大数据分析平台项目就是将大数据技术应用于教育培训领域，为企业经营...
MySQL常用操作指令大全
2020-10-16 00:43

码渔的博客一、基础概念名词描述数据库（database）保存有组织的数据的容器（通常是一个文件或一组文件）。表（table） ...二、使用MySQL 2.1 连接数据库 mysql -u <用户名> -p -h <主机名&g
没有解决我的问题, 去提问

悬赏问题

¥15 drone 推送镜像时候 purge: true 推送完毕后没有删除对应的镜像,手动拷贝到服务器执行结果正确在样才能让指令自动执行成功删除对应镜像，如何解决？
¥15 求daily translation（DT）偏差订正方法的代码
¥15 js调用html页面需要隐藏某个按钮
¥15 ads仿真结果在圆图上是怎么读数的
¥20 Cotex M3的调试和程序执行方式是什么样的？
¥20 java项目连接sqlserver时报ssl相关错误
¥15 一道python难题3
¥15 牛顿斯科特系数表表示
¥15 arduino 步进电机
¥20 程序进入HardFault_Handler

码龄粉丝数原力等级 --

MySQL - 根据用户输入动态显示月份

1条回答默认最新

码龄粉丝数原力等级 --

General

PHP Approach

Idea

Implementation

MySQL only (per-row) approach

The idea

The calendar table

The select

Drawbacks

悬赏问题

MySQL - 根据用户输入动态显示月份

1条回答 默认 最新

General

PHP Approach

Idea

Implementation

MySQL only (per-row) approach

The idea

The calendar table

The select

Drawbacks

悬赏问题

1条回答默认最新