在联接中,如何在所有列名之前添加其来源表的前缀 [英] In a join, how to prefix all column names with the table it came from
问题描述
我正在分析一个相当糟糕的旧数据库/代码库,试图通过将查询合并到联接中来减少服务器负载(包括电子邮件警报cron作业,该作业通常会调用超过一百万个单独的查询).
I'm analysing a rather horrible legacy database/codebase, trying to reduce server load by combining queries into joins (including an email alert cron job that typically invokes well over a million separate queries).
SELECT * FROM
class_alerts_holding ah
INNER JOIN class_listings l ON l.id = ah.lid
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid
这会吐出120列...
This spits out 120 columns...
aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ...
为了帮助我分析如何构造新查询,如果我可以在结果中的列前面加上来自JOIN的表的前缀,那将是很棒的事情.
To assist my analysis of how to construct the new queries it would be awesome if I could prefix the columns in the result with the table they came from in the JOIN e.g.
class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ...
有没有办法做到这一点?
Is there a way to achieve this?
推荐答案
您可以
select ah.*, l.*, u.*, pi.* from ...
那么这些列将至少按表的顺序返回.
then the columns will be returned ordered by table at least.
为了更好地区分每两组列,您还可以像这样添加定界符"列:
For better distinction between every two sets of columns, you could also add "delimiter" columns like this:
select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...
(已编辑,删除了不必要的显式别名,请参见注释.)
这篇关于在联接中,如何在所有列名之前添加其来源表的前缀的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!