dongll0502 2012-03-12 17:19
浏览 47
已采纳

只有在mysql中找到子查询时才有条件串联字符串?

So I have this select statement that I am trying to make more responsive.

I'm selecting products (items table) that got hit on a website that were listed as unavailable.

I then take the name of the product hit and see if there is any similar product in the database, and subselect the info for any similarly named products, to use as suggested alternates.

My problem is that now I want to display a url for any valid alternates, by concating a standard url with the alternate product id at the end. Unfortunately, I can't concat said url because alt_id is a field created by a subselect and doesn't exactly exist in the table itself, so I get the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'alt_id' in 'field list'' in /blah/ on line 34

Here is the starting query, which works:

$data = query_array(
"select unavailable_item_hits.itemid, items.Name as unavail_name, 

(select max(items.ItemId) from items join products on items.itemid = products.itemid join brands on items.brandcode = brands.code where items.name = unavail_name and items.flagstatus != 'U' and products.flagstatus != 'U' and brands.flagstatus != 'I' and items.ItemId !=unavailable_item_hits.ItemId) as alt_id,

 unavailable_item_hits.url as url_hit, unavailable_item_hits.marketer_tag, unavailable_item_hits.count as hit_count, unavailable_item_hits.datestatus as last_date_hit from unavailable_item_hits join items on unavailable_item_hits.ItemId = items.itemid order by alt_id is not null, hit_count desc;");

Which returns this data:

itemid  unavail_name    alt_id  url_hit     marketer_tag    hit_count   last_date_hit
7615    3602 Oxford         sb.local/product.php?ItemId=7615&ProductId=55555555555555555&ref=lsf    lsf     1   2012-02-02 18:53:37
9621    McTavish    10259   sb.local/product.php?ItemId=9621&ProductId=17246&ref=lsf    lsf     2   2012-02-02 18:53:59 

Now, the part that I'd like to add is something like this:

select concat('http://example.com/products.php?ItemId=', alt_id), null) as alt_url, ...

But that doesn't work because alt_id isn't defined yet. I don't know what to do from here, how to be able to use the data from that subselect in the final result. Maybe the only way is to do two different selects or something, but that will mean pulling the data out into an array in memory and then performing the subselect, which I'd like to avoid. Or maybe I have to perform the subselect again on the field that I want to manipulate like a string?

So any suggestions on clean ways to manipulate the results of a subselect?

  • 写回答

1条回答 默认 最新

  • douwen0612 2012-03-12 18:02
    关注

    First, I wouldn't bother the database server with things like string concatenation. Just append the alt_id to your url in the host language (PHP). Databases are good in storing and fetching large amounts of data, not calculating or string manipulation.

    If you really need to do this in the database, you can just repeat the subquery for your concat-call like

    select
        [...]
        concat('http://example.com/products.php?ItemId=', (select 
            max(items.ItemId) 
         from items 
         join products on items.itemid = products.itemid 
         join brands on items.brandcode = brands.code 
         where 
            items.name = unavail_name 
            and items.flagstatus != 'U' 
            and products.flagstatus != 'U' 
            and brands.flagstatus != 'I' 
            and items.ItemId !=unavailable_item_hits.ItemId)) as alt_url
         [...]
    

    It's not a pretty query but that doesn't really matter to the database server. SQL is a declarative language. You just specify what you want to have not how it should be produced. MySQL will (hopefully) see the repeated subquery and only execute it once for every tuple, or, even better, transform it into some join.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 微信会员卡等级和折扣规则
  • ¥15 微信公众平台自制会员卡可以通过收款码收款码收款进行自动积分吗
  • ¥15 随身WiFi网络灯亮但是没有网络,如何解决?
  • ¥15 gdf格式的脑电数据如何处理matlab
  • ¥20 重新写的代码替换了之后运行hbuliderx就这样了
  • ¥100 监控抖音用户作品更新可以微信公众号提醒
  • ¥15 UE5 如何可以不渲染HDRIBackdrop背景
  • ¥70 2048小游戏毕设项目
  • ¥20 mysql架构,按照姓名分表
  • ¥15 MATLAB实现区间[a,b]上的Gauss-Legendre积分