SQL语句の集锦

7.某一个字段重复根据另一个字段取最新的一条

例如：progress_id 是主键 int 自增

select * from table_XXX

 

select progress_id,detailed_ListId progress_detailed_ListId,workamount,createuser progress_Createuser ,createtime progress_Createtime,(select sum(workamount)sumworkamount from  table_XXX)sumworkamount from table_XXX where progress_id in(
select max(progress_id) from table_XXX group by detailed_listid
)

 

 

 

6.删除数据后根据主键从备份表中恢复

insert  sameTable_1 (name,dz) select name,dz from sameTable_1_bak where not exists (select 1  from sameTable_1 where sameTable_1_bak.itmid=sameTable_1.itmid)

 

5.查询数据库所有表的大小

exec sp_MSforeachtable 'exec sp_spaceused ''?'''  

 

4.根据某一个字段重复只取第一条数据

select * from  News_ITM a left join
(select * from (
 select *, row_number() over (partition by newid order by msg desc) as group_idx  
    from NewsComment_ITM) s where group_idx = 1) b on
    a.id = b.newId

 

3.即时获取新增自增ID值

string sql="insert into XX表 values(aa,bb,cc);select @@IDENTITY";

 

2.时间为NULL，转成空字符串

 

select  ISNULL(convert(varchar(100),subDateTime,20),'') ,subDateTime from News_ITM

 

1.已有字段添加主键（修改）

alter table [dbo].[News_ITM] add Constraint primarykey  primary key(id)