从选择子查询中获取多个列

SELECT 
   *, 
   p.name AS name, 
   p.image, 
   p.price,
   ( 
       SELECT ps.price 
       FROM product_special ps 
       WHERE p.id = ps.id
         AND ps.date < NOW() 
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS special_price,
   ( 
       SELECT ps.date 
       FROM product_special ps 
       WHERE p.id = ps.id
         AND ps.date < NOW() 
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

如您所见，我在重复相同的子查询只是为了获得另一列。我想知道是否有更好的方法吗？

id是两个表中的主键。如果有帮助，我可以毫无问题地使product_special.priority唯一。

#1 楼

假设组合product_special.id, product_special.priority是唯一

 SELECT p.*, special_price,special_date
 FROM product p
 LEFT JOIN 
 (
     SELECT ps.id, ps.price as special_price, ps.`date` as special_date
     FROM product_special ps
     INNER JOIN 
     (
       SELECT id, MIN(priority) as min_priority 
       FROM product_special
       GROUP BY id
     ) ps2 
     ON (ps2.id = ps.id)
 )a ON (a.id=p.id)

#2 楼

除非打算将字段返回为special_price.price和date.date，否则为什么不对子查询中的名称使用别名？例如

SELECT p.*, p.name AS  name, p.image, p.price, ps.*
FROM product p
LEFT JOIN
   (SELECT
      psi.price as special_price, psi.date as my_date 
    FROM product_special psi
    WHERE 
      p.id = psi.id AND
      psi.date < NOW()
    ORDER BY psi.priority ASC, LIMIT 1
   ) AS ps ON
  p.id = ps.id

您的查询语言是否具有FIRST（）聚合函数？不知道是否可以将product_special的PK设置为id和优先级（均为ASC排序）之间的组合，并将ORDER子句更改为GROUP BY id, psi.priority

您可以完全删除ORDER BY子句并使用HAVING MIN(psi.priority)

#3 楼

请注意，SQL Server的“交叉应用”机制可以解决此问题，但是在PostgreSQL中不可用。基本上，这是它们的解决方案，用于将参数（通常是对当前表表达式外部的列的引用）传递给在FROM子句中称为表表达式的函数。但是事实证明，它对于希望避免子查询嵌套的另一级别或将事物从FROM子句移到SELECT子句的所有情况都是有用的。 PostgreSQL通过提供一种例外来实现此目的-如果表达式是一个简单的函数调用，但严格来说不是嵌入式SELECT，则可以传递类似的参数。所以

left join highestPriorityProductSpecial(p.id) on true

可以，但不是

left join (select * from product_special ps where ps.id = p.id order by priority desc limit 1) on true

即使该函数的定义恰恰是。

所以，实际上，这是一个方便的解决方案（至少在9.1中）：通过执行函数内部的限制，制作一个函数以提取最高优先级的行。

但是函数具有缺点，即查询计划不会显示其内部正在发生什么，并且我相信它将始终选择嵌套循环联接，即使那可能不是最佳选择。

交叉应用从9.3（2013年发布）开始在Postgres中可用，但是他们选择遵循SQL标准并使用标准的横向运算符。在第二个查询中，将left join替换为left join横向

– a_horse_with_no_name
16-4-21在10:29

#4 楼

尝试以下SQL命令：

SELECT p.name,p.image,p.price,pss.price,pss.date
FROM Product p OUTER APPLY(SELECT TOP(1)* 
FROM ProductSpecial ps
WHERE p.Id = ps.Id ORDER BY ps.priority )as pss

请您为您的答案添加更多信息

–艾哈迈德·阿布哈斯纳（Ahmad Abuhasna）
16年4月21日在10:03

有问题的代码使用LIMIT且未使用DBMS标记（因此可能是MySQL或Postgres或SQLite或其他一些dbms）。答案中的代码使用OUTER APPLY和TOP，因此它将仅在没有LIMIT的SQL Server（和Sybase）中工作。

–超立方体ᵀᴹ
16年4月21日在10:18

这仅适用于sql server，仅适用于其他数据库，我们可以在select语句中使用内部查询。

–SANTOSH APPANA
16年5月6日在11:17

在Postgres中，没有OUTER APPLY，但是有LATERAL，应该等效。使用它的示例：stackoverflow.com/a/47926042/4850646

–卢卡斯·巴斯克罗托（Lucas Basquerotto）
18年11月12日13:36

#5 楼

受dezso的答案的启发https://dba.stackexchange.com/a/222471/127433
我正在使用数组解决PostgreSQL中的问题，如下所示：

SELECT 
   *, 
   p.name AS name, 
   p.image, 
   p.price,
   ( 
       SELECT ARRAY[ps.price, ps.date]
       FROM product_special ps 
       WHERE p.id = ps.id
         AND ps.date < NOW() 
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS special_price_and_date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

诚然，它仍然只是一列，但是在我的代码中，我可以轻松访问这两个值。
希望它也对您有用。

#6 楼

我只想把它放在这里作为万不得已的方法，适用于所有使用不支持一个或多个其他答案的数据库引擎的人...

您可以使用以下内容：

SELECT (col1 || col2) as col3

（使用分隔符，或将col1和col2格式化为特定长度。）
然后再使用子字符串绘制数据。

我希望有人发现它很有用。

#7 楼

我非常喜欢“ array”答案，因此我想使用通用信息模式为PostgreSQL 9.3添加完整的示例。这使我们能够保留相关的子查询及其所有功能。我一定会把它放在后兜里。
请注意，数组元素不能具有混合类型，至少在9.3上是这样。

SELECT
  table_data.table_schema,
  table_data.table_name,
  table_data.column_data[2] AS num_fields,
  table_data.column_data[3] AS min_column
FROM ( 
  SELECT table_schema,
         table_name,
         (SELECT ARRAY[c.table_name, COUNT(1)::text, MIN(c.column_name)]
            FROM information_schema.columns c
           WHERE c.table_name = t.table_name
             AND c.table_schema = t.table_schema
           GROUP BY c.table_name
         ) AS column_data
    FROM information_schema.tables t
   WHERE table_schema = 'information_schema'
     AND table_name LIKE 'column%'
) AS table_data;

    table_schema    |     table_name      | num_fields |        min_column
--------------------+---------------------+------------+--------------------------
 information_schema | column_domain_usage | 7          | column_name
 information_schema | column_privileges   | 8          | column_name
 information_schema | column_udt_usage    | 7          | column_name
 information_schema | columns             | 44         | character_maximum_length
 information_schema | column_options      | 6          | column_name

#8 楼

在DB2 z / OS版中，使用pack和unpack函数返回子选择中的多列。

SELECT 
   *, 
   p.name AS name, 
   p.image, 
   p.price,
    unpack((select PACK (CCSID 1028,
               ps.price,
               ps.date)
         FROM product_special ps 
       WHERE p.id = ps.id
         AND ps.date < NOW() 
       ORDER BY ps.priority ASC, LIMIT 1)) .* AS (SPECIAL_PRICE double, DATE date)
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id);

编程黑洞网