如何在 SQL 语句中包含许多“子”查询来生成图像的文件路径？

Question

您好，

使用此查询将自定义 OS Commerce mySQL 数据库中的数据额外添加到 CSV 文件中。

SELECT products_id,
max( if( products_extra_fields_id = '1', products_extra_fields_value, 0 ) ) AS band_avail, 
max( if( products_extra_fields_id = '2', products_extra_fields_value, 0 ) ) AS 1_gal_avail,
max( if( products_extra_fields_id = '3', products_extra_fields_value, 0 ) ) AS 2_gal_avail,
max( if( products_extra_fields_id = '4', products_extra_fields_value, 0 ) ) AS 5_gal_avail,
max( if( products_extra_fields_id = '5', products_extra_fields_value, 0 ) ) AS class,  
max( if( products_extra_fields_id = '6', products_extra_fields_value, 0 ) ) AS height, 
max( if( products_extra_fields_id = '7', products_extra_fields_value, 0 ) ) AS growth_habit,
max( if( products_extra_fields_id = '8', products_extra_fields_value, 0 ) ) AS color,
max( if( products_extra_fields_id = '9', products_extra_fields_value, 0 ) ) AS bloom_size,
max( if( products_extra_fields_id = '10', products_extra_fields_value, 0 ) ) AS bloom_type,
max( if( products_extra_fields_id = '11', products_extra_fields_value, 0 ) ) AS rebloom,
max( if( products_extra_fields_id = '12', products_extra_fields_value, 0 ) ) AS fragrance,
max( if( products_extra_fields_id = '13', products_extra_fields_value, 0 ) ) AS hybridizer,
max( if( products_extra_fields_id = '14', products_extra_fields_value, 0 ) ) AS date_introduced,
max( if( products_extra_fields_id = '15', products_extra_fields_value, 0 ) ) AS disease_resistant,
max( if( products_extra_fields_id = '16', products_extra_fields_value, 0 ) ) AS shade_tolerance,
max( if( products_extra_fields_id = '17', products_extra_fields_value, 0 ) ) AS thorns,
max( if( products_extra_fields_id = '23', products_extra_fields_value, 0 ) ) AS new_rose,
max( if( products_extra_fields_id = '24', products_extra_fields_value, 0 ) ) AS hips,
max( if( products_extra_fields_id = '25', products_extra_fields_value, 0 ) ) AS fall_color,
max( if( products_extra_fields_id = '26', products_extra_fields_value, 0 ) ) AS difficult_locations,
max( if( products_extra_fields_id = '27', products_extra_fields_value, 0 ) ) AS good_for_cutting,
max( if( products_extra_fields_id = '28', products_extra_fields_value, 0 ) ) AS good_for_drying,
max( if( products_extra_fields_id = '29', products_extra_fields_value, 0 ) ) AS ground_cover,
max( if( products_extra_fields_id = '30', products_extra_fields_value, 0 ) ) AS hedge,
max( if( products_extra_fields_id = '31', products_extra_fields_value, 0 ) ) AS pots,
max( if( products_extra_fields_id = '32', products_extra_fields_value, 0 ) ) AS price_and_size,
max( if( products_extra_fields_id = '34', products_extra_fields_value, 0 ) ) AS image_type,
max( if( products_extra_fields_id = '35', products_extra_fields_value, 0 ) ) AS date_search_range,
max( if( products_extra_fields_id = '36', products_extra_fields_value, 0 ) ) AS image_of_bush,
max( if( products_extra_fields_id = '37', products_extra_fields_value, 0 ) ) AS image_prefix,
max( if( products_extra_fields_id = '38', products_extra_fields_value, 0 ) ) AS sort_id,
max( if( products_extra_fields_id = '39', products_extra_fields_value, 0 ) ) AS zone,
max( if( products_extra_fields_id = '41', products_extra_fields_value, 0 ) ) AS status,
max( if( products_extra_fields_id = '42', products_extra_fields_value, 0 ) ) AS climbing,
max( if( products_extra_fields_id = '43', products_extra_fields_value, 0 ) ) AS crl,
max( if( products_extra_fields_id = '44', products_extra_fields_value, 0 ) ) AS band_sales,
max( if( products_extra_fields_id = '45', products_extra_fields_value, 0 ) ) AS gallon_sales,
max( if( products_extra_fields_id = '46', products_extra_fields_value, 0 ) ) AS 5_gallon_sales,
max( if( products_extra_fields_id = '47', products_extra_fields_value, 0 ) ) AS preorder_date,
max( if( products_extra_fields_id = '48', products_extra_fields_value, 0 ) ) AS preorder_inventory,
max( if( products_extra_fields_id = '49', products_extra_fields_value, 0 ) ) AS preband_sales,
max( if( products_extra_fields_id = '50', products_extra_fields_value, 0 ) ) AS pregallon_inventory,
max( if( products_extra_fields_id = '51', products_extra_fields_value, 0 ) ) AS pregallon_sales,
max( if( products_extra_fields_id = '52', products_extra_fields_value, 0 ) ) AS map_location,
max( if( products_extra_fields_id = '53', products_extra_fields_value, 0 ) ) AS price_grouping,
max( if( products_extra_fields_id = '54', products_extra_fields_value, 0 ) ) AS collection,
max( if( products_extra_fields_id = '55', products_extra_fields_value, 0 ) ) AS limited_stock,
max( if( products_extra_fields_id = '56', products_extra_fields_value, 0 ) ) AS awards,
max( if( products_extra_fields_id = '57', products_extra_fields_value, 0 ) ) AS spring_crop
FROM products_to_products_extra_fields GROUP BY products_id

