MySQL的.订购位置并随机加入列表 [英] Mysql. Order locations and join listings randomly

问题描述

我有两个表:位置和列表.

I have two tables: locations and listings.

位置
id title address latitude longitude

locations
id title address latitude longitude

列表
id location info status

listings
id location info status

SELECT locations.title, 
       locations.address, 
       ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS distance      
  FROM locations 
ORDER BY distance

这将按位置以及用户提供的纬度和经度顺序列出位置.效果很好，但是我真正想做的是..

This will list the locations in order by location with the users provided latitude and longitude. Works perfect, but what I really want to do is..

1. 在每个位置列出一个列表"，并使位置保持顺序.
2. 如果一个地点包含多个列表"，则该地点是完全随机的.

在一个SQL查询中完成所有这些操作会更好吗? 还是填充所有具有至少一个列表"的位置，然后使用另一个查询为该位置选择随机的列表"?

Would it be better to do this all in one SQL query? Or populate all the locations that have atleast one "listings", then use another query to select a random "listings" for that location?

更新

提供的创建表:

CREATE TABLE `listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` varchar(4) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL,
  `info` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT NULL,
  `address_street` varchar(45) DEFAULT NULL,
  `addrees_city` varchar(45) DEFAULT NULL,
  `address_state` varchar(45) DEFAULT NULL,
  `address_zip` varchar(45) DEFAULT NULL,
  `latitude` decimal(10,6) DEFAULT NULL,
  `longitude` decimal(10,6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

推荐答案

如果在清单"表中该位置至少有1行与之关联，则要输出locations.title，请使用:

To output the locations.title if the location has at least 1 row associated with it in the "listings" table, use:

SELECT loc.title
  FROM LOCATIONS loc
 WHERE EXISTS(SELECT NULL
                FROM LISTING li
               WHERE li.location = loc.id)

使用:

  SELECT x.title, 
         x.address,
         x.distance,
         x.info,
         x.status
    FROM (SELECT loc.title, 
                 loc.address, 
                 ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS distance,
                 li.*,
                 CASE 
                   WHEN @location = loc.id THEN @rownum := @rownum + 1
                   ELSE @rownum := 1
                 END AS rank,
                 @location := loc.id
            FROM LOCATIONS loc
       LEFT JOIN LISTINGS li ON li.location = loc.id
            JOIN (SELECT @rownum := 0, @location := -1) r
        ORDER BY loc.id, RAND()) x
   WHERE x.rank = 1
ORDER BY x.distance

使用MySQL 5.1.49-community，我已经通过上述查询成功呈现了所需的结果.

Using MySQL 5.1.49-community, I've successfully rendered the desired results with the query above.

我无法使用以下方法来复制OP的重复行:

I'm unable to reproduce the OP's duplicated row using:

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT NULL,
  `address_street` varchar(45) DEFAULT NULL,
  `address_city` varchar(45) DEFAULT NULL,
  `address_state` varchar(45) DEFAULT NULL,
  `address_zip` varchar(45) DEFAULT NULL,
  `latitude` decimal(10,6) DEFAULT NULL,
  `longitude` decimal(10,6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1$$

CREATE TABLE `listings` (
  `id` int(11) NOT NULL,
  `token` varchar(4) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL,
  `info` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

INSERT语句:

INSERT INTO `locations` 
VALUES (1,'John\'s Ice Cream','1701 S Martin Luther King Jr Blvd','Lansing','MI','48910','42.714672','-84.567139'),
       (2,'7 Eleven','3500 Okemos Rd','Okemos','MI','48864','42.683331','-84.431709'),
       (3,'Kurt\'s Pizza','213 Ann St.','East Lansing','MI','48823','42.736053','-84.481636'),
       (4,'Walmart','16275 National Pkwy','Lansing','MI','48906','42.780350','-84.637238'),
       (5,'Alex\'s Hot dog Shop','8505 Delta Market Dr','Lansing','MI','48917','42.739830','-84.677330');

INSERT INTO `listings` 
VALUES (19,'39c4','1','5 gallons for $8','active'),
       (21,'89dF','4','2 mens shirts for $2','active'),
       (22,'67oP','1','Ice cream cones for $1','active'),
       (23,'5tG8','2','Large soft drinks only $0.99!','active');

这篇关于MySQL的.订购位置并随机加入列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！