union

示例1

select
   DepID as id,
   DepCode as code,
   FatherID,
   1 as TypeID,
   DepName as name,
   InputCode,
   RecStatus,
   isDeleted,
   1 as multi,
   depID as RealID
from
   View_Dep
union
select
   DeviceID*100000 as id,
   deviceCode as code,
   DepID as fatherid,
   2 as typeID,
   DeviceName as name,
   '' as inputCode,
   0,
   0,
   100000,
   deviceID 
from
   Device
union
select
   convert(bigint,1) *b.ItemID*100000*100000 as id,
   c.ItemCode,
   a.DeviceID*100000 as fatherID,
   3 as typeID,
   c.ItemName,
   '',
   0,
   0,
   convert(bigint,100000) * 100000,
   b.ItemID
from
   device a
   join devItem b on  a.DevTypeID = b.DevTypeID
   join ItemInf c on  b.ItemID = c.ItemID

TypeID字段在数据库表中不存在,为该视图添加的字段
用于区分是从哪张表里面取出来的

区分：3 as typeID 与 typeID as '3'
前者新建一列，值为3
后者将一列改名为'3'

示例2

select  DepID,   DepCode,   FatherID,   OrgID,   DepTypeID,   DepName,   InputCode,   IsOutUnit,   RecStatus,   IsDeleted,   ModifyPersonID,   ModifyTime
from DepInfo
where IsDeleted = 0
union
select -100,null,null,null,null,'(所有部门)',null,null,1,0,null,null
union
select -99,null,null,null,null,'(本人所在部门)',null,null,1,0,null,null

在数据库里面不添加记录，通过视图添加两条记录"(所有部门)","(本人所在部门)"

top,percent

select top 50 percent * from Journals
select top 50 * from Journals
select top 50 percent id from Journals

LIKE '%'

SELECT * FROM Persons
WHERE City LIKE 'N%'

从 "Persons" 表中选取居住在以 "N" 开始的城市里的人

in

SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

BETWEEN AND

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'

介于 "Adams"和 "Carter"之间的人
可结合order by

SELECT * FROM Journals
WHERE id 
BETWEEN 'a3a2c0d0' AND 'zlzlzl'
ORDER BY id DESC