mysql - 查询mysql时如何获取非空值

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 7 年前。

Improve this question

我正在尝试编写一个查询，该查询将从关联表中获取设备记录及其关联数据。我的查询如下:

select e.Name,ec.Name as EquipmentClassName, s.Name as SiteName
from  equipment e
left join site s on (e.SiteID = s.ID or e.SiteID is NULL and s.ID is NULL)
left join equipmentclass ec on e.EquipmentClassID=ec.ID
where e.id like '%' and (
  (isNull(e.Name) OR e.Name=  CASE  WHEN COALESCE('','') = '' THEN e.Name   ELSE ''  END)
 and (isNull(e.EquipmentClassID) OR e.EquipmentClassID=  CASE  WHEN COALESCE('1','') = '' THEN e.EquipmentClassID   ELSE ''  END)
 and (isNull(e.SiteID) OR e.SiteID=  CASE  WHEN COALESCE('','') = '' THEN e.SiteID   ELSE ''  END)
)

好的，当我不在 where 子句中传递任何参数时，我也会得到所有记录，包括空记录。但是当我传递任何参数时，我会再次获得所有记录。我在这里期待的是过滤后的数据。

假设我有两条记录Equip1、class1、null 和 Equi2、null、Site2

现在在 where 子句中我传递 Class1，所以我应该得到一条包含 class1 的记录而不是两条记录。

我该怎么做？

Answer 1

CREATE PROCEDURE equipmentData
@Name                   varchar(90) = null, 
@EquipmentClassID       varchar(90) = null,
@LocationID             varchar(90) = null,
@ParentEquipmentID      varchar(90) = null,
@EquipmentLevelID       varchar(90) = null,
@SiteID                 varchar(90) = null

AS
BEGIN

select e.ID,
e.Name,
ec.Name as EquipmentClassName,
 loc.Name as LocationName,
  pe.Name as ParentEquipmentName ,
  eql.Name as EquipmentLevelName, 
  s.Name as SiteName,
  ep.ID as EquipmentPropertyID,
  ep.Name as EquipmentPropertyName,
  ep.Description as EquipmentPropertyDesc, 
  ep.Mandatory,
  ep.SeqNbr,
  ep.DataType,
  ep.RuleIDs,
  ep.DefaultValue,
  ep.UOM,
  epv.Value
from  equipment e
inner join equipmentclass ec            on   e.EquipmentClassID=ec.ID
left join site s                        on   (e.SiteID = s.ID or e.SiteID is NULL and s.ID is NULL)
left join location loc                  on   e.LocationID=loc.ID
left join equipment pe                  on   pe.ID=e.ParentEquipmentID
left join equipmentlevel eql            on   eql.ID = e.EquipmentLevelID
left join extendedproperty ep           on   ep.ObjectTypeID = e.ObjectTypeID 
left join extendedpropertyvalue epv     on   epv.InstanceID = e.ID
where 
e.id like '%' 
AND (e.Name             = @Name                 or isnull(e.Name,'')= '')
AND (@EquipmentClassID  = @EquipmentClassID     or isnull(e.EquipmentClassID,'')= '')
AND (@LocationID        = @LocationID           or isnull(e.LocationID,'')= '')
AND (@ParentEquipmentID = @ParentEquipmentID    or isnull(e.ParentEquipmentID,'')= '')
AND (@EquipmentLevelID  = @EquipmentLevelID     or isnull(e.EquipmentLevelID,'')= '')
AND (@SiteID            = @SiteID               or isnull(e.SiteID,'')= '')

END

EXEC equipmentData @Name='',@EquipmentClassID='',
 @LocationID='',@ParentEquipmentID='',@EquipmentLevelID='',@SiteID=''

首先复制上面的代码，除了最后一行，即

EXEC equipmentData @Name='',@EquipmentClassID='',
     @LocationID='',@ParentEquipmentID='',@EquipmentLevelID='',@SiteID=''

然后按 F5，然后根据您的需要通过在单引号中传递参数来运行最后一行代码......您将获得数据。