The usage of as in sql query statement;

is currently self-learning mysql,. When querying, sometimes you need to use as aliases, but after adding as aliases to the following two query statements, why is it incorrect?

1, select cat_id,goods_name,shop_price from goods where shop_price in (select max (shop_price) from goods group by cat_id) as alias, why is there an error?

2, select hid,tname,gid,mres,mtime from (m left join t on t.tid = m.hid) as alias;, there will also be errors,

3. If these two query statements get the following as alias, there will be no error. Why?

4. Where and how should as (alias) be used? thank you!

Mar.05,2021

Add an alias to the field in

query
select cat_id, goods_name as gname from goods
alias the table
select cat_id, goods_name from goods as g where g.cat_id > 1

select g.cat_id, g.goods_name, g.price as base_price, T.price as max_price from goods as g
left join (
 select gid, max(price) as price from order_good group by gid
) as T on g.id = T.gid

PS:SQL is written casually, not necessarily for


since you only posted the sql statement, I restored it locally according to the grammar you saw, and the following answers for you

1. The first sentence sql you use is where in () , which is followed by a collection of data. (select max (shop_price) from goods group by cat_id) this sentence is correct, but when you add as , it is wrong. Why? Because as I just said, where in is followed by a data collection, and what you need to do now is to define an alias for this collection, which is equivalent to a table, which must be wrong.

2. The second sentence is even more outrageous, obviously a direct syntax error, from is followed by a table, as definition of the new table name is not wrong, but you () in the sql is not a query of the table data, but link up with the above, of course, an error will be reported.

3. as alias get I think you typed the wrong word. It is to remove , and to remove as will not report an error. Of course, you made a grammatical mistake.

4. as generally defines an alias for the field of the query or a table . Let's not say much about the field, and mention the usage of the table

.
select * from (select * from [Table] where xxx) as newTable --as

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1be681d-3054a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1be681d-3054a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?