代码之家 › 专栏 › 技术社区 › Siva Kranthi Kumar

具有相同列值的max()group by(aggregate)函数问题

mysql sql

Siva Kranthi Kumar · 技术社区 · 16 年前

DROP TABLE IF EXISTS `items_stock`;
CREATE TABLE IF NOT EXISTS `items_stock` (
  `ItemStockID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ItemID` mediumint(8) unsigned NOT NULL,
  `Date` date NOT NULL,
  `CurrentStock` decimal(14,2) NOT NULL DEFAULT '0.00',
  `CreatedBy` int(5) NOT NULL,
  `CreatedDate` datetime NOT NULL,
  `ModifiedBy` int(5) DEFAULT NULL,
  `ModifiedDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `Active` enum('1','0') NOT NULL DEFAULT '1',
  PRIMARY KEY (`ItemStockID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `items_stock` (`ItemStockID`, `ItemID`, `Date`, `CurrentStock`, `CreatedBy`, `CreatedDate`, `ModifiedBy`, `ModifiedDate`, `Active`) VALUES 

(1, 1, '2010-11-03', 500.00, 1, '2010-11-03 11:12:12', NULL, '2010-11-04 09:39:29', '1'), 
(2, 1, '2010-11-04', 400.00, 1, '2010-11-04 11:12:12', NULL, '2010-11-04 10:10:09', '1'), 
(3, 1, '2010-11-04', 700.00, 1, '2010-11-04 11:14:12', NULL, '0000-00-00 00:00:00', '1'), 
(4, 1, '2010-11-03', 600.00, 1, '2010-11-04 11:19:12', NULL, '2010-11-04 10:11:26', '1'),
(5, 2, '2010-11-05', 800.00, 1, '2010-11-05 11:19:12', NULL, '2010-11-05 10:11:26', '1');

现在我想得到今天所有项目的当前库存(2010-11-05),例如ID为1的项目当前库存为700(itemstockID为3)

我试着回答以下问题

SELECT MAX(DATE) AS DATE, ItemStockID, ItemID, CurrentStock FROM items_stock WHERE DATE <= '2010-11-05' AND Active = 1 GROUP BY ItemID;

+------------+-------------+--------+--------------+
| DATE       | ItemStockID | ItemID | CurrentStock |
+------------+-------------+--------+--------------+
| 2010-11-04 |           1 |      1 |       500.00 |
| 2010-11-05 |           5 |      2 |       800.00 |
+------------+-------------+--------+--------------+

但如果同一日期不止一次,它就没有给出合适的当前库存。

实际结果应该是这样的

+------------+-------------+--------+--------------+
| DATE       | ItemStockID | ItemID | CurrentStock |
+------------+-------------+--------+--------------+
| 2010-11-04 |           3 |      1 |       700.00 |
| 2010-11-05 |           5 |      2 |       800.00 |
+------------+-------------+--------+--------------+

如果我使用下面的查询,它会给出上面的结果

SELECT ISTI.ItemStockID, ISTI.DATE, ISTI.ItemID, ISTI.CurrentStock FROM items_stock AS ISTI
JOIN (SELECT MAX(ItemStockID) AS ItemStockID 
        FROM items_stock AS IST
            JOIN (SELECT MAX(DATE) AS DATE, ItemID FROM items_stock WHERE DATE <= '2010-11-05' AND Active = 1 GROUP BY ItemID) AS SD ON SD.Date = IST.Date AND IST.ItemID = SD.ItemID
        GROUP BY IST.ItemID
    ) AS ISTO ON ISTO.ItemStockID = ISTI.ItemStockID;

你能告诉我如何优化得到上述结果吗

2 回复 | 直到 16 年前

smartnut007 16 年前

select A.ItemId,A.DATE,A.CurrentStock,A.ItemStockID from (select ItemId,MAX(DATE) DATE from items_stock where DATE <= '2010-11-05' AND Active = 1 group by ItemId) I, items_stock A where A.ItemStockID=( select MAX(ItemStockID) from items_stock where ItemID=I.ItemId and DATE=I.DATE and Active=1) ;

Siva Kranthi Kumar 16 年前

SELECT ISTI.ItemStockID, ISTI.DATE, ISTI.ItemID, ISTI.CurrentStock FROM items_stock AS ISTI
JOIN (SELECT MAX(ItemStockID) AS ItemStockID 
        FROM items_stock AS IST
            JOIN (SELECT MAX(DATE) AS DATE, ItemID FROM items_stock WHERE DATE <= '2010-11-05' AND Active = 1 GROUP BY ItemID) AS SD ON SD.Date = IST.Date AND IST.ItemID = SD.ItemID
        GROUP BY IST.ItemID
    ) AS ISTO ON ISTO.ItemStockID = ISTI.ItemStockID;