所以我正在努力让系统更快。目前,我正在为一笔交易对数据库进行数千次轮询。我正在通过组合数据集/表来加快速度。目前,我的数据库中有4个表:客户、食品交易、饮料交易和沙漠交易。(这些是占位符名称)。这些设置如下:每个进入的客户都会获得一个唯一的交易编号,用于订购
**Customers Table:**
ID Customer Name
0 Harvey
1 Jessica
2 Rachel
3 Louis
4 Mike
5 Donna
**Food Table**
Transaction ID Food ID Customer ID
1121 4 1
1122 2 0
1125 9 3
1122 7 0
1120 6 2
1122 6 0
**Drinks Table:**
Transaction ID Drink ID Customer ID
1121 2 1
1121 4 1
1121 4 1
1120 3 2
1125 1 3
1130 8 4
1132 8 4
**Desert Table:**
Transaction ID Desert ID Customer ID
1130 1 4
1125 3 3
1120 3 2
2100 9 5
1118 8 5
1118 7 5
现在我一直在尝试将这些表与FULL OUTER JOIN组合在一起,但我认为我可能误解了这个函数。我能够通过使用LEFT JOIN找到相应的客户名称。
有人知道我如何创建下表的其余部分吗:
**Combined Table**
Transaction IDs Customer Name Amount Foods Amount Drinks Amount Dessert
2100 Donna 0 0 1
1132 Mike 0 1 0
1130 Mike 0 1 1
1125 Louis 1 1 1
1122 Harvey 3 0 0
1121 Jessica 1 3 0
1120 Rachel 1 1 1
1118 Donna 0 0 2
一个交易属于一个客户,但是一个客户可以有多个交易ID。因此,由于事务ID在最后一个表中是唯一的,所以我不知道在使用left JOIN语句时,将使用什么作为左表。
这里是编写查询的另一种方法。它在外部查询中使用相关的子查询,而不是join
s:
SELECT c.`Customer ID`, c.`Customer Name`,
(select COUNT(DISTINCT f.`Food ID`) from Food f where f.`Customer ID` = c.`ID`
) as "Amount Foods",
(select COUNT(DISTINCT d.`Drink ID`) from Drink d where d.`Customer ID` = c.`ID`
) as "Amount Drinks",
(select COUNT(DISTINCT d.`Desert ID`) from Desert d where d.`Customer ID` = c.`ID`
) as "Amount Desert",
FROM Customers c ;
这做了几件事:
- 当您添加更多的表时,它会在外部级别阻止笛卡尔乘积
- 它消除了在外部查询中对
group by
的需要 - 它确保所有客户都在场,即使他们在任何表中都没有行
注:我保留了count(distinct)
。但是,您可能不需要它。count(*)
应该足够了,除非您知道存在不想计数的重复值。
可能是这个查询?
SELECT `Food`.`Customer ID`, `Customers`.`Customer Name`,
COUNT(DISTINCT `Food`.`Food ID`) as "Amount Foods",
COUNT(DISTINCT `drink`.`Food ID`) as "Amount drinks",
COUNT(DISTINCT `desert`.`Food ID`) as "Amount deserts"
FROM `Food`
LEFT OUTER JOIN `Customers` ON `food`.`Customer ID` = `Customers`.`ID`
LEFT OUTER JOIN `Customers` ON `drink`.`Customer ID` = `Customers`.`ID`
LEFT OUTER JOIN `Customers` ON `desert`.`Customer ID` = `Customers`.`ID`
GROUP BY `Food`.`Customer ID`, `Customers`.`Customer Name`;
编辑:
如果可能的话,我会建议改变表格结构。
- 表1:客户(CustomerID、Customer_name…)
- 表2:事务(TransactionID、CustomerID,可能是日期、时间…)
- 表3:订单(OrderID、TransactionID、类型(1-食物、2-饮料、3-沙漠)、日期、时间…)
然后你可以写这样的查询
SELECT t.transactionid, c.customername,
(SELECT count(*) FROM order food WHERE t.transactionid = food.transactionid and food.type = 1) as food_count,
(SELECT count(*) FROM order drink WHERE t.transactionid = drink.transactionid and drink.type = 2) as drink_count,
(SELECT count(*) FROM order desert WHERE t.transactionid = desert.transactionid and desert.type = 3) as desert_count
FROM transaction t
INNER JOIN customer c on t.customerid = c.customerid
GROUP BY t.transactionid, c.customername;