MySQL-查询两张表按照某字段排序并分页

java学习 专栏收录该内容
14 篇文章 0 订阅
SELECT
	*
FROM
			(
				SELECT
					a.id AS "id",
					a.`name` AS "name",
					a.unit_type AS "unitType",
					a.price_show AS "priceShow",
					a.price_sell AS "priceCustom",
					a.price_cost AS "priceCost",
					a.specification AS "yzbSpecification",
					a.categorya_id AS "categorya.id",
					a.categoryb_id AS "categoryb.id",
					a.categoryc_id AS "categoryc.id",
					a.categoryd_id AS "categoryd.id",
					a.type AS "type",
					a.url AS "url",
					a.image_url AS "imageUrl",
					a.count AS "count",
					a.city_id AS "city.id",
					yt.id AS "merchant.id",
					yt.`name` AS "merchant.name",
					yt.brand_name AS "merchant.brandName",
					categorya.`name` AS "categorya.name",
					categoryb.`name` AS "categoryb.name",
					categoryc.`name` AS "categoryc.name",
					categoryd.`name` AS "categoryd.name",
					s.`name` AS "yzbSpecification.name"
				FROM
					yzb_materials a
				LEFT JOIN yzb_merchant yt ON yt.id = a.merchant_id
				LEFT JOIN yzb_materials_category categorya ON categorya.id = a.categorya_id
				LEFT JOIN yzb_materials_category categoryb ON categoryb.id = a.categoryb_id
				LEFT JOIN yzb_materials_category categoryc ON categoryc.id = a.categoryc_id
				LEFT JOIN yzb_materials_category categoryd ON categoryd.id = a.categoryd_id
				LEFT JOIN yzb_specification_data s ON s.id = a.specification
				WHERE
					a.del_flag = '0'
		UNION ALL
					SELECT
						a.id AS "id",
						a.`name` AS "name",
						a.unit_type AS "unitType",
						a.price_show AS "priceShow",
						a.price_custom AS "priceCustom",
						a.price_cost AS "priceCost",
						a.specification AS "yzbSpecification",
						a.categorya_id AS "categorya.id",
						a.categoryb_id AS "categoryb.id",
						a.categoryc_id AS "categoryc.id",
						a.categoryd_id AS "categoryd.id",
						"2" AS "type",
						a.details_url AS "url",
						a.image_url AS "imageUrl",
						a.count AS "count",
						a.city_id AS "city.id",
						ycm.id AS "merchant.id",
						ycm.`name` AS "merchant.name",
						ycm.brand_name AS "merchant.brandName",
						categorya.`name` AS "categorya.name",
						categoryb.`name` AS "categoryb.name",
						categoryc.`name` AS "categoryc.name",
						categoryd.`name` AS "categoryd.name",
						s.`name` AS "yzbSpecification.name"
					FROM
						yzb_com_materials a
					LEFT JOIN yzb_com_merchant ycm ON ycm.id = a.merchant_id
					LEFT JOIN yzb_materials_category categorya ON categorya.id = a.categorya_id
					LEFT JOIN yzb_materials_category categoryb ON categoryb.id = a.categoryb_id
					LEFT JOIN yzb_materials_category categoryc ON categoryc.id = a.categoryc_id
					LEFT JOIN yzb_materials_category categoryd ON categoryd.id = a.categoryd_id
					LEFT JOIN yzb_com_store store ON store.id = a.store_id
					LEFT JOIN yzb_specification_data s ON s.id = a.specification
					WHERE
						a.del_flag = '0'
				) as bbb
ORDER BY
	priceShow ASC
LIMIT 100


  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值