如何从一个表将两个查询合并到sql中的一个查询数据库？

mzmfm0qo 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(392)

面对这样的问题——我不能把这两个要求结合起来，怎么办？一个表中的所有数据

SELECT
  max(f.price) AS max_rent,
  min(f.price) AS min_rent
FROM Commercial f
WHERE f.type = 1;
SELECT
  max(f.price) AS max_sale,
  min(f.price) AS min_sale
FROM Commercial f
WHERE f.type = 0;

收到结果。2张有结果的表格

+---------+---------+ +---------+---------+
| max_sale| min_sale| |max_sale | min_sale|
+---------+---------+ +---------+---------+
|  23     |  45     | |   15    |  52     | 
+---------+---------+ +---------+---------+

预期结果。1个结果表

+---------+---------+---------+---------+
| max_sale| min_sale| max_sale| min_sale|
+---------+---------+---------+---------+
|  23     |  45     |   15    |  52     | 
+---------+---------+---------+---------+

sql mysql union

来源：https://stackoverflow.com/questions/48206718/how-to-combine-2-queries-to-one-query-database-in-sql-from-single-table

3条答案

按热度按时间

w8rqjzmb1#

用这个怎么样 group by ?

SELECT f.type,
       max(f.price) AS max_price_for_sale,
       min(f.price) AS min_price_for_sale
FROM Commercial f
WHERE f.type IN (0, 1)
GROUP BY f.type;

赞(0）回复(0）举报 2021-06-25

t9aqgxwy2#

只需在中间添加一个并集

SELECT
  max(f.price) AS max_price_for_rent,
  min(f.price) AS min_price_for_rent
FROM Commercial f
WHERE f.type = 1;

UNION 

SELECT
  max(f.price) AS max_price_for_sale,
  min(f.price) AS min_price_for_sale
FROM Commercial f
WHERE f.type = 0;

如果要保留副本，请添加 UNION ALL 但是，对于此特定查询，使用 GROUP BY 戈登林诺夫的回答会更好。

赞(0）回复(0）举报 2021-06-25

0qx6xfy63#

这对你有用吗？它应该沿一行返回4列：

SELECT
    C1.max_rent,
    C1.min_rent,
    C0.max_rent,
    C0.min_rent
FROM
    (
    SELECT
        max(f.price) AS max_rent,
        min(f.price) AS min_rent
    FROM Commercial f
    WHERE f.type = 1) C1,
    (
    SELECT
        max(f.price) AS max_rent,
        min(f.price) AS min_rent
    FROM Commercial f
    WHERE f.type = 0) C0

赞(0）回复(0）举报 2021-06-25

我来回答

如何从一个表将两个查询合并到sql中的一个查询数据库？

3条答案

相关问题

热门标签

最新问答