SQL语句中CASE WHEN以及如何运用 CAST将两个SQL字段合成一个字段显示(oracle和sqlserver的区别)

 

SELECT [Prt_AlarmRecord].[GUID]
      ,[Prt_AlarmRecord].[AlarmGUID]
      ,[Prt_AlarmRecord].[StartTime]
      ,[Prt_AlarmRecord].[EndTime]
      ,CAST([Prt_AlarmRecord].[DurationTime] as decimal(18,1)) AS [DurationTime]
      ,[Prt_AlarmRecord].[AlarmLevel]
      ,[Prt_AlarmRecord].[AlarmType]
      ,[Prt_AlarmRecord].[AlarmCode]
      ,[Prt_AlarmRecord].[PartCode]
      ,t2.[GUID] as Part_GUID
      ,t2.[ParentGUID] as Part_ParentGUID
      ,t2.PartNameFull
      ,[Prt_AlarmRecord].[AlarmDesc]
      ,[Prt_AlarmRecord].[AlarmPosition]
      ,[Prt_AlarmRecord].[Remark]
      ,[Prt_AlarmRecord].[CreateActor]
      ,[Prt_AlarmRecord].[CreateTime]
      ,[Prt_AlarmRecord].[EndEditActor]
      ,[Prt_AlarmRecord].[EndEditTime]
    FROM  [Prt_AlarmRecord] 
    LEFT JOIN
     (SELECT
        child.[GUID],child.[ParentGUID],child.PartCode  AS PartCode,
        CASE WHEN
        parent.[PartName]
        IS NULL
        THEN 
        child.[PartName]
        ELSE
        (CAST(isnull(parent.[PartName],'')  as varchar(100))) +'|'+(CAST(child.[PartName] as varchar(100)))
        END
        AS PartNameFull

    FROM Bmt_Part child  LEFT JOIN Bmt_Part parent on parent.[GUID]=child.[ParentGUID]
    ) as t2
    on [Prt_AlarmRecord].[PartCode]= t2.PartCode 
     WHERE [StartTime] >='2002-01-01 00:00:00' AND [EndTime]<= '2020-05-06 23:59:59'
and [Prt_AlarmRecord].[PartCode] in (select [Prt_AlarmRecord].[PartCode] from [dbo].[Prt_AlarmRecord]  
WHERE  [Prt_AlarmRecord].[PartCode]='1' or [Prt_AlarmRecord].[PartCode]='2' or [Prt_AlarmRecord].[PartCode]='3' )  
ORDER BY PartNameFull ASC , [StartTime] DESC

oracle中，如何将两个字段数据合并成一个字段显示，接下来看一下在sql server和pl/sql的区别

sql server中如何合并（用Cast()函数）

--1、创建模拟的数据表---
create table GoodsCate(
    Mid int not null,
    Code varchar(10) not null,
    Name varchar(20) not null,
)
--2、添加模拟数据--
insert into GoodsCate(Mid,Code,Name) values(1,'6801','电子仪器')
insert into GoodsCate(Mid,Code,Name) values(2,'6802','激光仪器')
insert into GoodsCate(Mid,Code,Name) values(3,'6803','扫描仪器')
--3、未合并的结果--
select * from GoodsCate
--4、sqlserver中的合并--
select (CAST(Code as varchar(20))+'_'+CAST(Name as varchar(20))) 合并结果集 from GoodsCate

那么，在oracle中如何合并数据（用Concat()函数）

--1、创建模拟的数据表 ---
create table GoodsCate(
    MID INTEGER not null,
    CODE NVARCHAR2(100) not null,
    NAME NVARCHAR2(100) not null
)
--2 、添加模拟数据 --
insert into GoodsCate(Mid,Code,Name) values(1,'6801','电子仪器')
insert into GoodsCate(Mid,Code,Name) values(2,'6802','激光仪器')
insert into GoodsCate(Mid,Code,Name) values(3,'6803','扫描仪器')
--3 、未合并的结果 --
select * from GoodsCate
--4 、oracle 中的合并--
select (Concat(Concat(CODE,'_'),NAME)) 合并结果集 from GoodsCate