我对SQL有疑问。下面是Prestashop 1.6中来自订单控制器的查询。问题是。为什么我有两个ps_orders别名?请查看以下查询:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS
a.`id_order`,
`reference`,
`total_paid_tax_incl`,
`payment`,
a.`date_add` AS `date_add`,
a.id_currency,
a.id_order AS id_pdf,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,
osl.`name` AS `osname`,
os.`color`,
IF(
(SELECT COUNT(id_customer)
FROM `ps_orders` so
WHERE so.id_customer = a.id_customer) = 1, "Tak", "Nie") AS first_order,
IF(
(SELECT so.id_order
FROM `ps_orders` so
WHERE so.id_customer = a.id_customer
AND so.id_order < a.id_order
LIMIT 1) > 0, 0, 1) AS NEW,
country_lang.name AS cname,
IF(a.valid, 1, 0) badge_success
FROM `ps_orders` a
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
LEFT JOIN `ps_address` address ON address.id_address = a.id_address_delivery
LEFT JOIN `ps_country` country ON address.id_country = country.id_country
LEFT JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country`
AND country_lang.`id_lang` = 6)
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state`
AND osl.`id_lang` = 6)
WHERE 1
ORDER BY a.`id_order` DESC
LIMIT 0, 50一旦我在另一个so.id_order中有了so.id_order。也许我错了,但我认为这是同一个ps_orders。第一个声明是ps_orders so,第二个声明是ps_orders a?为什么有人在同一张桌子上使用两个别名?
发布于 2018-03-21 11:23:14
一般查询如下:
SELECT
A.SomeColumn
FROM
YourTable A如果您有子查询,那么
SELECT
A.SomeColumn,
(SELECT SomeOtherColumn FROM AnotherTable) ColumnAlias
FROM
YourTable A如果您有一个与的子查询相同的表,您可能需要使用不同的别名来区分它们!
SELECT
A.SomeColumn,
(SELECT B.SomeColumn FROM YourTable B) ColumnAlias
FROM
YourTable A子查询中引用的表中的列不能在外部引用:
SELECT
A.SomeColumn,
(SELECT B.SomeOtherColumn FROM YourTable B) ColumnAlias,
B.SomeOtherColumn -- What table is B here? Outside scope!
FROM
YourTable A但是来自外部的列可以在内部引用!
SELECT
A.SomeColumn,
(SELECT B.SomeOtherColumn FROM YourTable B WHERE B.SomeOtherColumn = A.SomeColumn) ColumnAlias,
FROM
YourTable APD:在这些示例中,我假设子查询返回1行。
发布于 2018-03-21 11:23:08
一个别名用于主查询,另一个用于子查询。
主查询和每个子查询必须使用别名,因为它们都引用与主查询相同的表。
这是子查询可以与主查询中使用的同一表中的记录关联的唯一方法--就像在执行自联接时一样--一旦在查询中拥有相同的表(或视图)多次,就必须对其使用不同的别名。
https://stackoverflow.com/questions/49404896
复制相似问题