生成此结果集（这使得将产品导入新购物车变得容易！）：

"products_id","band_avail","1_gal_avail","2_gal_avail","5_gal_avail","class","height","growth_habit","color","bloom_size","bloom_type","rebloom","fragrance","hybridizer","date_introduced","disease_resistant","shade_tolerance","thorns","new_rose","hips","fall_color","difficult_locations","good_for_cutting","good_for_drying","ground_cover","hedge","pots","price_and_size","image_type","date_search_range","image_of_bush","image_prefix","sort_id","zone","status","climbing","crl","band_sales","gallon_sales","5_gallon_sales","preorder_date","preorder_inventory","preband_sales","pregallon_inventory","pregallon_sales","map_location","price_grouping","collection","limited_stock","awards","spring_crop"
"0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"
"1","93","0","Out of Stock","0","Rambler","15-20 feet","No Entry","Light Pink","Bloom Size 4","Loosely Double","Rebloom rr","Fragrance fff","Barbier","1921","No Entry","No Entry","Hooked","No","No","No","dl_Yes","No","No","No","No","No","Standard","Vertical Image","1920 to 1929","No Image of Bush","Albertine","Sort_A_C","Zone 7","Active_On_Website","On_Climber_List","Not_On_Combined_Rose_List","0","0","0","February","0/12/07","0","0","0","0","0","0","0","0","0"
"2","51","0","Out of Stock","0","Large Flowered Climber","10-15 feet","Climbing","Apricot and Apricot Blend","Bloom Size 4","Very Fully Double","Rebloom 0","Fragrance fff","Kordes","1956","Very Disease Resistant","No Entry","No Entry","No","No","No","No","c_Yes","No","No","No","No","Standard","Horizontal Image","1950 to 1959","Horizontal Image of Bush","Alchymist","Sort_A_C","Zone 4","Active_On_Website","On_Climber_List","On_Combined_Rose_List","2","0","0","February","0/12/07","0","0/Dec./2007","0","0","0","0","0","0","y"
"3","13","0","Out of Stock","0","Rambler","20+ feet","Lax, trailing","Deep Pink","Bloom Size 3","Fully Double","Rebloom 0","Fragrance ff","Barbier","1909","Disease Resistant","Shade Tolerant","No Entry","No","No","No","No","No","No","No","No","No","Standard","Horizontal Image","1900 to 1909","Vertical Image of Bush","Alexander_Girault","Sort_A_C","Zone 6","Active_On_Website","On_Climber_List","On_Combined_Rose_List","0","0","0","February","0/12/07","0","0//","0","TXQ - Fake","Page - Fake","0","0","0","0"
"4","27","0","Out of Stock","0","Climbing Hybrid Tea","6-8 feet","Shrub, may be grown as a climber","Medium Pink","Bloom Size 4","Fully Double","Rebloom rrr","Fragrance ffff","Boerner","1949","Disease Resistant","No Entry","No Entry","No","No","No","dl_Yes","c_Yes","No","No","he_Yes","No","Standard","Horizontal Image","1940 to 1949","Horizontal Image of Bush","Aloha","Sort_A_C","Zone 5","Active_On_Website","On_Climber_List","On_Combined_Rose_List","2","0","0","February","0/0509/","0","0//","0","0","0","0","0","0","0"
"5","22","0","Out of Stock","6","Rambler","10-15 feet","Climbing","Mauve and Mauve Blend","Bloom Size 3","Semi Double","Rebloom rr","Fragrance fff","Laffay","1829","Disease Resistant","No Entry","Thornless","No","No","fc_Yes","No","No","No","No","he_Yes","No","Standard","No Image","1825 to 1849","No Image of Bush","Amadis","Sort_A_C","Zone 5","Active_On_Website","On_Climber_List","On_Combined_Rose_List","0","1","0","February","0/12/07","0","0/Dec./2007","0","0","0","0","0","0","0"
"6","18","5","Out of Stock","0","Hybrid Tea","5 feet","Upright","Yellow Blend","Bloom Size 6","Fully Double","Rebloom rrr","Fragrance ff","Lammerts","1965","No Entry","No Entry","No Entry","No","No","No","No","No","No","No","No","No","Standard","Horizontal Image","1960 to 1969","Horizontal Image of Bush","American_Heritage","Sort_A_C","Zone 6","Active_On_Website","No Entry","On_Combined_Rose_List","1","0","0","No Entry","0/05/09","0","0//","0","0","0","0","0","0","0"
"7","61","0","Out of Stock","0","Gallica","2.5 feet","Upright","Deep Pink","Bloom Size 2","Semi Double","Rebloom 0","Fragrance fff","species","wild","Disease Resistant","Shade Tolerant","No Entry","No","No","No","dl_Yes","c_Yes","No","No","No","p_Yes","Standard","Horizontal Image","Ancient and Medieval","Horizontal Image of Bush","Apothecary_Rose","Sort_A_C","Zone 3","Active_On_Website","No Entry","On_Combined_Rose_List","3","4","0","February","0/05/09","0","0/Dec./2007","0","0","0","0","0","0","y"
"8","49","0","Out of Stock","0","Floribunda","4 feet","Upright","Apricot and Apricot Blend","Bloom Size 4","Fully Double","Rebloom rrr","Fragrance ffff","Boerner","1965","Very Disease Resistant","No Entry","No Entry","No","No","No","No","c_Yes","No","No","No","No","Standard","Horizontal Image","1960 to 1969","No Image of Bush","Apricot_Nectar","Sort_A_C","Zone 6","Active_On_Website","No Entry","On_Combined_Rose_List","1","0","0","February","0/05/09","0","0","0","0","0","0","0","0","y"

