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

转载于:https://www.cnblogs.com/Lulus/p/7874253.html

相关资源：scrollview 示例