MYSQL ERROR 1248 (42000): 每个派生表都必须有自己的别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3363918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL ERROR 1248 (42000): Every derived table must have its own alias
提问by Matt Elhotiby
I cant for the life of me figure out why this is erroring
我一生都无法弄清楚为什么这是错误的
SELECT * FROM
SELECT
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent,
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id
FROM
wiki_city_list c join propertyinfo p on c.city_id=p.city
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (3958, 4576, 4577) and (Rent!='' or (Rentlow!='' and Renthigh!='')) ) as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut )
WHERE
pu.Status='Active'
and p.delete_date='0000-00-00'
GROUP BY
c.city_id, p.prop_ynow_id
UNION
SELECT
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent,
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id
FROM
wiki_city_list c join propertyinfo p on c.city_id=p.city
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (9744) and (Rent!='' or (Rentlow!='' and Renthigh!='')) ) as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut )
WHERE
pu.Status='Active'
and p.delete_date='0000-00-00'
GROUP BY
c.city_id, p.prop_ynow_id
UNION
SELECT
'' as prop_ynow_id,
id as propertyid,
0 as prop_add_value,
t.name as name,
'' as picture,
t.address as location,
t.city as city,
s.ShortCut as state,
t.zip as zip,
CAST(REPLACE(REPLACE(t.price,'$',''),',','') as UNSIGNED) as minrent,
'' as maxrent,
t.service as service,
'' as hood_id,
'' as phone,
t.latitude as latitude,
t.longitude as longitude,
t.bathrooms as minbath,
'' as maxbath,
t.bedrooms as minbed,
'' as maxbed,
t.url as url,
t.source_id as source_id,
t.source_name as source_name,
t.addresscode as addresscode,
t.citycode as citycode,
t.ctime as ctime,
t.paid as paid,
t.similar_url as similar_url,
t.created_at as created_at,
t.updated_at as updated_at,
SUBSTRING_INDEX(c.city_name,'_',1) as city_name,
t.service_listing_id as service_listing_id
FROM LBCPrimary.third_party_properties as t, LBCPrimary.wiki_city_list as c, LBCPrimary.state as s
WHERE
t.city in ( '230' )
and
address <> '' and
t.city = c.city_id and
c.city_state = s.stateid
order by t.ctime
desc
limit 46 as a limit 0,50
回答by a1ex07
It means exactly what it says - each derived table musthave an alias. SELECT a.* FROM (SELECT ....)a
这正是它所说的意思 - 每个派生表必须有一个别名。SELECT a.* FROM (SELECT ....)a
Update. This should work for you:
更新。这应该适合你：
SELECT xxx.* FROM
(
SELECT ....
FROM ....
UNION
(
SELECT ....
FROM .....
LIMIT 46
)
LIMIT 50
)xxx
回答by Matt Elhotiby
The very first line of your query is
您查询的第一行是
SELECT * FROM
which appears to be unnecessary (as all three UNIONed queries already include SELECT and FROM clauses).
这似乎是不必要的（因为所有三个 UNIONed 查询都已经包含 SELECT 和 FROM 子句）。
Removing this unnecessary line should resolve the problem; alternatively, adding a (just after the first line, and adding a ) QRYALIASat the end would also resolve the problem.
删除这条不必要的线路应该可以解决问题；或者，(在第一行之后添加一个，并) QRYALIAS在最后添加一个也可以解决问题。