我需要从 image_type、image_prefix、image_of_bush 中提取数据，应用一些规则（如下）并创建完整图像文件路径的输出以附加到以上结果集。

CASE ONE
One Horizontal Image
image_type = "Horizontal Image"
image_of_bush = "No Image of Bush"
IMAGE NAME:  image_prefix + _s + .jpg  (Example: Albertine_s.jpg)

CASE TWO
One Vertical Image
image_type  = "Vertical Image"
image_of_bush  = "No Image of Bush"
IMAGE NAME:  image_prefix + _v + .jpg  (Example: Albertine_v.jpg)

CASE THREE
Two Horizontal Images
image_type  = "Horizontal Image"
image_of_bush  = "Horizontal Image of Bush"
FIRST IMAGE NAME:  image_prefix + _s + .jpg  (Example: Albertine_s.jpg)
SECOND IMAGE NAME:  image_prefix + _bs + .jpg  (Example: Albertine_bs.jpg)

CASE FOUR
Two Vertical Images
image_type  = "Vertical Image"
image_of_bush  = "Vertical Image of Bush"
FIRST IMAGE NAME:  image_prefix + _v + .jpg  (Example: Albertine_v.jpg)
SECOND IMAGE NAME:  image_prefix + _bv + .jpg  (Example: Albertine_bv.jpg)

