Sql
1.找到表ppt里面num最小的数
使用聚合函数:select * from ppt where num=(select min(num) from ppt)
不使用聚合函数:select * from ppt where num<=all(select num from ppt)
2.选择表ppt中num重复的记录
select * from ppt
where num in(select num from ppt group by num having count (num>1))
select <列名> from <表名> //select后面跟着查询后要显示的列,多列“,”隔开
where [查询条件]
groupy by <列名>
having[条件]
order by<排序列> desc/asc
//聚合函数:sum、count、avg、max、min
添加显示新列 select<列名>,<列值> as <新列名>
使用聚合函数作为条件Where 列=(select 聚合函数(列名) from 表名)
Id category count summary
1 a 5 A2001
2 a 2 A2002
3 a 11 A2003
4 b 10 B2001
5 b 6 B2002
6 b 3 B2003
7 c 9 C2001
8 c 8 C2002
9 c 4 C2003
10 c 4 C2004
根据指定规则对列进行分组 group by <列名>
将category字段进行分组,计算各组cout字段总和并显示列名右cout改名为数量之和
select category, sum(count) as 数量之和
from groupbyDemo
group by category
显示category和数量之和两列,数量之和列的值为sum(count)
category 数量之和
a 18
b 19
c 25查询结果排序 order by <列> desc/asc desc降序;asc升序
SELECT category, SUM(COUNT) AS 数量之和, summary //显示3列category,数量之和,summary
FROM groupByDemo
GROUP BY category
ORDER BY category desc
category 数量之和 smmary
C 25 C2001
b 19 B2001
a 18 A2001
查询summary 会显示各组数据的第一条记录分组后筛选 having [条件]
SELECT category, SUM(COUNT) AS 数量之和 FROM groupByDemo
GROUP BY category
HAVING SUM(COUNT) > 18
将字段category分组,计算各组cout总和,筛选cout总和大于18的组并显示在列名为数量之和
category 数量之和
a 18
b 19先where筛选行,再having筛选组
where是分组前的条件,在分组前根据条件进行筛选;having是分组后的,在分组后根据条件进行筛选
SELECT category, SUM(COUNT)FROM groupByDemo
WHERE COUNT > 10
GROUP BY category
HAVING SUM(COUNT) > 8
category 数量之和
a 11
where首先将每行cout>10的行挑选出来
sum(cout)再根据category计算各组的cout和
having将分组后cout和>8的组显示出来
复制表结构:Select * into B from A where 1=0
复制表数据:Select * into B from A
四表联查:select * from a,b,c,d where 关联条件
建立临时表:creat table #temp (字段1 类型,字段2 类型 …)
取出表A中第31到第40记录:select top 10 * from A where id not in (select top 30 id from A).