codeigniter 如何合并来自不同表的两个查询的结果？

x7rlezfr 于 2022-12-07 发布在其他

关注(0)|答案(2)|浏览(123)

我有两张表：
1.工作表（_S）：
字段：标识、店铺名称、地址、区域名称、联系人
1.表格城市（_C）：
字段：标识、区域名称（与table_shop相同）、城市
两个表中的公共字段：区域名称
现在我想使用area_name和city来搜索商店。如果可用的数据较少，那么第一优先级是area_name，然后按city来获取。
例如see this。
我想获取移动的商店的一个特定的地区，如'河边' 3记录，但这里只有1记录可用，但我需要3，所以其余2记录是来自一个地方城市的河边这是阿赫梅达巴德。所以我需要从车站路，阿默达巴德获取下两个记录。
我试着做这两个不同的查询，

$q1=mysql_fetch_array(mysql_query("select * from Table_Shop where area_name='river front'"));

$tot_q1=count($q1);

if($tot_q1<3)

{

  $q2=mysql_fetch_array(mysql_query("select * from Table_City where city='ahmedabad' and area_name!='river front'"));

// i add this line *area_name!='river front'* to stop duplicate value such as if river front(mobile shop) is already fetched then i dont need to fetch it again.

}

return $q1+q2;

但是在这段代码中实现分页会更加复杂，如何在一个查询中实现呢？

codeigniter

来源：https://stackoverflow.com/questions/43936158/how-to-combine-results-of-two-query-from-different-tables

2条答案

按热度按时间

rfbsl7qr1#

尝试以下内容

SELECT * from (
    SELECT table_shop.id, table_shop.shopname, table_shop.address, 
table_shop.area_name, table_city.city
        FROM table_shop
        JOIN table_city on table_shop.area_name = table_city.area_name
        WHERE area_name='river front'
    UNION
    SELECT table_shop.id, table_shop.shopname, table_shop.address, table_shop.area_name, table_city.city
        FROM table_shop
        JOIN table_city on table_shop.area_name = table_city.area_name
        WHERE area_name!='river front' 
            AND table_city.city='ahmedabad') limit 3

或

SELECT table_shop.id, table_shop.shopname, table_shop.address, 
table_shop.area_name, table_city.city
    FROM table_shop
    JOIN table_city on table_shop.area_name = table_city.area_name
    WHERE table_shop.area_name='river front' OR  table_city.city='ahmedabad'
    ORDER BY table_shop.area_name <> 'river front',
    priority 
    limit 3

此外，通过area_name（一个文本列）链接表也不是一个好主意，我建议您创建第三个表，

area {id, name, city_id}

将table_shop更改为

shop {id, name, address, area_id, contact}

并将table_city更改为

city {id, name}

此外，在表前面加上table_是多余的，所以我会避免使用它。

赞(0）回复(0）举报 2022-12-07

ahy6op9u2#

MariaDB [sandbox]> drop table if exists table_shop;
Query OK, 0 rows affected (0.10 sec)

MariaDB [sandbox]> create table table_shop (id int,shopname varchar(20),areaname varchar(20));
Query OK, 0 rows affected (0.18 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> drop table if exists table_city;
Query OK, 0 rows affected (0.08 sec)

MariaDB [sandbox]> create table table_city (id int, areaname varchar(20), city varchar(20));
Query OK, 0 rows affected (0.19 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> insert into table_shop values
    -> (1,'Mob','station road'),(2,'food','river road'),(3,'cold drink','river road'),(4,'mob','river front'),
    -> (5,'rec','station road'),(6,'Mob','station road'),(7,'mob','station road'),(8,'ice','river road'),
    -> (9,'mob','river road');
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> insert into table_city values
    -> (1,'station road','ahamabad'),(2,'river road','delhi'),(3,'river front','ahamabad');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select ts.id,ts.shopname, ts.areaname, tc.city, s.*
    -> from table_shop ts
    -> join table_city tc on tc.areaname = ts.areaname
    -> join
    -> (
    -> select  distinct ts.shopname, ts.areaname, tc.city
    -> from table_shop ts
    -> join table_city tc on tc.areaname = ts.areaname
    -> where ts.areaname = 'river front'
    -> ) s on s.shopname = ts.shopname and s.city = tc.city
    ->
    -> ;
+------+----------+--------------+----------+----------+-------------+----------+
| id   | shopname | areaname     | city     | shopname | areaname    | city     |
+------+----------+--------------+----------+----------+-------------+----------+
|    1 | Mob      | station road | ahamabad | mob      | river front | ahamabad |
|    4 | mob      | river front  | ahamabad | mob      | river front | ahamabad |
|    6 | Mob      | station road | ahamabad | mob      | river front | ahamabad |
|    7 | mob      | station road | ahamabad | mob      | river front | ahamabad |
+------+----------+--------------+----------+----------+-------------+----------+
4 rows in set (0.00 sec)

赞(0）回复(0）举报 2022-12-07

我来回答

codeigniter 如何合并来自不同表的两个查询的结果？

2条答案

相关问题

热门标签

最新问答