现在有这样的表编号 编码 价格 价格2
1 C T T
2 B E G
3 H A A
4 Y R Y
5 R Y S
6 D B B
7 E G E
8 T H H
如果我给的条件是俩T那么我查出的结果是第一条,如果我给的是俩 Y 我查出的是第四条我现在写的SQL语句这样,但查不出结果string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\桌面文件\远东超市数据库\Demo远东超市.mdb";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[] { null, null, null, null }); foreach (DataRow row in schemaTable.Rows)
{
//string queryString = "select top 1 1 from [" + row[2] + "] where [" + row[3] + "] like '%00000024%' or [" + row[3] + "] like '%0000000A%'";
string queryString = "select top 1 1 from [" + row[2] + "] where [" + row[3] + "] like " + "'%" + str1 + "%' or [" + row[3] + "] like " + "'%" + str2 + "%'";
using (OleDbCommand command = new OleDbCommand(queryString, connection))
{
if (command.ExecuteScalar() != null)
{
//Console.WriteLine("{0} - {1}", row[2], row[3]);
List<TreeNode> listNodes = new List<TreeNode>();
foreach (TreeNode node in treeView1.Nodes)
{
FindCheckNode(node, listNodes);
}
foreach (TreeNode no in listNodes)
{
if (no.Text == row[3].ToString() | no.Text == row[2].ToString())
{
no.BackColor = Color.Red;
}
}
}
}
}
}
Console.Read();
1 C T T
2 B E G
3 H A A
4 Y R Y
5 R Y S
6 D B B
7 E G E
8 T H H
如果我给的条件是俩T那么我查出的结果是第一条,如果我给的是俩 Y 我查出的是第四条我现在写的SQL语句这样,但查不出结果string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\桌面文件\远东超市数据库\Demo远东超市.mdb";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[] { null, null, null, null }); foreach (DataRow row in schemaTable.Rows)
{
//string queryString = "select top 1 1 from [" + row[2] + "] where [" + row[3] + "] like '%00000024%' or [" + row[3] + "] like '%0000000A%'";
string queryString = "select top 1 1 from [" + row[2] + "] where [" + row[3] + "] like " + "'%" + str1 + "%' or [" + row[3] + "] like " + "'%" + str2 + "%'";
using (OleDbCommand command = new OleDbCommand(queryString, connection))
{
if (command.ExecuteScalar() != null)
{
//Console.WriteLine("{0} - {1}", row[2], row[3]);
List<TreeNode> listNodes = new List<TreeNode>();
foreach (TreeNode node in treeView1.Nodes)
{
FindCheckNode(node, listNodes);
}
foreach (TreeNode no in listNodes)
{
if (no.Text == row[3].ToString() | no.Text == row[2].ToString())
{
no.BackColor = Color.Red;
}
}
}
}
}
}
Console.Read();
--> Author : jinjazzli
--> Target : ---->1000
--> Date : 2009-12-11 15:48:55
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (编号 int,编码 varchar(1),价格 varchar(1),价格2 varchar(1))
insert into @tb
select 1,'C','T','T' union all
select 2,'B','E','G' union all
select 3,'H','A','A' union all
select 4,'Y','R','Y' union all
select 5,'R','Y','S' union all
select 6,'D','B','B' union all
select 7,'E','G','E' union all
select 8,'T','H','H'select 编号, id from
(select 编号,编码 as id from @tb
union all
select 编号,价格 from @tb
union all
select 编号,价格2 from @tb
)t
where id='T'
group by 编号,id
having count(*)=2
编号 id
----------- ----
1 T(1 行受影响)
insert into tb values(1 , 'C' , 'T' , 'T')
insert into tb values(2 , 'B' , 'E' , 'G')
insert into tb values(3 , 'H' , 'A' , 'A')
insert into tb values(4 , 'Y' , 'R' , 'Y')
insert into tb values(5 , 'R' , 'Y' , 'S')
insert into tb values(6 , 'D' , 'B' , 'B')
insert into tb values(7 , 'E' , 'G' , 'E')
insert into tb values(8 , 'T' , 'H' , 'H')
gocreate proc my_proc @str as varchar(10) , @cnt as int
as
begin
select * from tb where
(case when 编码 = @str then 1 else 0 end) +
(case when 价格 = @str then 1 else 0 end) +
(case when 价格2 = @str then 1 else 0 end) >= @cnt
end
goexec my_proc 'T' , 2
/*
编号 编码 价格 价格2
----------- ---------- ---------- ----------
1 C T T(所影响的行数为 1 行)
*/exec my_proc 'Y' , 2
/*
编号 编码 价格 价格2
----------- ---------- ---------- ----------
4 Y R Y(所影响的行数为 1 行)
*/drop table tb
drop proc my_proc
大哥,你写SQL语句的速度真快,高手啊
1 C T T
2 B E G
3 H A A
4 Y R Y
5 R Y S
6 D B B
7 E G E
8 T H H
select * from 表名
where (编码='条件1' and 价格='条件2' )
or (编码='条件1' and 价格2='条件2' )
or (价格='条件1' and 价格2='条件2' )