赢得MSSQL数据辞书的SQL语句

//我们一般是用union all 改成union看一下,联合查询显示不重复的记录哦。

  判断a表中有而b表中没有的记录

复制代码 代码如下:CREATE VIEW
dbo.vw_db_dictionary AS SELECT TOP 100 PERCENT dbo.sysobjects.name AS
table_name, dbo.sysproperties.[value] AS table_desc,
dbo.syscolumns.name AS field, properties.[value] AS field_desc,
dbo.systypes.name AS field_type, dbo.syscolumns.length AS field_size,
ISNULL(COLUMNPROPERTY(dbo.syscolumns.id, dbo.syscolumns.name, ‘Scale’),
0) AS field_precision, dbo.syscolumns.isnullable AS nullable, CASE WHEN
syscomments.text IS NULL THEN ” ELSE syscomments.text END AS
default_value, CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name,
‘IsIdentity’) = 1 THEN ‘√’ ELSE ” END AS is_identity, CASE WHEN EXISTS
(SELECT 1 FROM sysobjects WHERE xtype = ‘PK’ AND name IN (SELECT name
FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id
= syscolumns.id AND colid = syscolumns.colid))) THEN ‘√’ ELSE ” END AS
is_key FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON
dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN dbo.systypes ON
dbo.syscolumns.xtype = dbo.systypes.xtype LEFT OUTER JOIN
dbo.sysproperties properties ON dbo.syscolumns.id = properties.id AND
dbo.syscolumns.colid = properties.smallid LEFT OUTER JOIN
dbo.sysproperties ON dbo.sysobjects.id = dbo.sysproperties.id AND
dbo.sysproperties.smallid = 0 LEFT OUTER JOIN dbo.syscomments ON
dbo.syscolumns.cdefault = dbo.syscomments.id WHERE (dbo.sysobjects.xtype
= ‘U’) ORDER BY dbo.sysobjects.name

sql UNION过滤重复记录语句并取得不重复的数据

select a.* from tbl1 a

$sql =UNION
ALL-UNION———————-以下句子如何取得不重复的数据?SELECT
DISTINCT TOP 100 PERCENT kirinLDS.dbo.tbl人员调整.部门名 AS 部门名FROM
kirinLDS.dbo.tbl人员 INNER JOIN kirinLDS.dbo.tbl定单 ON
kirinLDS.dbo.tbl人员.工号 = kirinLDS.dbo.tbl定单.业务员 INNER JOIN
kirinLDS.dbo.tbl人员调整 ON kirinLDS.dbo.tbl人员.姓名 =
kirinLDS.dbo.tbl人员调整.人员WHERE (LEFT(kirinLDS.dbo.tbl定单.会计月, 4)
= ‘2009’)

left join tbl2 b

UNION

  • on a.key = b.key

SELECT DISTINCT TOP 100 PERCENT kirinSZ.dbo.tbl人员调整.部门名 AS
部门名FROM kirinSZ.dbo.tbl人员 INNER JOIN kirinSZ.dbo.tbl定单 ON
kirinSZ.dbo.tbl人员.工号 = kirinSZ.dbo.tbl定单.业务员 INNER JOIN
kirinSZ.dbo.tbl人员调整 ON kirinSZ.dbo.tbl人员.姓名 =
kirinSZ.dbo.tbl人员调整.人员WHERE (LEFT(kirinSZ.dbo.tbl定单.会计月, 4) =
‘2009’)

where b.key is null

UNION

         虽然使用in也可以实现,但是这种方法的效率更高一些

SELECT DISTINCT TOP 100 PERCENT kirinLDSbj.dbo.tbl人员调整.部门名 AS
部门名FROM kirinLDSbj.dbo.tbl人员 INNER JOIN kirinLDSbj.dbo.tbl定单 ON
kirinLDSbj.dbo.tbl人员.工号 = kirinLDSbj.dbo.tbl定单.业务员 INNER JOIN
kirinLDSbj.dbo.tbl人员调整 ON kirinLDSbj.dbo.tbl人员.姓名 =
kirinLDSbj.dbo.tbl人员调整.人员WHERE
(LEFT(kirinLDSbj.dbo.tbl定单.会计月, 4) = ‘2009’);

2. 
新建一个与某个表相同结构的表

select * into b

from a where 1<>1

3.between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time
between time1 and time2

select a,b,c, from table1 where a
not between 数值1 and
数值2

  1. 说明:包括所有在 TableA 中但不在 TableB和TableC
    中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a
from tableC)

5.
初始化表,可以将自增长表的字增长字段置为1

TRUNCATE TABLE table1

6.多语言设置数据库或者表或者order by的排序规则

        
–修改用户数据库的排序规则

ater database dbname collate
SQL_Latin1_General_CP1_CI_AS

发表评论

电子邮件地址不会被公开。 必填项已用*标注