改进SQL查询以避免合并的另一种方法是什么?

User can search by Postcode (eg: L14, L15, L16) or Location from a textbox.

用户可以通过邮编(如:L14、L15、L16)或文本框中的位置进行搜索。

If user type in "Liverpool", it will find all the shops that are located in "Liverpool". If User type in the postcode (Eg: L15), it will search all the shops that do delivery in L15 postcode zone.

如果用户输入“Liverpool”，就会找到位于“Liverpool”的所有商店。如果用户输入邮编(如:L15)，它将搜索所有在L15邮编区域进行投递的商店。

See the Tables below:

请参阅下面的表:

mysql> select * from shops;
+----+----------+-----------+----------+
| id | name     | location  | postcode |
+----+----------+-----------+----------+
|  1 | Shop One | Liverpool | L10      |
|  2 | Shop Two | Liverpool | L16      |
+----+----------+-----------+----------+

- - - - - -

mysql> select * from shops_delivery_area;
+------------------+---------+----------+---------------+
| delivery_area_id | shop_id | postcode | delivery_cost |
+------------------+---------+----------+---------------+
|                1 |       1 | L10      |          1.50 |
|                2 |       1 | L11      |          0.00 |
|                3 |       1 | L12      |          1.00 |
|                4 |       1 | L13      |          1.00 |
|                5 |       2 | L10      |          2.50 |
|                6 |       2 | L16      |          0.00 |
|                7 |       2 | L28      |          0.00 |
+------------------+---------+----------+---------------+

SQL Query:

SQL查询:

SELECT U.* FROM 
   ((SELECT DISTINCT shops.*, DA.delivery_cost, DA.postcode AS AreaPostcode FROM shops
             JOIN shops_delivery_area as DA on (DA.shop_id = shops.id)
   WHERE DA.postcode = "Liverpool")
  UNION
   (SELECT DISTINCT shops.*, DA.delivery_cost, DA.postcode AS AreaPostcode FROM shops
             JOIN shops_delivery_area as DA on  
                              (DA.shop_id = shops.id AND
                              DA.postcode = shops.postcode)
   WHERE shops.location = "Liverpool")) as U

- - - - - -

Result - by Location (Liverpool):

结果-按地点(利物浦):

+----+----------+-----------+----------+---------------+--------------+
| id | name     | location  | postcode | delivery_cost | AreaPostcode |
+----+----------+-----------+----------+---------------+--------------+
|  1 | Shop One | Liverpool | L10      |          1.50 | L10          |
|  2 | Shop Two | Liverpool | L16      |          0.00 | L16          |
+----+----------+-----------+----------+---------------+--------------+

Result - by Postcode (L12):

结果-邮编(L12):

+----+----------+-----------+----------+---------------+--------------+
| id | name     | location  | postcode | delivery_cost | AreaPostcode |
+----+----------+-----------+----------+---------------+--------------+
|  1 | Shop One | Liverpool | L10      |          1.00 | L12          |
+----+----------+-----------+----------+---------------+--------------+

It appear to be working correctly... Is there other way to improve the SQL query shorter to avoid union or something?

它似乎运行正常……是否有其他方法可以缩短SQL查询以避免合并或其他?

4 个解决方案

#1

Whatever you choose, be aware that short code is not always optimal code. In many cases, where you have sufficiently divergent logic, unioning the results really is the most optimal (and sometimes most clean, programatically) option.

无论您选择什么，请注意短代码并不总是最优的代码。在许多情况下，当您具有足够的发散逻辑时，统一结果确实是最优的(有时是最干净的，编程的)选项。

That said, the following OR in the WHERE clause seems to cover both your cases...

也就是说，以下或WHERE条款似乎涵盖了你的两种情况……

SELECT DISTINCT
  shops.*,
  DA.delivery_cost,
  DA.postcode AS AreaPostcode
FROM
  shops
INNER JOIN
  shops_delivery_area as DA
    ON (DA.shop_id = shops.id)
WHERE
  (DA.postcode = "Liverpool")
OR
  (DA.postcode = shops.postcode AND shops.location = "Liverpool")

#2

Since all tables and selected columns are the same, you can simply do this:

因为所有的表和选定的列都是相同的，所以您可以这样做:

  SELECT DISTINCT shops.*, DA.delivery_cost, DA.postcode AS AreaPostcode FROM shops
             JOIN shops_delivery_area as DA on DA.shop_id = shops.id
   WHERE (DA.postcode = "Liverpool")
      OR (DA.postcode = shops.postcode AND shops.location = "Liverpool")

Like you said in Diego's answer, the conditions are a litle different! So, you compensate that difference in the WHERE clause.

就像你在迭戈的回答中说的，条件有点不同!所以，你补偿了WHERE子句中的差异。

#3

What am I missing? Why cant you do

我缺少什么?你为什么不能做

 WHERE DA.postcode = "Liverpool" or shops.location = "Liverpool"

#4

please try this:

请试试这个:

SELECT DISTINCT shops.*, 
       DA.delivery_cost, 
       DA.postcode 
FROM shops 
       JOIN shops_delivery_area as DA on DA.shop_id = shops.id
WHERE DA.postcode = "Liverpool" 
      OR (location = "Liverpool" and DA.postcode = shops.postcode)

#1