从多个表中选择语句，输入变量

本文关键字：语句 输入 变量 选择 | 更新日期: 2023-09-27 17:49:30

我有两个表:AreaCode和EquipmentNumber。

+------------------------------------+
| AreaCd                             |
|------------------------------------|
| AreaID INT NOT NULL AUTO_INCREMENT |
| Code INT                           |
| Name CHAR(30)                      |
| Comments TEXT                      |
| PKEY (AreaID)                      |
+------------------------------------+
+------------------------------------+
| EqNum                              |
|------------------------------------|
| EqID INT NOT NULL AUTO_INCREMENT   |
| AreaID INT                         |
| Number CHAR(5)                     | 
| Type CHAR(10)                      |
| PKEY (EqID)                        |
| FKEY (AreaID) REF AreaCode(AreaID) |
+------------------------------------+

我想提取EqNum.Number, Eq.Type和AreaCd.Code。问题是，查询是由来自表单的输入填充的，因此搜索限制是可变的。我已经创建了3个类似的独立查询:

"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Code = " + int nCode + ";";
"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Number = '" + string sNumber + "';";
"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Type = '" + string sType + "';";

如果用户一次只搜索一列(Code、Number、或 Type)，那么它们自己都可以很好地工作，但是我需要同时搜索一列、两列、或三列的能力。

我尝试过使用OR, LIKE，多重选择，我甚至尝试过将int nCode转换为使用%通配符的字符，但我就是找不到有效的东西。

问题:有人能帮我把这三个查询连接起来，搜索表中所有三个字段的任何组合:EqNum.Number, EqNum.Type和AreaCd.Code，当添加更多字段时，将优化搜索结果?(即搜索EqNum.Type将产生比搜索EqNum.Number, EqNum.Type和AreaCd.Code更多的结果)

从多个表中选择语句，输入变量

SELECT e.Number, e.Type, a.Code
FROM EqNum e INNER JOIN AreaCd a
ON e.AreaId = a.AreaId
WHERE (@Number IS NULL OR e.Number = @Number)
AND (@Type IS NULL OR e.Type = @Type)
AND (@Code IS NULL OR a.Code = @Code)

学习如何使用ADO的参数。. NET，点击这里。

设置参数看起来像这样:

command.Parameters["@Number"].Value = (string.IsNullOrEmpty(number) ? (object) DBNull.Value : number);

我想这就是你想要的:

只是添加了一个简单的技巧;

if(string.IsNullOrEmpty(sNumber))
    sNumber="$$$";               //If sNumber is empty, then selection using sNumber= '$$$' will return nothing.
if(string.IsNullOrEmpty(sType))
    sType="$$$"                 //If sType is empty, then selection using sType = '$$$' will return nothing.
"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId 
AND 
  (Code = " + int nCode + " 
   OR Number = '" + string sNumber + "' 
   OR Type = '" + string sType + "')"

或使用LIKE:

if(string.IsNullOrEmpty(sNumber))
    sNumber="$$$";             //If sNumber is empty, then selection using sNumber LIKE '%$$$%' will return nothing.
if(string.IsNullOrEmpty(sType))
    sType="$$$"                //If sType is empty, then selection using sType LIKE '%$$$%' will return nothing.
"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId 
AND 
  (Code = " + int nCode + "
   OR Number LIKE '%" + string sNumber + "%' 
   OR Type LIKE '%" + string sType + "%')"

查看示例SQL Fiddle

试试这个

  SELECT Number, Type, Code FROM EqNum, AreaCd " +
   "WHERE EqNum.AreaId = AreaCd.AreaId AND 
   isnull(Code,0) = " + int nCode + " or
    isnull(Number,0) = '" + string sNumber + "' or isnull(Type,0) = '" + string sType + "'
相关文章：