频道导航

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

2019-01-03 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了常用SQL语句(嵌套子查询/随机等等)详细整理，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1．sql 插入语句得到自动生成的递增ID值
<div class="codetitle"><a style="CURSOR: pointer" data="45344" class="copybut" id="copybut45344" onclick="doCopy('code45344')"> 代码如下:

<div class="codebody" id="code45344">
insert into Table1(Name,des,num) values ('ltp','thisisbest',10);
select @@identity as 'Id'

2．实现是1 或0 想显示为男或女
<div class="codetitle"><a style="CURSOR: pointer" data="37058" class="copybut" id="copybut37058" onclick="doCopy('code37058')"> 代码如下:

<div class="codebody" id="code37058">
select name,Sex=(case Sex when '1' then '男' when '0' then '女' end) from Tablename

3．嵌套子查询
<div class="codetitle"><a style="CURSOR: pointer" data="15031" class="copybut" id="copybut15031" onclick="doCopy('code15031')"> 代码如下:

<div class="codebody" id="code15031">
select a,b,c from Table1 where a IN (select a from Table2)

4．显示文章、提交人和最后回复时间
<div class="codetitle"><a style="CURSOR: pointer" data="54552" class="copybut" id="copybut54552" onclick="doCopy('code54552')"> 代码如下:

<div class="codebody" id="code54552">
select a.title,a.username,b.adddate from tablename a,(select max(adddate) adddate from tablename where tablename.title=a.title) b

5．随机提取条记录的例子
<div class="codetitle"><a style="CURSOR: pointer" data="35990" class="copybut" id="copybut35990" onclick="doCopy('code35990')"> 代码如下:<div class="codebody" id="code35990">
sql Server：Select Top 10 From Tablename Order By NewID() Access：Select Top 10 From Tablename Order By Rnd(ID) Rnd(ID) 其中的ID 是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段UserName Select Top 10 From 表Order BY Rnd(Len(UserName)) MysqL：Select From 表Order By Rand() Limit 10

6．在同一表内找相同属性的记录
<div class="codetitle"><a style="CURSOR: pointer" data="18236" class="copybut" id="copybut18236" onclick="doCopy('code18236')"> 代码如下:<div class="codebody" id="code18236">
select UserID from Accounts_Users where UserName is not null group by UserID having count ()>1

7．查询类别所有的产品对应数据
<div class="codetitle"><a style="CURSOR: pointer" data="44939" class="copybut" id="copybut44939" onclick="doCopy('code44939')"> 代码如下:<div class="codebody" id="code44939">
SELECT CategoryName,ProductName FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID;

8．按范围查询编号在2 到5 之间的用户信息
<div class="codetitle"><a style="CURSOR: pointer" data="64829" class="copybut" id="copybut64829" onclick="doCopy('code64829')"> 代码如下:<div class="codebody" id="code64829">
select from UserValue where UserID between 2 and 5;

9．日程安排提前5 分钟提醒
<div class="codetitle"><a style="CURSOR: pointer" data="31717" class="copybut" id="copybut31717" onclick="doCopy('code31717')"> 代码如下:<div class="codebody" id="code31717">
Select from TabSchedule where datediff(minute,getdate(),开始时间)<5

10．得出某日期所在月份的最大天数
<div class="codetitle"><a style="CURSOR: pointer" data="31053" class="copybut" id="copybut31053" onclick="doCopy('code31053')"> 代码如下:<div class="codebody" id="code31053">
SELECT DAY(DATEADD(dd,-DAY('2008-02-13'),DATEADD(mm,1,'2008-02-13'))) AS 'DayNumber'

11．按姓氏笔画排序
<div class="codetitle"><a style="CURSOR: pointer" data="6486" class="copybut" id="copybut6486" onclick="doCopy('code6486')"> 代码如下:<div class="codebody" id="code6486">
Select From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

12．通配符的一些用法
<div class="codetitle"><a style="CURSOR: pointer" data="80188" class="copybut" id="copybut80188" onclick="doCopy('code80188')"> 代码如下:<div class="codebody" id="code80188">
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 的记录

13．复制表结构（只复制结构,源表名：a，目标表名：b）
<div class="codetitle"><a style="CURSOR: pointer" data="32526" class="copybut" id="copybut32526" onclick="doCopy('code32526')"> 代码如下:<div class="codebody" id="code32526">
select into b from a where 1<>1 或 select top 0 into [b] from [a]

14．复制表数据（复制数据，源表名：a，目标表名：b）
<div class="codetitle"><a style="CURSOR: pointer" data="75511" class="copybut" id="copybut75511" onclick="doCopy('code75511')"> 代码如下:<div class="codebody" id="code75511">
insert into b(Name,num) select Name,num from Table1;

原文链接：https://www.f2er.com/mssql/63620.html

sql常用语句