SQL 多条件,任意选择 查询方法

Question

比如说查询条件有A,B,C,D,E,F但每次所使用的条件都有可能不同,例:第一次是B,E,F第二次是A,D,F第三次是C,D,E第四次`~~~~~像这样子,这个查询应怎么写?

Answer 1

实例 public java.util.List<Operator> selectOperatorsConditions(
HashMap<String, String> conditions, Connection conn) {
// 根据条件进行查询操作员信息
java.util.List<Operator> operatorsByCondition = new ArrayList<Operator>(); StringBuffer sql = new StringBuffer(
"select operator_id,operator_name,is_admin from t_operator"); if (conditions.size() > 0) {
sql.append(" where ");
Iterator<String> keyset = conditions.keySet().iterator();
while (keyset.hasNext()) {
String cols = (String) keyset.next();
sql.append(cols + " = '" + conditions.get(cols) + "' and ");
}
sql.delete(sql.lastIndexOf("and"), sql.length());
}
Statement st = null;
ResultSet rs = null;
try {
if (conn != null) {
st = conn.createStatement();
rs = st.executeQuery(sql.toString()); while (rs.next()) {
Operator operator = new Operator(rs.getString(1), rs
.getString(2), rs.getString(3));
operatorsByCondition.add(operator);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return operatorsByCondition;
}

Answer 2

sql="select * from test where 1=1"sqllike=""if a<>"" then
sqllike=sqllike & "a的条件"end if if b<>"" thensqllike=sqllike & "b的条件"end if同理写其他几个最后sql=sql & sqllike

Answer 3

我这个弄成了个存储过程，如果你不需要，你可以吧中间的查询语句提出来 /******************************************************************
--
--action:根据查询条件查询符合条件的员工信息
--author:默
--create-date:2009-3-12
--parameter:@name 员工名称
--parameter:@did 部门编号
--parameter:@work 职位
--parameter:@rid 角色编号
--
*************************************************************************/
CREATE PROCEDURE employees_select_SQL_proc
@name nvarchar(20),
@did nvarchar(3),
@work nvarchar(10),
@rid nvarchar(3)
AS
DECLARE @sql nvarchar(1000)
SET @sql='SELECT * FROM employees WHERE 1=1'
IF(@name IS NOT NULL)
BEGIN
IF( len(@name)>0)
BEGIN
SET @sql=@sql+' AND E_name LIKE ''%'+@name+'%'''
END
END
IF(@did IS NOT NULL)
BEGIN
IF (@did>0)
BEGIN
SET @sql=@sql+' AND E_Did ='+@did
END
END
IF(@work IS NOT NULL)
BEGIN
IF (len(@work)>0)
BEGIN
SET @sql=@sql+' AND E_work LIKE ''%'+@work+'%'''
END
END
IF(@rid IS NOT NULL)
BEGIN
IF(@rid >0)
BEGIN
SET @sql=@sql+' AND E_Rid ='+@rid
END
END
SET @sql=@sql+'ORDER BY E_datetime DESC'
EXEC(@sql) GO