个人中心
gpt4 book ai didi

php - 两个表并集的行数增加的临时列

转载 作者：行者123 更新时间：2023-11-29 17:50:19 25 4
gpt4 key购买 nike

我有两个表:

hair_product_table  22 products
mix_product_tables  300 products table

示例数据:

select version() as 'mysql version';
DROP TABLE IF EXISTS `hair_product_table`;
CREATE TABLE IF NOT EXISTS `hair_product_table` (
  `idphair` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `productname` varchar(60) NOT NULL,
  `description` text,
  `pic` text,
  PRIMARY KEY (`idphair`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `mix_product_tables`;
CREATE TABLE IF NOT EXISTS `mix_product_tables` (
  `idmix` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `productname` varchar(256) NOT NULL,
  `pic` text,
  `description` text,
  PRIMARY KEY (`idmix`)
) ENGINE=InnoDB AUTO_INCREMENT=2776 DEFAULT CHARSET=utf8;

#insert dadata for hair_product_table

INSERT INTO `hair_product_table` (`idphair`, `productname`, `description`, `pic`) VALUES
(4, 'jselwe','',''),
(5, 'ddd','',''),
(6, 'jselwe','',''),
(7, 'jelwe','',''),
(8, 'jelwe','',''),
(9, 'jelwe','',''),
(10, 'jelwe','',''),
(11, 'jelwe','',''),
(12, 'jelwe','',''),
(13, 'jelwe','',''),
(14, 'jelwe','',''),
(15, 'jelwe','',''),
(16, 'jelwe','',''),
(17, 'jelwe','',''),
(18, 'jelwe','',''),
(19, 'jelwe','',''),
(20, 'jelwe','',''),
(21, 'jelwe','',''),
(22, 'jelwe','',''),
(23, 'jelwe','',''),
(24, 'jelwe','',''),
(25, 'jelwe','',''),
(26, 'jelwe','',''),
(27, 'jelwe','','');


#insert dadata for mix_product_tables


INSERT INTO `mix_product_tables` (`idmix`, `productname`, `description`, `pic`) VALUES
(1, 'mix1','',''),
(2, 'mix2','',''),
(3, 'mix3','',''),
(4, 'mix4','',''),
(5, 'mix5','',''),
(6, 'jselwe','',''),
(7, 'jelwe','',''),
(8, 'jelwe','',''),
(9, 'jelwe','',''),
(10, 'jelwe','',''),
(11, 'jelwe','',''),
(12, 'jelwe','',''),
(13, 'jelwe','',''),
(14, 'jelwe','',''),
(15, 'jelwe','',''),
(16, 'jelwe','',''),
(17, 'jelwe','',''),
(18, 'jelwe','',''),
(19, 'jelwe','',''),
(20, 'jelwe','',''),
(21, 'jelwe','',''),
(22, 'jelwe','',''),
(23, 'jelwe','',''),
(24, 'jelwe','',''),
(25, 'jelwe','',''),
(26, 'jelwe','',''),
(27, 'jelwe','',''),
(28, 'jselwe','',''),
(29, 'ddd','',''),
(30, 'ddd','',''),
(31, 'jselwe','',''),
(32, 'ddd','',''),
(33, 'jselwe','',''),
(34, 'jelwe','',''),
(35, 'jelwe','',''),
(36, 'jelwe','',''),
(37, 'jelwe','',''),
(38, 'jelwe','',''),
(39, 'jelwe','',''),
(40, 'jelwe','',''),
(41, 'jelwe','',''),
(42, 'jelwe','',''),
(43, 'jelwe','',''),
(44, 'jelwe','',''),
(45, 'jelwe','',''),
(46, 'jelwe','',''),
(47, 'jelwe','',''),
(48, 'jelwe','',''),
(49, 'jelwe','',''),
(50, 'jelwe','',''),
(51, 'jelwe','',''),
(52, 'jelwe','',''),
(53, 'jelwe','',''),
(54, 'jelwe','',''),
(55, 'jselwe','',''),
(56, 'ddd','',''),
(57, 'ddd','',''),
(58, 'jselwe','',''),
(59, 'ddd','',''),
(60, 'jselwe','',''),
(61, 'jelwe','',''),
(62, 'jelwe','',''),
(63, 'jelwe','',''),
(64, 'jelwe','',''),
(65, 'jelwe','',''),
(66, 'jelwe','',''),
(67, 'jelwe','',''),
(68, 'jelwe','',''),
(69, 'jelwe','',''),
(70, 'jelwe','',''),
(71, 'jelwe','',''),
(72, 'jelwe','',''),
(73, 'jelwe','',''),
(74, 'jelwe','',''),
(75, 'jelwe','',''),
(76, 'jelwe','',''),
(77, 'jelwe','',''),
(78, 'jelwe','',''),
(79, 'jelwe','',''),
(80, 'jelwe','',''),
(81, 'jelwe','',''),
(82, 'jselwe','',''),
(83, 'ddd','',''),
(84, 'ddd','',''),
(85, 'jselwe','',''),
(86, 'ddd','',''),
(87, 'jselwe','',''),
(88, 'jelwe','',''),
(89, 'jelwe','',''),
(90, 'jelwe','',''),
(91, 'jelwe','',''),
(92, 'jelwe','',''),
(93, 'jelwe','',''),
(94, 'jelwe','',''),
(95, 'jelwe','',''),
(96, 'jelwe','',''),
(97, 'jelwe','',''),
(98, 'jelwe','',''),
(99, 'jelwe','',''),
(100, 'jelwe','',''),
(101, 'jelwe','',''),
(102, 'jelwe','',''),
(103, 'jelwe','',''),
(104, 'jelwe','',''),
(105, 'jelwe','',''),
(106, 'jelwe','',''),
(107, 'jelwe','',''),
(108, 'jelwe','','');

示例查询:

SELECT  hpt.productname,  hpt.description, hpt.pic, hpt.idphair 
FROM  hair_product_table hpt
INNER JOIN mix_product_tables AS mpt
UNION SELECT mpt.productname,  mpt.description, mpt.pic, mpt.idmix 
FROM mix_product_tables AS mpt limit 30;
+-------------+-------------+------+---------+
| productname | description | pic  | idphair |
+-------------+-------------+------+---------+
| jselwe      |             |      |       4 |
| ddd         |             |      |       5 |
| jselwe      |             |      |       6 |
| jelwe       |             |      |       7 |
| jelwe       |             |      |       8 |
| jelwe       |             |      |       9 |
| jelwe       |             |      |      10 |
| jelwe       |             |      |      11 |
| jelwe       |             |      |      12 |
| jelwe       |             |      |      13 |
| jelwe       |             |      |      14 |
| jelwe       |             |      |      15 |
| jelwe       |             |      |      16 |
| jelwe       |             |      |      17 |
| jelwe       |             |      |      18 |
| jelwe       |             |      |      19 |
| jelwe       |             |      |      20 |
| jelwe       |             |      |      21 |
| jelwe       |             |      |      22 |
| jelwe       |             |      |      23 |
| jelwe       |             |      |      24 |
| jelwe       |             |      |      25 |
| jelwe       |             |      |      26 |
| jelwe       |             |      |      27 |
| mix1        |             |      |       1 |
| mix2        |             |      |       2 |
| mix3        |             |      |       3 |
| mix4        |             |      |       4 |
| mix5        |             |      |       5 |
| jselwe      |             |      |      28 |
+-------------+-------------+------+---------+

同样的重新测试:http://rextester.com/edit/KTAR73194

对于显示所有产品，我没有问题

$query = mysqli_query($dbc,'SELECT  hpt.productname,  hpt.description, hpt.pic, hpt.idphair 
FROM  hair_product_table hpt
INNER JOIN mix_product_tables AS mpt
UNION SELECT mpt.productname,  mpt.description, mpt.pic, mpt.idmix 
FROM mix_product_tables AS mpt limit 30');

现在我想显示更多产品的链接

我创建了一个新页面ore_product_page.php。

当我点击此链接时使用 Ajax

<div id="show_more_product<?php echo $last_id_product ?>" title="Load more product">More product</div>要访问此页面，我发送左侧 ID这就是我遇到的问题，$last_id_product 取最后一个 id“id:8”而不是表 mix_product_table 的最后一个产品中的最后一个 id 联合“id:30”

我的问题是如何获得联合表的确切ID，或者我是否可以为从1到30开始的2个表创建一个临时列，例如rowNumber

谢谢

最佳答案

我认为你的过于复杂的问题的核心可以表达如下:

DROP TABLE IF EXISTS a;

CREATE TABLE a(id SERIAL PRIMARY KEY);

DROP TABLE IF EXISTS b;

CREATE TABLE b(id SERIAL PRIMARY KEY);

INSERT INTO a VALUES (1),(2),(3),(4),(5);

INSERT INTO b VALUES (3),(4),(5),(6),(7);

SELECT * FROM a UNION ALL SELECT * FROM b ORDER BY i;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  3 |
|  4 |
|  4 |
|  5 |
|  5 |
|  6 |
|  7 |
+----+

所以现在两个表困惑了。

这可以很容易地解决，如下所示。

SELECT 'a' source
     , id 
  FROM a 
 UNION 
SELECT 'b'
     , id
  FROM b 
 ORDER 
    BY source
     , id;
+--------+----+
| source | id |
+--------+----+
| a      |  1 |
| a      |  2 |
| a      |  3 |
| a      |  4 |
| a      |  5 |
| b      |  3 |
| b      |  4 |
| b      |  5 |
| b      |  6 |
| b      |  7 |
+--------+----+

关于php - 两个表并集的行数增加的临时列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/49421192/

25 4 0
文章推荐： mysql - 评估一列，使其等于某个值而不等于其他值？
文章推荐： android - 如何在Android中获取当前壁纸uri？
文章推荐： android - 停止在 Action Bar appcompat 中设置为 ActionView 的进度条
文章推荐： javascript - 我们如何进行动态范围界定以在 NodeJs 中实现事务跟踪？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com