union
示例1
select DepID as id, DepCode as code, FatherID, 1 as TypeID, DepName as name, InputCode, RecStatus, isDeleted, 1 as multi, depID as RealIDfrom View_Depunionselect DeviceID*100000 as id, deviceCode as code, DepID as fatherid, 2 as typeID, DeviceName as name, '' as inputCode, 0, 0, 100000, deviceID from Deviceunionselect 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.ItemIDfrom 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, ModifyTimefrom DepInfowhere IsDeleted = 0unionselect -100,null,null,null,null,'(所有部门)',null,null,1,0,null,nullunionselect -99,null,null,null,null,'(本人所在部门)',null,null,1,0,null,null
在数据库里面不添加记录,通过视图添加两条记录"(所有部门)","(本人所在部门)"
top,percent
select top 50 percent * from Journalsselect top 50 * from Journalsselect top 50 percent id from Journals
LIKE '%'
SELECT * FROM PersonsWHERE City LIKE 'N%'
从 "Persons" 表中选取居住在以 "N" 开始的城市里的人
in
SELECT * FROM PersonsWHERE LastName IN ('Adams','Carter')
BETWEEN AND
SELECT * FROM PersonsWHERE LastNameBETWEEN 'Adams' AND 'Carter'
介于 "Adams"和 "Carter"之间的人
可结合order by
SELECT * FROM JournalsWHERE id BETWEEN 'a3a2c0d0' AND 'zlzlzl'ORDER BY id DESC