SQL - 创建查询以返回包含两个字符串的值

本文介绍了SQL - 创建查询以返回包含两个字符串的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好。我有一个数据库供应商，如下所示。我有一个文本框，其中包含逗号分隔的字符串，如SMPS，MotherBoard。我必须选择包含SMPS和主板的供应商名称。我已经为它编写了查询。但我的代码无法正常工作。我的问题是如何选择包含SMPS和主板的供应商名称。 ID Vendor_Name Asset_Type 37 Futuresoft主板 37 Futuresoft SMPS 38 Future India HDD 38 Future India操纵杆 38 Future India笔记本电脑屏幕 39 Tech_M Baterry 39 Tech_M笔记本电脑屏幕 39 Tech_M鼠标 46 dgd电池 46 dgd RAM 46 dgd操纵杆 46 dgd鼠标 46 dgd处理器 47 Religare HDD 48 ryy电池 48 ryy HDD 48 ryy joystick 我的代码如下 public void dddl（） { DataTable objDt = new DataTable（）; objDt.Columns.Add（ Vendor_Name）; string serial = TextBox8.Text; 会话[ St] = TextBox8.Text; string [] s = serial.Split（' ）; for （ int i = 0 ; i < s.Length; i ++） { { string s1 = s [i] .ToString（）; // string qry =SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type like'％+ s [ i] .ToString（）+％'; // string qry = SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type ='+ s [i] .ToString（）+'; // string qry =SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type ='+ s [i] .ToString（）+'AND Asset_Type ='+ s [i] .ToString （）+'; string qry = SELECT * from Vendor WHERE Asset_Type =' + s [ 0 ]。ToString（）+ 'AND Vendor_Name in（SELECT Vendor_Name from Vendor WHERE Asset_Type like'％ + s [i]。 ToString（）+ ％'）; SqlDataAdapter adpt = new SqlDataAdapter（qry，con）; DataTable dt = new DataTable（）; adpt.Fill（dt）; objDt.Merge（dt）; } objDt = objDt.DefaultView.ToTable（ true ， Vendor_Name）; DropDownList4.DataTextField = Vendor_Name; DropDownList4.DataValueField = Vendor_Name; DropDownList4.DataSource = objDt; DropDownList4.DataBind（）; con.Close（）; } } 如何操作请帮助解决方案 这样的东西可能会给你你需要的东西，但我认为最有效的是你需要对数据进行标准化，将供应商与资产类型分开然后有一个连接表。 下面的代码不准确，所以你可能需要正确调整它，但你应该明白。 public void dddl（） { DataTable objDt = new DataTable（）; objDt.Columns.Add（Vendor_Name）; string serial = TextBox8.Text; Session [St] = TextBox8.Text; string [] s = serial.Split（'，'）; var sbuilder = new StringBuilder（）; sbuilder.AppendLine（从Vendor中选择Vendor_Name）; for（int i = 0; i< s.Length; i ++） { if（i == 0） sbuilder.Append（Where）; sbuilder.Append（string.Format（Vendor_Name in（从Vendor中选择Vendor_Name，其中Asset_Type类似于@a {0}），i））; if（i< s.Length-1） sbuilder.Append（AND）; } SqlDataAdapter adpt = new SqlDataAdapter（sbuilder.ToString（），con）; for（int i = 0; i< s.Length; i ++） { adpt.SelectCommand.Paramters.AddWithValue（string.format（@ a {0}，i） ，s [i]）; } DataTable dt = new DataTable（）; adpt.Fill（objDt）; objDt = objDt.DefaultView.ToTable（true，Vendor_Name）; DropDownList4.DataTextField =Vendor_Name; DropDownList4.DataValueField =Vendor_Name; DropDownList4.DataSource = objDt; DropDownList4.DataBind（）; con.Close（）; } Hi Everyone. I have a database Vendor as follows. I have a text box that contains comma separated string like SMPS,MotherBoard,. I have to select the vendor name that contains both SMPS and Motherboard. I have written the query for it. but my code is not working correctly. My issue how to select Vendor name that contains both SMPS and Motherboard.ID Vendor_Name Asset_Type37 Futuresoft Motherboard37 Futuresoft SMPS38 Future India HDD38 Future India joystick38 Future India Laptop Screen39 Tech_M Baterry39 Tech_M Laptop Screen39 Tech_M Mouse46 dgd Battery46 dgd RAM46 dgd joystick46 dgd Mouse46 dgd Processor47 Religare HDD48 ryy Battery48 ryy HDD48 ryy joystick My code is as follows public void dddl() { DataTable objDt = new DataTable(); objDt.Columns.Add("Vendor_Name"); string serial = TextBox8.Text; Session["St"] = TextBox8.Text; string[] s = serial.Split(','); for (int i = 0; i < s.Length; i++) { { string s1 = s[i].ToString(); //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type like '%" + s[i].ToString() + "%'"; //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type = '" + s[i].ToString() + "'"; //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type = '" + s[i].ToString() + "' AND Asset_Type= '" + s[i].ToString() + "'"; string qry = "SELECT * from Vendor WHERE Asset_Type = '" + s[0].ToString() + "' AND Vendor_Name in (SELECT Vendor_Name from Vendor WHERE Asset_Type like '%" + s[i].ToString() + "%')"; SqlDataAdapter adpt = new SqlDataAdapter(qry, con); DataTable dt = new DataTable(); adpt.Fill(dt); objDt.Merge(dt); } objDt = objDt.DefaultView.ToTable(true, "Vendor_Name"); DropDownList4.DataTextField = "Vendor_Name"; DropDownList4.DataValueField = "Vendor_Name"; DropDownList4.DataSource = objDt; DropDownList4.DataBind(); con.Close(); } }How to do it Please help 解决方案 Something like this might give you what you need, but I think to be most effective you need to normalise your data a bit, separate the vendors from the asset types then have a join table.The code below isn't exact so you might need to tweak it properly but you should get the idea.public void dddl() { DataTable objDt = new DataTable(); objDt.Columns.Add("Vendor_Name"); string serial = TextBox8.Text; Session["St"] = TextBox8.Text; string[] s = serial.Split(','); var sbuilder = new StringBuilder(); sbuilder.AppendLine("Select Vendor_Name from Vendor"); for (int i = 0; i < s.Length; i++) { if(i ==0) sbuilder.Append(" Where "); sbuilder.Append(string.Format("Vendor_Name in (Select Vendor_Name from Vendor where Asset_Type like @a{0})", i)); if(i < s.Length-1) sbuilder.Append( " AND "); } SqlDataAdapter adpt = new SqlDataAdapter(sbuilder.ToString(), con); for(int i=0; i< s.Length; i++){ adpt.SelectCommand.Paramters.AddWithValue(string.format("@a{0}", i), s[i]);} DataTable dt = new DataTable(); adpt.Fill(objDt); objDt = objDt.DefaultView.ToTable(true, "Vendor_Name"); DropDownList4.DataTextField = "Vendor_Name"; DropDownList4.DataValueField = "Vendor_Name"; DropDownList4.DataSource = objDt; DropDownList4.DataBind(); con.Close(); } 这篇关于SQL - 创建查询以返回包含两个字符串的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！