mysql-union选择先排序

rmbxnbpk  于 2021-06-19  发布在  Mysql
关注(0)|答案(3)|浏览(412)

我有sql SELECTUNION (SELECT...) ```
SELECT virtuemart_product_id
FROM #__virtuemart_products
WHERE product_special = 1
UNION
(SELECT virtuemart_product_id
FROM #__virtuemart_product_badges
WHERE badge = 3)

  1. 此选择返回virtuemart\u product\u id: `12345,54321,...,789,987` -例如,最后一个id来自union(#u virtuemart_product_badges)表。
  2. 我需要对结果进行排序,以便 `UNION` table是第一个( `union_select_virtuemart_product_id` 之后呢 `first_select_virtuemart_product_id` ). 如果我试试这个:

...
UNION
(SELECT virtuemart_product_id
FROM #__virtuemart_product_badges AS badge
WHERE badge = 3)
ORDER BY badge.virtuemart_product_id

  1. 结果当然是错误的。分别是任何 `ORDER BY` 除外 `...ORDER BY virtuemart_product_id` 以错误结束。
  2. 这有可能吗?
  3. 谢谢
2eafrhcq

2eafrhcq1#

如果要首先从联合表中获取结果,请首先在该联合后选择该表,然后再选择另一个表。就像this:-

  1. (SELECT virtuemart_product_id
  2. FROM #__virtuemart_product_badges
  3. WHERE badge = 3)
  4. UNION
  5. SELECT virtuemart_product_id
  6. FROM #__virtuemart_products
  7. WHERE product_special = 1
1szpjjfi

1szpjjfi2#

你可以试试这个。它创建一个人工值,指示数据来自哪个表,然后在id值之前按该值排序。通过更改分配给数据集的值和排序顺序,可以更改首先输出的值:

  1. (SELECT virtuemart_product_id, 0 AS dataset
  2. FROM #__virtuemart_products
  3. WHERE product_special = 1)
  4. UNION
  5. (SELECT virtuemart_product_id, 1 AS dataset
  6. FROM #__virtuemart_product_badges
  7. WHERE badge = 3)
  8. ORDER BY dataset DESC, virtuemart_product_id

编辑
因为要求只返回一列( virtuemart_product_id ),则需要将此查询 Package 在子查询中,然后 SELECT virtuemart_product_id 从中:

  1. SELECT virtuemart_product_id
  2. FROM ((SELECT virtuemart_product_id, 0 AS dataset
  3. FROM #__virtuemart_products
  4. WHERE product_special = 1)
  5. UNION
  6. (SELECT virtuemart_product_id, 1 AS dataset
  7. FROM #__virtuemart_product_badges
  8. WHERE badge = 3)
  9. ORDER BY dataset DESC, virtuemart_product_id) v
展开查看全部
ffvjumwh

ffvjumwh3#

尝试进行一些嵌套查询:

  1. select * from (YOUR ALL QUERY) AS p order by virtuemart_product_id

相关问题