1.记录分组叠加
select c_GId,c_Id from t_Cargo
where c_GId in ('MH-C2970','MX-B0794')
查询结果:
![](https://pic002.cnblogs.com/images/2012/174051/2012022913510971.jpg)
select b.c_GId,LEFT(b.CargoList,len(b.CargoList)-1) from
(
select a.c_GId,
(
select c_Id + ';' from t_Cargo
where c_GId=a.c_GId
for xml path('')
) as CargoList
from t_Cargo a
where a.c_GId in ('MH-C2970','MX-B0794')
group by a.c_GId
)
b
查询结果:
--查询各表中的索引 SELECT d.name 表名, c.name 索引名称, b.name 索引字段, CASE c.type WHEN '1' THEN '聚集' WHEN '2' THEN '非聚集' END AS 索引类型 , c.is_unique 是否唯一 from sys.index_columns a LEFT JOIN sys.columns b ON (a.column_id=b.column_id AND a.object_id=b.object_id) LEFT JOIN sys.indexes c ON (a.index_id=c.index_id AND a.object_id=c.object_id) LEFT JOIN sys.tables d ON a.object_id=d.object_id WHERE d.object_id=object_id('guest')
--用not in的视图和存储过程 SELECT a.NAME,b.definition, CASE WHEN a.xtype='v' THEN '视图' WHEN a.xtype='p' THEN '存储过程' ELSE '其他' END AS 类型 from sysobjects a LEFT JOIN sys.all_sql_modules b ON a.id=b.object_id WHERE a.xtype IN ('v','p') AND CHARINDEX('not in', b.definition,0) >0 ORDER BY 类型 DESC