当前位置: 首页 > 面试题库 >

MYSQL错误1248(42000):每个派生表必须具有自己的别名

祁渊
2023-03-14
问题内容

我无法为自己的生活弄清楚为什么这是错误的

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

问题答案:

它的含义与所说的完全一样-每个派生表都 必须 有一个别名。SELECT a.* FROM (SELECT ....)a

更新。这应该为您工作:

SELECT xxx.* FROM 
(
    SELECT ....
    FROM ....
    UNION
    (
       SELECT ....
       FROM .....
       LIMIT 46
    )
    LIMIT 50
)xxx


 类似资料:
  • 问题内容: 运行以下查询时出现该错误: 我知道我需要添加别名,但是我不确定在哪里 问题答案: 您需要为子查询添加别名,并且需要将条件应用于您合并的两个查询: 或返回数据,以便您可以在外部查询中应用条件:

  • 问题内容: 我在MySQL上运行此查询 它给出了这个错误: 每个派生表必须具有自己的别名。 是什么导致此错误? 问题答案: 每个派生表(AKA子查询)确实必须有一个别名。也就是说,括号中的每个查询都必须被赋予一个别名(),该别名可以在外部查询的其余部分中用于引用它。 当然,对于您而言,整个查询可以替换为:

  • 问题内容: 在MySQL中使用此查询时出现错误。 查询逻辑是正确的,我已经在Oracle中进行了尝试,并且运行正常,但是在MySQL中运行时出现错误。 我查看了关于StackOverflow的先前问题,但没有找到什么对我有帮助。 这是查询: 这是错误: 问题答案: 您需要为子查询提供别名,如下所示: 从文档中, 子查询在SELECT语句的FROM子句中是合法的。实际语法为: SELECT … FR

  • 问题内容: 我有一个连接到数据库的应用程序,如果名称不存在该表,则创建一个表,并添加新的注册用户信息,以便我可以将密码散列到我的登录框中,并将其与数据库匹配,也将被散列。目前,我有以下错误代码。 此代码段 在主班 问题答案: 您尚未指定任何列,这正是错误消息告诉您的内容。正确的语法是

  • 问题内容: 我是React的新手,我想知道这里的标准是什么。 想象一下,我有一个像这样的反应路由器: 现在我想删除如果设置为的两条路线,因此一种理智的做法如下所示: 但是有两条路线,React返回错误: 表达式必须具有一个父元素。 我不想在这里使用多个ifs。React首选的处理方式是什么? 问题答案: 将它们放在一个数组中(也分配键): 使用最新的React版本,您也可以这样尝试 :