如何对一行进行多次计数以防止

Question

产品表：

shrimp
lobster
beef
special1
special2

我还有一个事务表，每一行都包含上表中的一个产品。我想数一下交易表，看看有多少虾，龙虾和牛肉卖出去了。然而，与special1的交易行应计算为1倍虾和1倍龙虾，special2应计算为1倍虾、1倍龙虾和1倍牛肉。

有什么最好的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美元)，我卖了2倍虾，2倍龙虾(因为special1需要1倍的虾和1倍的龙虾)

Answer 1

那么，在列中这样做要容易得多，请参阅我的SQLfiddle在这里

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，它们定义了在哪个菜中使用了多少配料。拥有适当的表将使整个事情比在CASE语句中定义复杂的SELECT构造更容易伸缩。

我还介绍了配置表amount中的列prodingred，您可以在其中放置每种产品的每种配料的实际数量(克或千克)。这将使你的结果更加准确，而不仅仅是说“我需要一个虾产品A”。

更新

如果您还希望看到每张票对您使用的成分的成本贡献，那么您向prodingred表中添加列costshare的想法是一个很好的起点。如果您将此列设为浮动(或双)列，则实际上可以表示每一餐(产品)中每种配料的成本份额，如下所示：

| 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

这会让你(见我的更新的SQLfiddle)

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