MySql 多项选择中的一行

Question

我不知道如何从表中获取一行:

我在一个范围/条件中搜索，我需要其中的 7 列:最高价格、最低价格、最后价格(如果且当价格 >=x 时，因此第一个价格 >=x 及其时间)， (如果且当价格<=y)。

表格是:

id (int, key unique)
date (varchar, don't need it) 
price (decimal) 
unix (int, key) (unix time)
ck (int, key)
fk (int, key)

我尝试了多个选择/连接并且它有效，但如果只有一个选择为空(也许价格不会 >=x)，它会返回整个空，无论如何我都需要返回结果...

SELECT *  FROM 
    (SELECT date , price as 'Sprc', unix as 'Sunx' 
    FROM prices WHERE
        (unix BETWEEN 1420185600 AND 1420192800) AND
        ck>0 AND fk=0 AND 
        price <= 1000 
    order by unix ASC limit 1) as tmp1
JOIN
    (SELECT date , price as 'TPprc', unix as 'TPunx'  
    FROM prices WHERE  
        (unix BETWEEN 1420185600 AND 1420192800) AND  
        ck>0 AND 
        fk=0 AND 
        price >= 100 
    order by unix ASC limit 1) as tmp2
JOIN
    (SELECT MIN(price) as 'min', MAX(price) as 'max'  
    FROM prices WHERE 
        ck>0 AND 
        fk=0 AND  
        (unix BETWEEN 1420185600 AND 1420192800) 
    order by unix ASC limit 1)  as tmp3
JOIN
    (SELECT date, price as 'last'  
    FROM prices WHERE 
        ck>0 AND 
        fk=0 AND 
        (unix BETWEEN 1420185600 AND 1420192800) 
    order by unix DESC limit 1)  as tmp4;

Answer 1

将单词 LEFT 放在每个连接语句前面。

这样做表明查询应返回第一个表中的所有行，无论是否在第二个表中找到匹配的行。