CASE FOUR
One Horizontal and One Vertical Image
image_type = "Horizontal Image"
image_of_bush  = "Vertical Image of Bush"
FIRST IMAGE NAME:  image_prefix + _s + .jpg  (Example: Albertine_s.jpg)
SECOND IMAGE NAME:  image_prefix + _bv + .jpg  (Example: Albertine_bv.jpg)

CASE FIVE
One Vertical and One Horizontal Image
image_type  = "Vertical Image"
image_of_bush  = "Horizontal Image of Bush"
FIRST IMAGE NAME:  image_prefix + _v + .jpg  (Example: Albertine_v.jpg)
SECOND IMAGE NAME:  image_prefix + _bs + .jpg  (Example: Albertine_bs.jpg)

上述结果集的附加输出看起来像

horizontal_img_1, horizontal_img_2, vertical_img_1, vertical_img_2,
"Albertine_s.jpg","Albertine_bs.jpg", "Albertine_v.jpg", "Albertine_bv.jpg"

当前汇编的 PHP：

<?php
// NOTES:
// The below code chunk (lines 12-41) grab the values for the three main image variables.
// This chunk would be used in all 6 cases
// I HAVE COMMENTS AT THE START OF EACH SIGNIFICANT CODE CHUNK BELOW...
?>



<?php
// Set dbid
$dbid_x = $product_id;

$image_flag_id = 34;
$bush_image_flag_id = 36;
$image_prefix_id = 37;


