我需要从MySQL表中选择满足用户提交的大小/数量对的所有产品。
例如,我的结果是
S = 2
M = 2
L = 3
XL = 1它是两个分隔符字符串,如下所示:
S,M,L,XL
2,2,3,1我的数据库查询当前在一个Coldfusion8页面中,在该页面中,我只循环从两个字符串创建的2d数组,并在SELECT语句中执行一个循环。
<cfloop from="1" to="#array.length#" index="i">
OR ( size = <cfqueryparam cfsqltype="cfsql_varchar" value="#var.lotArray[i][1]#"> AND qty >= <cfqueryparam cfsqltype="cfsql_varchar" value="#var.lotArray[i][2]#"> )
</cfloop>问题:
在存储过程中的SELECT中有在MySQL中这样做的方法吗?我想我可以循环遍历字符串长度并选择每次迭代一次,但是这将是大量的结果集,我不知道如何将这些结果组合在一起。我也看过regex,但我不知道如何选择两个regex字符串和两个需要关联的字符串(M=2,L=3.)
谢谢你的提示!
发布于 2012-06-20 22:34:39
我不太喜欢MySQL.好像不起作用。我曾希望至少能够像这样使用ELT循环使用我的变量名:
ELT( loopcounter, passed_sizes )从1,2,3.passed_sizes是像'S','M','L','XL‘这样的字符串,但我想你也不能构造那样的元素。
因此,我最终做了过多的代码解决方案:
BEGIN
SELECT art.a, art.v, art.c
FROM arts AS art
WHERE ( (filter = '') AND (1=1)
OR art.d = filter
)
AND ( (size_1 = '') AND (1=1) OR ( art.size = size_1 AND art.qty >= qty_1 ) )
AND ( (size_2 = '') AND (1=1) OR ( art.size = size_2 AND art.qty >= qty_2 ) )
AND ( (size_3 = '') AND (1=1) OR ( art.size = size_3 AND art.qty >= qty_3 ) )
AND ( (size_4 = '') AND (1=1) OR ( art.size = size_4 AND art.qty >= qty_4 ) )
AND ( (size_5 = '') AND (1=1) OR ( art.size = size_5 AND art.qty >= qty_5 ) )
AND ( (size_6 = '') AND (1=1) OR ( art.size = size_6 AND art.qty >= qty_6 ) )
AND ( (size_7 = '') AND (1=1) OR ( art.size = size_7 AND art.qty >= qty_7 ) )
AND ( (size_8 = '') AND (1=1) OR ( art.size = size_8 AND art.qty >= qty_8 ) )
AND ( (size_9 = '') AND (1=1) OR ( art.size = size_9 AND art.qty >= qty_9 ) )
AND ( (size_10 = '') AND (1=1) OR ( art.size = size_10 AND art.qty >= qty_10 ) )
GROUP BY art.a, art.v, art.c
HAVING anzahl = length;
END传入22个参数,而不是两个字符串。可读的,但是孩子,我想念容易使用的循环.如果有人知道一个更容易的实现,请张贴作为答案。谢谢!
https://stackoverflow.com/questions/11127238
复制相似问题