组织数据库&cronjob 的多选列 [英] Organize database & multichoice columns by cronjob

问题描述

卖家:id、年龄、来源、性别、buyer_age、buyer_origin、buyer_gender买方:id、年龄、来源、seller_age、seller_origin、seller_gender

这里我们想建议买家从这个卖家那里购买
和
建议卖家把东西卖给买家
使用定时任务.
最好使用一个查询而不是 foreach 卖家.最有效的方法是什么?我不想选择每个人并获取信息，然后更改查询，然后再次选择这是编码和使用系统内存的致命过程.如果有可能在下面的语句中使用 join 左右，那么就可以了.
如果卖家或买家说年龄和出身对我来说并不重要，那怎么可能呢?有没有

 WHERE Seller_origin = ANY

在mysql中?

解决方案

首先将买方和卖方中的所有列名称更改为以它们所源自的表的名称作为前缀，因为我们将加入表.

>

SELECT *来自卖家INNER JOIN 买家上(seller.buyer_gender =buyer.gender OR Seller.buyer_gender IS NULL)和(buyer.seller_gender =buyer.gender ORbuyer.seller_gender IS NULL) AND(seller.buyer_origin =buyer.origin OR Seller.buyer_origin IS NULL)和(buyer.seller_origin = Seller.origin OR buyer.seller_origin IS NULL)和(seller.buyer_age =buyer.age OR Seller.buyer_age IS NULL)和(buyer.seller_age = Seller.age OR Buyer.seller_age IS NULL)

seller:
  id, age, origin, gender, buyer_age, buyer_origin, buyer_gender

buyer:
  id, age, origin, seller_age, seller_origin, seller_gender

Here we want to suggest the buyer to buy from this seller
AND
suggest the seller to sell things to the buyer
with cronjob.
Doing it with one query is preferred instead of foreach seller. What's the most efficient way to do it? I don't want to SELECT each one and get info then change the query and again SELECT that's a killing process in coding and using system memory. If there's a possibility of using join or so with below statement then it's OK.
If seller or buyer says age and origin is NOT important for me, then how is it possible? Is there

 WHERE seller_origin = ANY

in mysql?

解决方案

Firstly change all the column names in both buyer and seller to be prefixed with the name of the table in which they originate as we will be joining tables.

SELECT *
FROM seller       
INNER JOIN buyer
ON
        (seller.buyer_gender = buyer.gender OR seller.buyer_gender IS NULL) AND
        (buyer.seller_gender = buyer.gender OR buyer.seller_gender IS NULL) AND
        (seller.buyer_origin = buyer.origin OR seller.buyer_origin IS NULL) AND
        (buyer.seller_origin = seller.origin OR buyer.seller_origin IS NULL) AND
        (seller.buyer_age = buyer.age OR seller.buyer_age IS NULL) AND
        (buyer.seller_age = seller.age OR buyer.seller_age IS NULL)  

这篇关于组织数据库&cronjob 的多选列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！