我目前正在尝试解决
MySQL和
PHP的复杂问题.
以下是我的表格示例：
客户名单：
table_clients
Client_ID | Client_Name | Address | Zip Code |
----------|-------------|-----------------|----------|
1 | Mark | 127 Park Ave | 12235 |
2 | John | 6 Freeman Drive | 12899 |
3 | Allan | 450 Clever Rd | 12235 |
服务清单：
table_services
Service_ID | Service_Name | Service_Price |
-----------|--------------|---------------|
1 | Fertilizer | 100.00 |
2 | Bug Spray | 50.00 |
3 | Seeds | 20.00 |
下表存储哪个客户端具有哪些服务(一个或多个),服务状态及其完成日期(如果适用)：
table_jobs
Job_ID | Client_ID | Service_ID | Status | Date_Done |
-------|-----------|------------|--------|------------|
1 | 1 | 1 | done | 2013-05-01 |
2 | 1 | 3 | active | NULL |
3 | 2 | 1 | active | NULL |
4 | 2 | 2 | active | NULL |
5 | 3 | 1 | active | NULL |
6 | 3 | 3 | active | NULL |
现在是棘手的部分.有些服务需要与其他服务有一定的时差.例如,如果一个客户在过去30天内收到肥料,则无法收到种子.为了跟踪这一点,我有第三个表格,其中包含以下信息：
table_time_difference
Service_ID_1 | Service_ID_2 | Time_Diff |
-------------|--------------|-----------|
1 | 3 | 30d |
1 | 4 | 7d |
2 | 4 | 14d |
4 | 5 | 14d |
现在一切都存储在数据库中(请记住,可能有数十种服务和数千个客户端),我试图获得具有某些服务或不具有某些服务的客户端,同时始终尊重时差.
例如：
我希望所有将要收到Fertilizer的客户都应该返回：
Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
2 | John | 12235 | 3 | 1 | Fertilizer |
3 | Allan | 12145 | 5 | 1 | Fertilizer |
现在,如果我想做所有接受肥料和虫子喷雾的客户：
Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
2 | John | 12235 | 3 | 1 | Fertilizer |
2 | John | 12235 | 4 | 2 | Bug Spray |
如果我想要在邮政编码12235中接收所有要接收种子的客户：
Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
3 | Allan | 12235 | 6 | 3 | Fertilizer |
请注意Mark不包括在内,因为他不满足自上次肥料服务以来的30天要求.
我已经尝试了各种各样的JOINS的许多不同的选项,但从来没有找到一个像所描述的那样工作的解决方案.我得到的最接近的是通过PHP生成子查询,并在一个大查询中加入它们.
例如,我的一次尝试看起来像这样(对于上面的最后一个预期结果)：
SELECT c.Client_ID,
c.Client_Name,
c.Zip_Code,
j.Job_ID,
s.Service_ID,
s.Service_Name
FROM clients c
LEFT JOIN jobs j
ON j.Client_ID = c.Client_ID
LEFT JOIN services s
ON s.Service_ID = j.Service_ID
WHERE s.Service_ID = "1"
&& c.Zip_Code = "12235"
&& c.Client_ID NOT IN (
SELECT Client_ID
FROM jobs
WHERE Status = "done"
&& Date_Done < (UNIX_TIMESTAMP() - 2592000)
)
>请注意,子查询是由PHP脚本生成的,该脚本执行查找对应于所请求服务的限制以及该服务的最小时间差,因为对同一服务可能存在多个限制,我不知道是否可以在纯SQL中执行此操作.
现在,上面显示的查询确实适用于那个确切的场景(虽然它非常慢),但它已经中断,我无法使其适应我的其他需求(包含或排除的多个服务).
如果您需要任何其他信息或者您愿意进一步讨论,请告诉我.
非常感谢所有阅读了整个问题的人(很长一段时间),我希望你们中的一些人了解我的需求并帮助我！
