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.

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

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择