常用SQL语句(嵌套子查询/随机等等)详细整理

insert into Table1(Name,des,num) values ('ltp','thisisbest',10);
select @@identity as 'Id'

select name,Sex=(case Sex when '1' then '男' when '0' then '女' end) from Tablename

select a,b,c from Table1 where a IN (select a from Table2)

select a.title,a.username,b.adddate from tablename a,(select max(adddate) adddate from tablename where tablename.title=a.title) b

SQL ServerSelect Top 10 From Tablename Order By NewID()

AessSelect Top 10 From Tablename Order By Rnd(ID)

Rnd(ID) 其中的ID 是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段UserName

Select Top 10 From 表Order BY Rnd(Len(UserName))

MySqlSelect From 表Order By Rand() Limit 10

select UserID from Aounts_Users where UserName is not null group by UserID having count ()>1

SELECT CategoryName,ProductName FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID;

select from UserValue where UserID between 2 and 5;

Select from TabSchedule where datediff(minute,getdate(),开始时间)<5

SELECT DAY(DATEADD(dd, -DAY('2008-02-13'),DATEADD(mm, 1, '2008-02-13'))) AS 'DayNumber'

Select From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

1.select from tablename where column1 like '[A-M]%' 这样可以选择出column 字段中首字母在A-M 之间的记录
2.select from tablename where column1 like '[ABC]%' 这样可以选择出column 字段中首字母是A 或者B 或者C 的记录
3.select from tablename where column1 like '[A-CG]%' 这样可以选择出column 字段中首字母在A-C 之间的或者是G 的记录
4.select from tablename where column1 like '[^C]%' 这样可以选择出column 字段中首字母不是C 的记录

select into b from a where 1<>1 或 select 0 into [b] from [a]

insert into b(Name,des,num) select Name,des,num from Table1;