110,529
社区成员
发帖
与我相关
我的任务
分享怎么查询不定项条件
就是有多个查询条件(a，b，c，d....)，用户在查询的时候，有可能只查其中的A项，或B,C项，又可能是全部项，即不定项。请问怎么实现?希望能详细点，给个例子参考参考！谢谢了
...全文
请发表友善的回复…
发表回复
闲着吃菜 2010-12-22
- 打赏
- 举报
[Quote=引用 4 楼 q107770540 的回复:]
http://blog.csdn.net/q107770540/archive/2010/07/09/5724013.aspx
[/Quote]
看过之后泪流满面涨见识了，没想到我写那么长的代码人家简单点的就实现了
要回家去吃老米了。。。
http://blog.csdn.net/q107770540/archive/2010/07/09/5724013.aspx
[/Quote]
看过之后泪流满面涨见识了，没想到我写那么长的代码人家简单点的就实现了
要回家去吃老米了。。。闲着吃菜 2010-12-22
- 打赏
- 举报
接上面的语句
你要是不把分给我 不结贴我跟你拼命
///执行的sql拼接
public DataTable Query()
{
string strCommand ="";
string strByGoods="";
string strByProduction = "";
string strGuiGe = "";
string strProductionType = "";
string strBOM = "";
switch (_BillType)
{ if (ProductionName != null)
{
if (ProductionName[0] == "模糊匹配")
{
strByProduction = "and HuoPinID in (select ID from tbHuoPin where HuoPinMingCheng like '%"+_ProductionName [1]+"%')";
}
else
{
strByProduction = "and HuoPinID in (select ID from tbHuoPin where HuoPinMingCheng = '" + _ProductionName[1] + "')";
}
}
if (ProductionType != null)
{
if ( ProductionType[0] == "模糊匹配")
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%')";
}
else
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "')";
}
}
if (BOM != null)
{
if (BOM[0]=="模糊匹配")
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao like '%"+_BOM[1] +"%')";
}
else
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao = '" + _BOM[1] + "')";
}
}
break ;
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe like '%" +_ProductionType[1] + "%')";
}
else
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "')";
}
}
if (BOM != null)
{
if (BOM[0] == "模糊匹配")
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao like '%" + _BOM[1] + "%')";
}
else
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao ='" + _BOM[1] + "')";
}
}
break ;
case "退料单":
{
strCommand = "select * from tbTuiLiaoDan where ID!=-1";
if (GoodsType != null)
{
if (GoodsType[0] == "模糊匹配")
{
strGuiGe = "and ID in (select FuDanID from tbTuiLiaoDetail where HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _GoodsType[1] + "%'))";
}
else if (GoodsType[0] == "精确匹配")
{
strGuiGe = "and ID in (select FuDanID from tbTuiLiaoDetail where HuoPinID in (select ID from tbHuoPin where GuiGe ='" +_GoodsType[1] + "'))";
}
}
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = " and ID in( select FuDanID from tbTuiLiaoDetail where ChanPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%'))";
}
else
{
strProductionType = "and ID in (select FuDanID from tbTuiLiaoDetail where ChanPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "'))";
}
}
} break;
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = " and ID in( select FuDanID from tbShengChanJiHuaDetail where HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%'))";
}
else
{
strProductionType = "and ID in (select FuDanID from tbShengChanJiHuaDetail where HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "'))";
}
}
if (BOM != null)
{
if (BOM[0] == "模糊匹配")
{
//strBOM = "and BOM in (select ID from tbShengChanJiHuaDetail where BanBenHao like '%" + _BOM[1] + "%')";
strBOM = "and ID in (select FuDanID from tbShengChanJiHuaDetail where BOM in ( select ID from tbWuLiaoQingDan where BanBenHao like '%" + _BOM[1] + "%'))";
}
else
{
//strBOM = "and BOM in (select ID from tbShengChanJiHuaDetail where BanBenHao = " + _BOM[1] + "";
strBOM = "and ID in (select FuDanID from tbShengChanJiHuaDetail where BOM in ( select ID from tbWuLiaoQingDan where BanBenHao= '"+_BOM[1] + "'))";
}
}
} break;
}
strCommand += strByGoods + strByProduction+strBOM+strGuiGe+strProductionType ;
if (_BillCode != null)
{
if (_BillCode[0] == "模糊匹配")
{
strCommand += "and DanJuBianHao like '%"+_BillCode[1] +"%'";
}
else
{
strCommand += "and DanJuBianHao ='"+_BillCode[1] +"'";
}
}
if (_BillTime != null)
{
if (_BillTime[0] != null)
{
strCommand += " and KaiDanRiQi>='"+_BillTime [0]+"' ";
}
if (_BillTime[1] != null)
{
strCommand += "and KaiDanRiQi<='"+_BillTime [1]+"'";
}
}
DataTable dt = new DataTable();
UserObject.Conn.ExecuteDataTable(strCommand ,ref dt );
return dt;
}
你要是不把分给我 不结贴我跟你拼命
zhao_zps 2010-12-22
- 打赏
- 举报
拼接查询字符串
闲着吃菜 2010-12-22
- 打赏
- 举报
上面代码有点小出入重新附上：
///数据显示和条件输入界面
private void btn_Definite_Click(object sender, EventArgs e)
{
QueryProducingOdersObject QPOObj = new QueryProducingOdersObject();
QPOObj ._BillType = BillType ;
ShowLastCondition(lbCondition .Text );
ShowLastCondition(lbCondition .Text );
foreach (ListViewItem lvi in lvCondition.Items)
{
switch (lvi.Text)
{
case "单据编号":
{
if (cbbBillCode .Text != "")
{
QPOObj.BillCode = new string[2];
QPOObj.BillCode[0] = cbbBillCode.Text;
QPOObj.BillCode[1] = txtbBillCode.Text;
}
} break;
case "开单日期":
{
QPOObj.BillTime = new DateTime?[2];
QPOObj.BillTime[0] = null;
QPOObj.BillTime[1] = null;
if (chkbStartTime .Checked ==true )
{
QPOObj.BillTime[0] = Convert .ToDateTime (dtpStartTime.Value.Date .ToString ());
}
if (chkbOver.Checked == true)
{
QPOObj.BillTime[1] = Convert.ToDateTime(dtpOverTime.Value.Date.ToString("yyyy-MM-dd") + " 23:59:59");
}
} break;
case "制单人":
{
if (cbbCreateMan.Text != "")
{
QPOObj.CreateMan = Convert .ToInt64 (cbbCreateMan .SelectedValue );
}
else
{
QPOObj.CreateMan = null;
}
} break;
case "单据状态":
{
if (cbbBillState.Text != "")
{
QPOObj.BillState = cbbBillState.SelectedIndex ;
}
else
{
QPOObj.BillState = null;
}
} break;
case "货品名称":
{
if (cbbGoods.Text != "")
{
QPOObj.GoodsName = new string[2];
QPOObj.GoodsName[0] = cbbGoods.Text;
QPOObj.GoodsName[1] = txtbGoods.Text;
}
else
{
QPOObj.GoodsName = null;
}
} break;
case "货品规格":
{
if (cbx_GoodsType.Text != "")
{
QPOObj.GoodsType = new string[2];
QPOObj.GoodsType[0]=cbx_GoodsType.Text;
QPOObj.GoodsType[1]=txbGodsType.Text;
}
else
{
QPOObj.GoodsType=null;
}
}break;
case "产品名称":
{
if (cbbProduction . Text != "")
{
QPOObj.ProductionName = new string[2];
QPOObj.ProductionName [0] = cbbProduction .Text ;
QPOObj.ProductionName [1] = txtbProduction .Text ;
}
else
{
QPOObj.ProductionName = null;
}
} break;
case "产品规格":
{
if (cbxProductionType.Text != "")
{
QPOObj.ProductionType = new string[2];
QPOObj.ProductionType[0] = cbxProductionType.Text;
QPOObj.ProductionType[1] = tbxProductionType.Text;
}
else
{
QPOObj.ProductionType = null;
}
} break;
case "BOM版本号":
{
if (cbbBOM.Text != "")
{
QPOObj .BOM =new string [2];
QPOObj.BOM[0]=cbbBOM .Text ;
QPOObj.BOM[1]=txtbBOM .Text ;
}
else
{
QPOObj.BOM = null;
}
} break;
}
}
DataTable dt = QPOObj . Query();
// SpreadMethods.ClearSpreadContent(spreadResult);
SpreadMethods.ClearSpreadText(spreadResult );
if (spreadResult.MaxRows <= dt.Rows.Count)
{
spreadResult.MaxRows = dt.Rows.Count + 1;
}
DrawspreadResults(dt);
}
///各种属性的定义
private DateTime?[] _BillTime;
public DateTime?[] BillTime
{
get { return _BillTime ; }
set { _BillTime = value; }
}
private long? _CreateMan;
public long ? CreateMan
{
get {return _CreateMan; }
set { _CreateMan = value; }
}
private int ? _BillState;
public int ? BillState
{
get { return _BillState ; }
set { _BillState = value; }
}
private string[] _GoodsName;
public string[] GoodsName
{
get { return _GoodsName; }
set { _GoodsName = value; }
}
//货品规格
private string[] _GoodsType;
public string[] GoodsType
{
get { return _GoodsType; }
set { _GoodsType = value; }
}
guyoujing 2010-12-22
- 打赏
- 举报
拼吧。。。很简单就是比较烦了一点
闲着吃菜 2010-12-22
- 打赏
- 举报
楼主本人刚好做过你这个类似的问题现奉上我自己的解决方案：
我的查询语句是在新建的一个类里面的新建类A里面我有一个publicA 方法执行select后语句返回一个dt这个dt就是你要的，我在类A里面 定义了几个可以为null的string类型的属性，然后在页面里面选择了该条件就对对应的属性附上值，没有赋值的就是null，在publicA方法里面进行拼接字符串的sql语句，你开始写一个
string strsql="select * from youtable where 1=1" 然后 根据z值是否为null就行sql语句的拼接 如：
if(属性A！=null)
{
strsql=strsql+" and 字段A=+属性A ；
}
按这样的方法拼接字符串最好肯定是等到你想要的结果，如果你有条件是在某个时间段内的话你可以定义private string[] ? 类型的属性然后拼接字符串： 先奉上我的实例代码：
我的查询语句是在新建的一个类里面的新建类A里面我有一个publicA 方法执行select后语句返回一个dt这个dt就是你要的，我在类A里面 定义了几个可以为null的string类型的属性，然后在页面里面选择了该条件就对对应的属性附上值，没有赋值的就是null，在publicA方法里面进行拼接字符串的sql语句，你开始写一个
string strsql="select * from youtable where 1=1" 然后 根据z值是否为null就行sql语句的拼接 如：
if(属性A！=null)
{
strsql=strsql+" and 字段A=+属性A ；
}
按这样的方法拼接字符串最好肯定是等到你想要的结果，如果你有条件是在某个时间段内的话你可以定义private string[] ? 类型的属性然后拼接字符串： 先奉上我的实例代码：
//页面代码
private void btn_Definite_Click(object sender, EventArgs e)
{
if (lstConditionItem.SelectedItems.Count > 0)
{
ShowCondition(lstConditionItem.SelectedItems[0].Text);
}
objQueryBase = new QueryBaseObject();
foreach ( ListViewItem lvi in lsvCondition.Items )
{
switch (lvi.Text)
{
case "业务类型":
objQueryBase.StrBusinessType = strBusinessType;
_strBillType = strBusinessType[0];
break;
case "单据编号":
if (cbx_BillNo.Text != "任意" && cbx_BillNo.Text!="")
{
objQueryBase.BillNoCondition = new string[2];
objQueryBase.BillNoCondition[1] = txb_BillNo.Text;
objQueryBase.BillNoCondition[0] = cbx_BillNo.Text;
}
break;
case "开单日期":
if (ckb_CreatTimeBegin.Checked || ckb_CreatTimeEnd.Checked)
{
objQueryBase.CreatDatetime = new string[2];
if (ckb_CreatTimeBegin.Checked)
{
objQueryBase.CreatDatetime[0] = dtp_CreatBillBegin.Value.ToShortDateString();
}
if (ckb_CreatTimeEnd.Checked)
{
objQueryBase.CreatDatetime[1] = dtp_CreatBillEnd.Value.ToShortDateString();
}
}
break;
case "单据金额":
{
objQueryBase.BillCurrency = new string[3];
objQueryBase.BillCurrency[0] = cbx_BillCurrency.Text;
if (txb_BillCurrencyBegin.Text != "")
{
objQueryBase.BillCurrency[1] = txb_BillCurrencyBegin.Text;
}
else
{
objQueryBase.BillCurrency[1] = "0";
}
if (txb_CurrencyEnd.Text != "")
{
objQueryBase.BillCurrency[2] = txb_CurrencyEnd.Text;
}
else
{
objQueryBase.BillCurrency[2] = "0";
}
}
break;
case "客户":
{
objQueryBase.CustomerID = Convert .ToInt64 ( cbx_Customer.SelectedValue);
} break;
case "客户类型":
{
objQueryBase.CustomerTypeID = Convert.ToInt64(cbx_CustomerType.SelectedValue);
}
break;
case "客户编号":
{
if (cbx_CustomerCode.Text != "任意" && txb_CustomerCode.Text!="")
{
objQueryBase.CustomerCode = new string[2];
objQueryBase.CustomerCode [0] = cbx_CustomerCode.Text;
objQueryBase.CustomerCode [1] = txb_CustomerCode.Text;
}
}break ;
case "货品":
{
if (cbx_Goods.Text != "")
{
objQueryBase.GoodsID = GoodsID;
}
} break;
case "产品":
{
if (cbx_ChanPin.Text != "")
{
objQueryBase.ChanPinID = ChanPinID;
}
} break;
case "货品编码":
{
if (cbx_GoodsCodeCondition.Text != "任意" && txb_GoodsCode.Text!="")
{
objQueryBase.GoodsCode = new string[2];
objQueryBase.GoodsCode[0] = cbx_GoodsCodeCondition.Text;
objQueryBase.GoodsCode[1] = txb_GoodsCode .Text;
}
} break;
case "货品名称":
{
if (cbx_GoodsName.Text != "任意" && txb_GoodsName.Text != "")
{
objQueryBase.GoodsName = new string[2];
objQueryBase.GoodsName[0] = cbx_GoodsName.Text;
objQueryBase.GoodsName[1] = txb_GoodsName.Text;
}
}break ;
case "货品规格":
{
if (cbxGoodsType.Text != "任意" && tbxGoodsType.Text != "")
{
objQueryBase.GoodsType = new string[2];
objQueryBase.GoodsType[0] = cbxGoodsType.Text;
objQueryBase.GoodsType[1] = tbxGoodsType.Text;
}
} break;
case "发票号":
{
if (cbx_InvoiceNum.Text != "任意" && txb_InvoiceNum .Text !="")
{
objQueryBase.InvoiceNum = new string[2];
objQueryBase.InvoiceNum[0] = cbx_InvoiceNum.Text;
objQueryBase.InvoiceNum[1] = txb_InvoiceNum.Text;
}
} break;
case "合同编号":
{
if (cbx_ContractNo.Text != "任意" && txb_ContractNo.Text != "")
{
objQueryBase.ContractNo = new string[2];
objQueryBase.ContractNo[0] = cbx_ContractNo.Text;
objQueryBase.ContractNo[1] = txb_ContractNo.Text;
}
} break;
case "录入用户":
{
if (cbx_Creator.Text != "")
{
objQueryBase.Creator = CreatorID;
}
} break;
case "审核用户":
{
if (cbx_Auditor.Text != "")
{
objQueryBase.Auditor = AuditorID;
}
} break;
case "审核日期":
{
if (ckb_AuditorDateBegin.Checked || ckb_AuditorDateEnd.Checked)
{
objQueryBase.AuditorDate = new string [2];
if (ckb_AuditorDateBegin.Checked)
{
objQueryBase.AuditorDate[0] = dtp_AuditDateBegin.Value.ToShortDateString();
}
if (ckb_AuditorDateEnd.Checked)
{
objQueryBase.AuditorDate[1] = dtp_AuditDateEnd.Value.ToShortDateString();
}
}
} break;
case "单据状态":
{
objQueryBase .BillStatus =cbx_BillStatus .SelectedIndex -1;
} break;
case "仓库":
{
objQueryBase.DepotID = DepotID;
}
break;
case "调出仓库":
{
objQueryBase.OutDepotID = DiaoChuDepot;
} break;
}
}
if (DataLock != "")
{
objQueryBase.DataLock = DataLock;
}
DataTable dt = objQueryBase.Query();
SpreadMethods.ClearSpreadContent(spreadResults);
if (dt != null)
{
if (spreadResults.MaxRows <= dt.Rows.Count)
{
spreadResults.MaxRows = dt.Rows.Count;
}
DrawspreadResults(dt);
}
}
昵称不能为空噢噢噢噢 2010-12-22
- 打赏
- 举报
一种做法
sql语句：
.....where 字段 in s
而
string s=筛选好的条件 （当然得要拼奏例如（'a','b'）等等结构了）
然后代入sql语句
......where 字段 in s
sql语句：
.....where 字段 in s
而
string s=筛选好的条件 （当然得要拼奏例如（'a','b'）等等结构了）
然后代入sql语句
......where 字段 in s
yksyuan 2010-12-22
- 打赏
- 举报
55555 我上次 一堆 if else
真是笨啊。
早知道 搜索一下好了
郁闷
真是笨啊。
早知道 搜索一下好了
郁闷
foxd 2010-12-22
- 打赏
- 举报
where = "1=1"
laowang134 2010-12-22
- 打赏
- 举报
用or要慎重。。。
q107770540 2010-12-22
- 打赏
- 举报
龍过鸡年 2010-12-22
- 打赏
- 举报
[Quote=引用 2 楼 timzhufaith 的回复:]
拼sql
[/Quote]
我遇到类似问题，也是拼where
拼sql
[/Quote]
我遇到类似问题，也是拼where
TimZhuFaith 2010-12-22
- 打赏
- 举报
拼sql
孟子E章 2010-12-22
- 打赏
- 举报
or and 等进行连接
