这里记录一些sql复杂的语句。
GROUP分组
GROUP BY聚合函数在聚合行的时候顺序是不确定的，多次运行的结果都有可能会不同。
必须要用子查询或者程序逻辑来获取特定的行的结果。
1
| SELECT T.* FROM (select * from `test` order by b desc limit 999999) T group by T.a
|
循环语句
1
2
3
4
5
6
7
| declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
|
用临时表加While循环的方法, 对Customers表中的CompanyName列进行遍历
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| create table #temp
(
id int identity(1,1),
customer nvarchar(50)
)
declare @customer nvarchar(50)
declare @n int
declare @rows int
select @n=1
insert #temp(customer) select distinct companyname from customers
select @rows = @@rowcount
while @n <= @rows
begin
select @customer = companyname
from customers
where companyname=(select customer from #temp where id = @n)
order by companyname desc
print(@customer)
select @n = @n + 1
end
|
Nadie da palos de balde.