为分组项目定义一个 id 列表 [英] define a list of id's for grouped item
问题描述
我有一个小问题,我就是不明白,问题是什么,我该如何解决,我有一个coldfusion变量,例如#account_code#
,首先,这段代码看起来像这样: 100.001.001
(当然有一堆),我有这个变量的一些值,比如 sum(nettotal)
和 cfquery i按此 #account_code#
分组,我只想设置这些代码的列表,因此我定义了列表,例如 <cfset code_list='100.001.001,100.001.002'>
并在查询中: account_code in (#code_list#)
我也试过这个 account_code in ('#code_list#')
但它却给出了错误,它说'.001'附近的语法不正确.据我了解,我需要以某种方式替换这些点,并在没有它们的情况下定义代码 id.谢谢大家的帮助!我真的很欣赏它!
i got a little question, i just can't understand, what is the problem and how do i solve it, i have a coldfusion variable, for example #account_code#
, first of all, this code looks like this: 100.001.001
(there are bunch of them of cource) and i have some values for this variable, like sum(nettotal)
and the cfquery i grouped by this #account_code#
, all i want is to set the list of these codes, thus i define list, for example <cfset code_list='100.001.001,100.001.002'>
and in query: account_code in (#code_list#)
i also tried this one account_code in ('#code_list#')
but instead it gives out the error, it says the Incorrect syntax near '.001'. as far as i understand i need to replace somehow these dots, and define the codes id's without them. thank you all for the help! i really appretiate it!
推荐答案
如果我理解你的问题是你如何正确使用 SQL IN 子句.
If i understand your question is really how do you correctly use the SQL IN clause.
SQL IN 子句接受一个值列表,如果这些值是数字,则不需要引用,但您的代码是字符串,因此每个值都需要引用
The SQL IN clause takes a list of values, if those values are numeric they do not need to be quoted, but your codes are strings, so each value needs to be quoted
select * from tbl
where id in ('100.001.001','100.001.002')
在 ColdFusion 中,正确的做法是将 <cfqueryparam> 与 list=true 一起使用
In ColdFusion the correct way to do this is the use <cfqueryparam> with list=true
<cfset code_list='100.001.001,100.001.002'>
<cfquery name="qSomething" ...>
select * from tbl
where id in (
<cfqueryparam list="true"
value="#code_list#"
cfsqltype="cf_sql_varchar" />
)
</cfquery>
这会将您的列表变成查询中的多个参数,如果您的 code_list 实际上是由表单或 url 变量传入的,则有助于保护您免受 SQL 注入攻击.
This turns your list into multiple parameters in your query, and if your code_list is actually being passed in by a form or url variable helps to protect you from SQL injection attacks.
这篇关于为分组项目定义一个 id 列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!