mysql - 如何多次计算一行以防万一

Question

产品表:

shrimp
lobster
beef
special1
special2

我还有一个交易表，其中每一行都包含上表中的一个产品。我想统计一下交易表，这样我就可以知道有多少虾、龙虾和牛肉被卖出去。然而，具有special1的交易行应计为1x虾和1x龙虾，而special2应计为1x虾、1x龙虾和1x牛肉。

可以帮助我完成此任务的最佳 SQL 语句是什么，或者如何创建规则表来帮助我实现正确的计数？

一些示例:

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` varchar(255) DEFAULT NULL,
  `descr` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


insert into product (product_id, descr) values ('1','shrimp dish');
insert into product (product_id, descr) values ('2','beef dish');
insert into product (product_id, descr) values ('3','lobster dish');
insert into product (product_id, descr) values ('4','special1 dish');



CREATE TABLE `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` varchar(255) DEFAULT NULL,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert into ticket (product_id, price) values ('2',4);
insert into ticket (product_id, price) values ('3',6);
insert into ticket (product_id, price) values ('4',11);

在这里，我想要查询票证，以获取总销售额(21 美元)，并且我出售了 2x 虾，2x 龙虾(因为special1 需要 1x 虾和 1x 龙虾来制作)

Answer 1

好吧，在列中进行操作要容易得多，请参阅 my SQLfiddle here :

SELECT iname ingredient, SUM(amount) amount_used 
FROM tickets
INNER JOIN products    ON pid=tpid
INNER JOIN prodingred  ON pi =pid
INNER JOIN ingredients ON iid=ii
GROUP BY iname

但是，您应该再引入两个表 ingredients 和 prodingred 来定义每道菜使用哪种成分的量。与在 SELECT 语句中定义复杂的 CASE 结构相比，拥有适当的表将使整个事情更容易扩展。

我还在分配表prodingred中引入了amount列，您可以在其中放置实际数量(以克或千克为单位)每种销售产品的每种成分。这将使您的结果比仅仅说“产品 A 需要 1 只虾”更加准确。

更新

如果您还想查看每张票据对您使用的原料的成本贡献，那么您可以在 prodingred 中添加一列costshare > 表是一个很好的起点。如果您将此列设置为 float (或双)列，您实际上可以表示每餐(产品)中每种成分的成本份额，如下所示:

| pi | ii | amount | costshare |
|----|----|--------|-----------|
|  1 |  1 |      1 |         1 |
|  2 |  2 |      1 |         1 |
|  3 |  3 |      1 |         1 |
|  4 |  1 |      1 |       0.3 |
|  4 |  2 |      1 |       0.7 |
|  5 |  1 |      1 |       0.2 |
|  5 |  2 |      1 |       0.5 |
|  5 |  3 |      1 |       0.3 |

求和就非常简单了:

SELECT iname ingredient, SUM(amount) amount_used, 
       SUM(ROUND(tprice*costshare*100)/100.) tprice_contribution
FROM tickets
INNER JOIN products    ON pid=tpid
INNER JOIN prodingred  ON pi =pid
INNER JOIN ingredients ON iid=ii
GROUP BY iname

哪个会让你(见我的 updated SQLfiddle )

|   iname | amount_used | tprice_contribution |
|---------|-------------|---------------------|
|    beef |           1 |                   6 |
| lobster |           2 |                11.7 |
|  shrimp |           1 |                 3.3 |