mysql - 如何在 JOINED 表的 SQL UNION 中 ORDER BY 未在结果中显示有序列
问题描述
我正在尝试获取两个连接结果将被联合并按几列排序的表。我发现在每个联合选择中都必须提到排序的列。但我不希望有序列出现在结果中。可能吗?也许还有其他方法可以获得我想要的结果。
这是我的查询:
SELECT coupon_id,
coupon_name,
real_price,
discount_price,
difference_percent,
discount_percent,
payment_price,
photo,
company_id,
company_name
FROM
(SELECT cpn.id AS coupon_id,
cpn.name_ru AS coupon_name,
cpn.real_price,
cpn.discount_price,
cpn.difference_percent,
cpn.discount_percent,
cpn.payment_price,
cp.photo,
com.id company_id,
com.name_ru AS company_name
FROM coupon cpn
LEFT JOIN coupon_photo cp ON cpn.id = cp.coupon_id
LEFT JOIN company com ON cpn.company_id = com.id
WHERE cpn.id NOT IN
(SELECT coupon_id AS id
FROM transaction
WHERE Date_add(created_time, interval 10 MINUTE) > Now()
AND status = 'pending')
AND cpn.exp_date > Now()
AND cpn.valid_date > Now()
AND cpn.available > 0
UNION SELECT cpn.id AS coupon_id,
cpn.name_ru AS coupon_name,
cpn.real_price,
cpn.discount_price,
cpn.difference_percent,
cpn.discount_percent,
cpn.payment_price,
cp.photo,
com.id company_id,
com.name_ru AS company_name
FROM coupon cpn
LEFT JOIN coupon_photo cp ON cpn.id = cp.coupon_id
LEFT JOIN company com ON cpn.company_id = com.id
LEFT JOIN transaction trans ON cpn.id = trans.coupon_id
WHERE cpn.exp_date > Now()
AND cpn.valid_date > Now()
AND Date_add(trans.created_time, interval 10 MINUTE) > NOW()
AND trans.status = 'pending'
AND (cpn.available - trans.amount) > 0 GROUP BY cpn.id) results
ORDER BY cpn.advertised DESC,
cpn.sold DESC,
cpn.views DESC
解决方案
您的results
子查询不包含您要尝试的列order by
。您需要将它们添加到联合的两个部分。您不必将它们添加到您的选择中,但它们需要出现在子查询中,以便您可以在ORDER BY
.
SELECT coupon_id,
coupon_name,
real_price,
discount_price,
difference_percent,
discount_percent,
payment_price,
photo,
company_id,
company_name
FROM
(SELECT cpn.id AS coupon_id,
cpn.name_ru AS coupon_name,
cpn.real_price,
cpn.discount_price,
cpn.difference_percent,
cpn.discount_percent,
cpn.payment_price,
cp.photo,
cpn.advertised,
cpn.sold,
cpn.views,
com.id company_id,
com.name_ru AS company_name
FROM coupon cpn
LEFT JOIN coupon_photo cp ON cpn.id = cp.coupon_id
LEFT JOIN company com ON cpn.company_id = com.id
WHERE cpn.id NOT IN
(SELECT coupon_id AS id
FROM transaction
WHERE Date_add(created_time, interval 10 MINUTE) > Now()
AND status = 'pending')
AND cpn.exp_date > Now()
AND cpn.valid_date > Now()
AND cpn.available > 0
UNION SELECT cpn.id AS coupon_id,
cpn.name_ru AS coupon_name,
cpn.real_price,
cpn.discount_price,
cpn.difference_percent,
cpn.discount_percent,
cpn.payment_price,
cp.photo,
cpn.advertised,
cpn.sold,
cpn.views,
com.id company_id,
com.name_ru AS company_name
FROM coupon cpn
LEFT JOIN coupon_photo cp ON cpn.id = cp.coupon_id
LEFT JOIN company com ON cpn.company_id = com.id
LEFT JOIN transaction trans ON cpn.id = trans.coupon_id
WHERE cpn.exp_date > Now()
AND cpn.valid_date > Now()
AND Date_add(trans.created_time, interval 10 MINUTE) > NOW()
AND trans.status = 'pending'
AND (cpn.available - trans.amount) > 0 GROUP BY cpn.id) results
ORDER BY advertised DESC,
sold DESC,
views DESC
正如 Strawberry 所指出的,GROUP BY
当您不聚合任何内容时,这并不是最佳实践。在这种情况下,由于 MySQL 使用 ANY_VALUE(),它可能会导致意外结果。它返回 GROUP BY 组中某行的值。它返回哪一行是不确定的。这意味着它完全取决于 MySQL 服务器。
我假设您使用它来避免重复行。您应该找到另一种重复数据删除方法。
此外,执行 LEFT JOIN 但过滤 JOIN 右侧的元素与执行 INNER JOIN 相同。只要确保这与您要实现的目标一致。
推荐阅读
- c++ - SFML 具有随机值的多个对象及其冲突
- matlab - 如何防止表值中的精度损失?
- .net - Office365 和 Visual Studio 2010 是否有 PIA?
- snowflake-cloud-data-platform - 我需要为 Snowflake 和 On Premise 之间的数据传输付费吗?
- css - 垂直滚动条可见性
- vba - 如何在电子邮件末尾嵌入照片?
- javascript - Safari浏览器中的Jquery datepicker错误
- flask - 使用 Flask 中的 HTTP 307 临时重定向将文件上传到第三方 API
- qt - 非 html 内容的 QTextDocument 样式表
- sql - pyodbc MS Access Driver 语法错误,但语法看起来正确