MySQL - 操作数应包含1个带opencart的列

Question

While working on a system I'm creating, I attempted to use the following query in my project:

SELECT o.order_id, 
(SELECT op.price, op.quantity, op.name FROM oc_order_product op 
RIGHT JOIN oc_order o ON op.order_id=o.order_id ) AS product, 
CONCAT(o.firstname, ' ', o.lastname) AS customer, 
(SELECT os.name FROM oc_order_status os 
WHERE os.order_status_id = o.order_status_id AND os.language_id = '1') AS status, 
o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified 
FROM `oc_order` o WHERE o.order_status_id > '0' ORDER BY o.order_id DESC LIMIT 0,20

but it warnings: Operand should contain 1 column(s), pls help me

Answer 1

A subquery in the SELECT list must return exactly one expression. A subquery that returns more than one expression is not valid.

The problem is the second line, that's a subquery in the SELECT list of the outer query. And there is more than one expression in the SELECT list of the subquery.

You also need to insure that any subquery in the SELECT list will return at most one row.

I suggest you consider a JOIN operation, rather than a correlated subquery, something like this:

SELECT o.order_id
     , op.price      AS product_price 
     , op.quantity   AS product_quantity
     , op.name       AS product_name
     , CONCAT(o.firstname, ' ', o.lastname) AS customer
     , ( SELECT os.name
           FROM oc_order_status os 
          WHERE os.order_status_id = o.order_status_id 
            AND os.language_id = '1'
          LIMIT 1   
       ) AS status
    , o.total
    , o.currency_code
    , o.currency_value
    , o.date_added
    , o.date_modified 
 FROM oc_order o 
 LEFT
 JOIN oc_order_product op
   ON op.order_id=o.order_id
WHERE o.order_status_id > '0' 
ORDER BY o.order_id DESC
LIMIT 0,20

I left the subquery to return status in the query, as a demonstration of a subquery that is valid in terms of SQL syntax. I added the LIMIT 1 clause, to insure the query doesn't throw an error at execution time, if that subquery happens to return more than one row.

(It's not clear what problem you were trying to solve by using a subquery in the SELECT list.)