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
