如果在其他地方得到回复,我立即道歉。我似乎找不到一个有效的版本,我尝试了很多不同的东西,有些来自知识,有些来自谷歌。
我在SQL查询中使用Haversine公式来排序一些零售店的距离,这样我就可以在地图上绘制它们。我得到了距离和不好的东西,但当我添加了一个选项来过滤重复或连锁商店时,我遇到了问题。根据我尝试过的不同查询,问题会有所不同,我想知道是否有人能指出我错在哪里,这样我就可以从这次经历中吸取教训。:D
我尝试过的事情(注意:这些例子中的lat/lon是假的lat/lon,我得到的lat/loon来自另一个SQL表):
SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' ORDER BY distance LIMIT 0 , 10
这会让我完成基本功。获取最近的10家商店,按距离排序,并且只选择我想要的列。现在,当有人点击"过滤相同链"选项时,我尝试了以下操作:
SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' GROUP BY store_name ORDER BY distance LIMIT 0 , 10
这确实会呈现结果,但它会过滤掉每个相同的链,而不仅仅是在显示一个链之后。例如,如果我们有沃尔格林、好市多、沃尔玛、沃尔格林、塔吉特等……那么我只想展示第一个沃尔格林,然后过滤掉第二个。谷歌搜索让我相信GROUP BY条款会起作用。它清除了所有的沃尔格林。事实上,它删除了所有重复的行。
为了尝试,我还尝试了GROUP BY store_name HAVING COUNT(*) = 1
和>
、<
、>=
、<=
变体。
我不相信我能做SELECT DISTINCT
,因为其他列本身都是不同的,所以即使store_name可能有重复,我试图选择的所有其他行都是唯一的。此外,我已经尝试过了,并确认它不会过滤掉非唯一列。
提前感谢您的帮助。请注意,我也在中标记了PHP,因为众所周知它可以与SQL接口,而我正在开发的平台是PHP。
注意:我不是只找一家商店。我正在寻找所有的商店,按距离排序,分组并过滤掉重复的商店。假设未过滤的结果是
Wal-Mart
K-Mart
Wal-Mart
Walgreens
Costco
Sams Club
Wal-Mart
Costco
Walgreens
我想返回的过滤结果:
Wal-Mart
K-Mart
Walgreens
Costco
Sams Club
一个解决方案:-
SELECT store_id,col_a,col_b,col_c,stores.store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, sub1.distance AS distance
FROM stores
INNER JOIN
(
SELECT store_name, MIN(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01'
GROUP BY store_name
) sub1
ON stores.store_name = sub1.store_name
AND (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) = sub1.distance
WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01'
ORDER BY distance LIMIT 0 , 10
这使用子查询来获取每个链的最近商店(因此子查询中的MIN/GROUPBY),然后将其与商店表连接,以获取最近商店的全部详细信息。
我在您的SQL中看不到错误,我在数据库中测试它并得到正确的结果。
SELECT store_id,
col_a,
col_b,
col_c,
store_name,
store_number,
street_address,
apt_suite,
city,
state_id,
zip_code,
latitude,
longitude,
phone_number,
phone_extension,
fax_number,
email_addr,
location_direction,
open_24_hr,
website_url,
(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE
primary_provider_code = '01' OR
secondary_provider_code = '01' OR
tertiary_provider_code = '01'
GROUP BY GROUP BY store_name
ORDER BY distance LIMIT 0 , 10
Alternative试试这个:
SELECT store_id,
col_a,
col_b,
col_c,
DISTINCT(store_name),
store_number,
street_address,
apt_suite,
city,
state_id,
zip_code,
latitude,
longitude,
phone_number,
phone_extension,
fax_number,
email_addr,
location_direction,
open_24_hr,
website_url,
(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE
primary_provider_code = '01' OR
secondary_provider_code = '01' OR
tertiary_provider_code = '01'
ORDER BY distance LIMIT 0 , 10
如果您只是想要最近商店的不同商店名称,那么您可以在当前查询的顶部执行distinct,如下所示:
SELECT DISTINCT STORE_NAME
FROM
(SELECT
STORE_ID,
COL_A,
COL_B,
COL_C,
STORE_NAME,
STORE_NUMBER,
STREET_ADDRESS,
APT_SUITE,
CITY,
STATE_ID,
ZIP_CODE,
LATITUDE,
LONGITUDE,
PHONE_NUMBER,
PHONE_EXTENSION,
FAX_NUMBER,
EMAIL_ADDR,
LOCATION_DIRECTION,
OPEN_24_HR,
WEBSITE_URL,
(3959 *ACOS(COS(RADIANS(12.1234567)) * COS(RADIANS(LATITUDE)) * COS(RADIANS(
LONGITUDE)-RADIANS(-45.678910.)) + SIN(RADIANS(12.1234567)) * SIN(RADIANS(
LATITUDE)))) AS DISTANCE
FROM
STORES
WHERE
PRIMARY_PROVIDER_CODE = '01'
OR SECONDARY_PROVIDER_CODE = '01'
OR TERTIARY_PROVIDER_CODE = '01'
ORDER BY
DISTANCE LIMIT 0 ,
10
) nearest_stores;