An's Blog
收藏、分享 …
Toggle navigation
Home
Cesium
SuperMap
ArcGIS
MapboxGL
CentOS
GeoServer
Favorites
Archives
Tags
常用SQL语句
2023-11-07 15:42:33
3
0
0
admin
## **1. 查询数据库中所有表名** select name from sysobjects where xtype='u' 或 select * from sys.tables ## **2. 查询数据库中所有表名和行数** use [db_name] go SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC 或 select a.name as 表名, max(b.rows) as 记录条数 from sysobjects a ,sysindexes b where a.id=b.id and a.xtype='u' group by a.name order by max(b.rows) desc 或 select schema_name(t.schema_id) as [ Schema ], t. name as TableName,i. rows as [RowCount] from sys.tables as t, sysindexes as i where t.object_id = i.id and i.indid <=1 ## **3. 删除所有重复的行,但保留一个** WITH cte AS ( SELECT BDCDYH, YWH, QSZT, ROW_NUMBER ( ) OVER ( PARTITION BY BDCDYH, YWH, QSZT ORDER BY QSZT DESC ) AS rn FROM FDCQ2_QJ ) DELETE FROM cte WHERE rn > 1; 或 DELETE FROM TABLE WHERE ID NOT IN ( SELECT MIN(ID) FROM TABLE GROUP BY Field1, Field2, Field3, ... ) 备注: > 使用这种格式我得到以下错误,任何想法?"ERROR 1093(HY000):您无法在FROM子句中为更新指定目标表'Table'" @ > M1ke MySQL不允许从子查询引用的主表更新,但有一个解决方法;将'FROM Table'更改为'FROM(SELECT * FROM > Table)AS t1'将表存储在临时表中,以便更新主表。 谢谢,我实际上在其他地方找到了相同的答案,但不记得在哪里 - 所以加1! > 很好。但是如果我们没有主键呢? 如果primery键是guid,而不是整数 > 请注意以OP建议的"大量行"的方式使用DELETE。如果磁盘IO不是内存或内存优化表,则需要花费大量时间。此外,恢复模型很重要,如果设置为FULL,您可能会将大量数据写入事务日志,这会增加更多时间(如果不小心,可以填满您的驱动器)。分组/分钟或DISTINCT > *可能是最安全的,但CTE / ROW_NUMBER是可接受的,并且可能是最快的,只要RAM允许它没有分页到磁盘(再次,另一个巨大的时间消费者)。 > @merdan,它适用于任何可排序的东西。例如以下是有效的select min(id) from ( select newid() as > id union select newid() as id ) as a
Pre:
CentOS7安装达梦DM8数据库(信创)
Next:
三维管网构建操作手册
0
likes
3
Weibo
Wechat
Tencent Weibo
QQ Zone
RenRen
Table of content