如何为此编写SQL？ [英] How to write sql for this?
问题描述
我有这样的表
Customer
-ID
-name
-address
Business
-ID
-name
-type
Discount
-ID
-amount
-BusinessID
-position
UsedDiscounts
-CustomerID
-DiscountID
企业有很多折扣。
用户使用了很多折扣，其中的记录在UsedDiscounts中。
User has used many discounts, record of which is in UsedDiscounts.
用户只能按顺序使用按位置定义的折扣。折扣1，然后是折扣2。因此，即使企业有10个折扣，一个客户有资格获得的是该企业使用的折扣的位置+ 1。
User can only use discount in order, defined by position. Discount 1, then Discount 2. So even if business has 10 discounts out, the one Customer qualifies for is Position of used discount by that business + 1.
目标：获取用户有资格获得的所有折扣。
我的方法是对折扣和二手折扣进行左排除联接。
My approach was to do left exclusion join on Discounts and Used Discounts.
因此获得所有折扣减去使用过的折扣，然后以某种方式在位置上进行最小折扣，并获得所有合格折扣。但是，我也许可以在SQL中实现这一目标，我不知道如何实现。.
So get all discounts minus used ones, then somehow do min on position and get all "qualified" ones. However, I might be able to pull this off in SQL I have no idea how..
示例不完整的SQL看起来像这样
Sample Incomplete SQL looks like this
SELECT *, min(gd.position) FROM
(SELECT * FROM "Deals" as d WHERE (d.active = true) AND (d.latitude BETWEEN 40 AND 41) AND (d.longitude BETWEEN -75 AND -70)) AS gd
LEFT JOIN
(SELECT du."DealId" FROM "DealsUsed" AS du WHERE du."CustomerId" = 1) AS bd
ON gd.id = bd."DealId"
WHERE bd."DealId" IS NULL
GROUP BY gd."UserId";
输出错误
Sample data:
Customer
--------
id name address
0 Tobby 93903903
1 Emi 3839039
2 Loop 393030
Business
--------
id name type
0 Cool flower
1 Corner car
2 New deli
3 Side printing
4 Big car
Discount
--------
id amount businessId position
0 10 0 0
1 22 3 1
2 10 3 2
3 43 2 0
4 23 5 0
5 10 5 1
Used Discount
----------
customerId discountId
1 2
outcome for customer 1 , emi, shouuld be
Discounts
--------
id amount businessId position
0 10 0 0
4 23 5 0
3 43 2 0
5 10 5 1
推荐答案
您的左手联接不会生效，因为您在第二个查询中使用了where子句：
Your left join does not take affect because you use where clause in the second query:
删除where子句：
SELECT *, min(gd.position) FROM
(SELECT * FROM "Deals" as d WHERE (d.active = true) AND (d.latitude BETWEEN 40 AND 41) AND (d.longitude BETWEEN -75 AND -70)) AS gd
LEFT JOIN
(SELECT du."DealId" FROM "DealsUsed" AS du) AS bd
ON gd.id = bd."DealId"
WHERE bd."DealId" IS NULL
GROUP BY gd."UserId";
使用 Group by 和不建议选择* 。只需选择您需要的字段。
using Group by with Select * is not advisable. Just select the fields you need. Something like:
SELECT gd."UserId", min(gd.position) FROM
(SELECT * FROM "Deals" as d WHERE (d.active = true) AND (d.latitude BETWEEN 40 AND 41) AND (d.longitude BETWEEN -75 AND -70)) AS gd
LEFT JOIN
(SELECT du."DealId" FROM "DealsUsed" AS du) AS bd
ON gd.id = bd."DealId"
WHERE bd."DealId" IS NULL
GROUP BY gd."UserId";
您可以在on后面使用where子句：
You can use where clause after on like:
SELECT gd."UserId",bd."CustomerId", min(gd.position) FROM
(SELECT * FROM "Deals" as d WHERE (d.active = true) AND (d.latitude BETWEEN 40 AND 41) AND (d.longitude BETWEEN -75 AND -70)) AS gd
LEFT JOIN
(SELECT du."DealId" FROM "DealsUsed" AS du) AS bd
ON gd.id = bd."DealId"
WHERE bd."DealId" IS NULL and bd."CustomerId" = 1
GROUP BY gd."UserId";
这篇关于如何为此编写SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
