实用的T_sql语句收藏（持续更新）

 

1.记录分组叠加

select c_GId,c_Id from t_Cargo 
where c_GId in ('MH-C2970','MX-B0794')

查询结果：

 

 

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