// IMAGE_FLAG
$image_flag_query = tep_db_query("SELECT products_extra_fields_value from " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id = " . (int)$dbid_x . " AND  products_extra_fields_id = " . $image_flag_id);
$image_flag_info = tep_db_fetch_array($image_flag_query);
$image_flag = $image_flag_info['products_extra_fields_value'];

// BUSH_IMAGE_FLAG
$bush_image_flag_query = tep_db_query("SELECT products_extra_fields_value from " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id = " . (int)$dbid_x . " AND  products_extra_fields_id = " . $bush_image_flag_id);
$bush_image_flag_info = tep_db_fetch_array($bush_image_flag_query);
$bush_image_flag = $bush_image_flag_info['products_extra_fields_value'];

// IMAGE_PREFIX
$image_prefix_query = tep_db_query("SELECT products_extra_fields_value from " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id = " . (int)$dbid_x . " AND  products_extra_fields_id = " . $image_prefix_id);
$image_prefix_info = tep_db_fetch_array($image_prefix_query);
$image_prefix = $image_prefix_info['products_extra_fields_value'];

echo "
<BR><BR>
image_flag = $image_flag<BR>
bush_image_flag = $bush_image_flag<BR>
image_prefix = $image_prefix<BR>
<BR><BR>";
?>





<!-- ROSE PICTURE -->

<?php
// NOTES:
// Lines 61-89 set the popup code for pulling in the large format images.  "bigrose" and "rosebush" are in horizontal format and basically identical - "bigrose_vertical" and "rosebush_vertical" are in vertical format and are basically identical.
// This chunk covers all 6 cases
?>


<TD vAlign=top>
<IMG height=30 src="/oredson/rvr/webimages/shim.gif" width=20 alt="shim"><BR>

<?php
echo "
<script>
function bigrose(){
window.open(\"http://www.roguevalleyroses.com/rvr_bigrose.php?model=$image_prefix\",\"\",\"width=825,height=625,status=1,scrollbars=no\")
}
</script> 

<script>
function bigrose_vertical(){
window.open(\"http://www.roguevalleyroses.com/rvr_bigrose_vertical.php?model=$image_prefix\",\"\",\"width=625,height=825,status=1,scrollbars=no\")
}
</script> 

<script>
function rosebush(){
window.open(\"http://www.roguevalleyroses.com/rvr_rosebush.php?model=$image_prefix\",\"\",\"width=825,height=625,status=1\")
}
</script>

<script>
function rosebush_vertical(){
window.open(\"http://www.roguevalleyroses.com/rvr_rosebush_vertical.php?model=$image_prefix\",\"\",\"width=625,height=825,status=1\")
}
</script> 

<script>
function contactwindow(){
window.open(\"http://www.roguevalleyroses.com/small_contact.html\",\"\",\"width=300,height=100,status=1,left=650\")
}
</script> 

";


// CUT LINE





echo "
<!-- ROSE PICTURE -->
<td valign=\"top\"><img src=\"/oredson/rvr/webimages/shim.gif\" width=\"20\" height=\"30\"><BR>
<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
// echo "<BR>image_flag = $image_flag<BR>";



// NOTE:
// The below code chunk (lines 113-129) check the "image_flag" variable and then generates the Horizontal larger picture link and the Horizontal rose picture itself
// This would be for case 1,3 and 4


// HORIZONTAL IMAGE
if ($image_flag == "Horizontal Image") {
// BIG PICTURE LINK
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:bigrose('');\">Larger Picture</a>";

// Looking for Pictures of the Rose Bush
if ($bush_image_flag == "Horizontal Image of Bush") {
echo "&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:rosebush('');\">Larger Picture of Bush</a><br>";
} elseif ($bush_image_flag == "Vertical Image of Bush") {
echo "&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:rosebush_vertical('');\">Larger Picture of Bush</a><br>";
}

// BIG PICTURE
echo "<img src=\"http://www90.pair.com/oredson/rvr/webimages/roses/small/{$image_prefix}_s.jpg\" align =\"Left\" width=\"265\" height=\"199\" vspace=\"10\" hspace=\"20\" Border=\"0\"></a>
<!-- COMMENTS -->
";
}


// NOTE TO ZACARY:
// The below code chunk checks the "image_flag" variable and then generates the Vertical larger picture link and the Vertical rose picture itself
// This would be for case 2,4 and 6


// VERTICAL IMAGE
if ($image_flag == "Vertical Image") {
// BIG PICTURE LINK
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:bigrose_vertical('');\">Larger Picture</a>";
// BIG PICTURE
echo "<a href=\"javascript:bigrose_vertical('');\"><img src=\"/oredson/rvr/webimages/roses/small/{$image_prefix}_v.jpg\" align =\"Left\" width=\"199\" height=\"265\" vspace=\"10\" hspace=\"20\" Border=\"0\"></a>
<!-- COMMENTS -->
";
            }

// CHECK FOR BIG HORIZONTAL BUSH IMAGE
            $bush = $img_path.$code."_bs.jpg";
            if (file_exists($bush))
            {echo "&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:rosebush('');\">Larger Picture of Bush</a><br>";}


// CHECK FOR BIG VERTICAL BUSH IMAGE
            $vertical_bush = $img_path.$code."_bv.jpg";
            if (file_exists($vertical_bush))
            {echo "&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"javascript:rosebush_vertical('');\">Larger Picture of Bush</a><br>";}


echo "
<BR><b>$product_name</b><BR><BR>
$product_description
";
// CUT LINE
?>
<br>
</td><tr></table>

Answer 1

第一次通关时你还没有拥有所需的一切吗？如果是这样，为什么要提取它并在 php 中进行第二遍？

case when image_type = "Horizontal Image" then concat(image_prefix, "_s.jpg") else null end as horiz_image_1,
case when image_type = "Horizontal Image" and image_of_bush = "Horizontal Image of Bush" then concat(image_prefix, "_bs.jpg") else null end as horiz_image_2,
case when image_type = "Vertical Image" then concat(image_prefix, "_v.jpg") else null end as vert_image_1,
case when image_type = "Vertical Image" and image_of_bush = "Vertical Image of Bush" then concat(image_prefix, "_bv.jpg") else null end as vert_image_2

如果您在原始查询中执行此操作，则“image_type”等名称将不起作用。您需要使用生成列的原始逻辑或将原始查询包装在外部查询中。

另请注意，您的 MAX 逻辑会将“0”放入 image_type 中，这可能不是您真正想要的。尝试使用 CASE 语句而不是 IF/MAX。