34,590
社区成员
发帖
与我相关
我的任务
分享这段SQL语句怎么写，想了半天无从下手！！
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
------------------------------------------------------------------------------
1 1 10 11 15 16 17 12 12
2 1 5 8 11 14 12 11 11
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
5 2 6 45 42 12 17 13 19
6 3 7 8 5 20 14 18 18
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
===================================================================================
求一SQL语句，要求按照每行中哪个最小的排序.得到的结果如下！
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
------------------------------------------------------------------------------
3 1 2 14 13 18 20 14 17 (min=2)
6 3 7 8 5 20 14 18 18 (min=5)
4 2 5 12 10 11 14 17 18 (min=5)
2 1 5 8 11 14 12 11 11 (min=5)
5 2 6 45 42 12 17 13 19 (min=6)
7 3 8 12 14 13 18 19 16 (min=8)
1 1 10 11 15 16 17 12 12 (min=10)
8 3 10 14 11 15 16 17 19 (min=10)
注意：small1 small2 small3 small4 small5 small6 small7中的字段有可能等于空的,谢谢大家帮忙！！
最好用一条SQL语句得到我要的结果，不要借助函数，临时表来处理！！
------------------------------------------------------------------------------
1 1 10 11 15 16 17 12 12
2 1 5 8 11 14 12 11 11
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
5 2 6 45 42 12 17 13 19
6 3 7 8 5 20 14 18 18
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
===================================================================================
求一SQL语句，要求按照每行中哪个最小的排序.得到的结果如下！
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
------------------------------------------------------------------------------
3 1 2 14 13 18 20 14 17 (min=2)
6 3 7 8 5 20 14 18 18 (min=5)
4 2 5 12 10 11 14 17 18 (min=5)
2 1 5 8 11 14 12 11 11 (min=5)
5 2 6 45 42 12 17 13 19 (min=6)
7 3 8 12 14 13 18 19 16 (min=8)
1 1 10 11 15 16 17 12 12 (min=10)
8 3 10 14 11 15 16 17 19 (min=10)
注意：small1 small2 small3 small4 small5 small6 small7中的字段有可能等于空的,谢谢大家帮忙！！
最好用一条SQL语句得到我要的结果，不要借助函数，临时表来处理！！
...全文
请发表友善的回复…
发表回复
wangxuelid 2007-12-08
- 打赏
- 举报
学习，，学习，，，向高手学习，，
ivyrich 2007-12-08
- 打赏
- 举报
个人认为,
NULL,表示"无",既不作最大,也不作最小
遇到字段为NULL时就不参与比较,后面的顶上.
NULL,表示"无",既不作最大,也不作最小
遇到字段为NULL时就不参与比较,后面的顶上.
Limpire 2007-12-08
- 打赏
- 举报
-->测试数据1: @T
declare @T table (smallid int,smallbigid int,small1 int,small2 int,small3 int,small4 int,small5 int,small6 int,small7 int)
insert into @T
select 1,1,10,11,15,16,17,12,12 union all
select 2,1,5,8,11,14,12,null,11 union all
select 3,1,2,14,13,18,20,14,17 union all
select 4,2,5,12,10,11,14,17,18 union all
select 5,2,6,45,42,12,17,13,19 union all
select 6,3,7,8,5,20,14,18,18 union all
select 7,3,8,12,14,13,18,19,16 union all
select 8,3,10,14,11,15,16,17,19
-->NULL按最大处理
select a.* from @T a inner join
(
select smallid,small=min(small)
from
(
select smallid,small=small1 from @T union all
select smallid,small=small2 from @T union all
select smallid,small=small3 from @T union all
select smallid,small=small4 from @T union all
select smallid,small=small5 from @T union all
select smallid,small=small6 from @T union all
select smallid,small=small7 from @T
) t
group by smallid
) b
on a.smallid=b.smallid
order by b.small
/*
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
2 1 5 8 11 14 12 NULL 11
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
1 1 10 11 15 16 17 12 12
*/
-->NULL按最小处理
select a.* from @T a inner join
(
select smallid,small=min(isnull(small,-2147483648))
from
(
select smallid,small=small1 from @T union all
select smallid,small=small2 from @T union all
select smallid,small=small3 from @T union all
select smallid,small=small4 from @T union all
select smallid,small=small5 from @T union all
select smallid,small=small6 from @T union all
select smallid,small=small7 from @T
) t
group by smallid
) b
on a.smallid=b.smallid
order by b.small
/*
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2 1 5 8 11 14 12 NULL 11
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
1 1 10 11 15 16 17 12 12
*/
ivyrich 2007-12-08
- 打赏
- 举报
学习....
遇到空值理应不计在其中,也就是不能作最小值算.
遇到空值理应不计在其中,也就是不能作最小值算.
Limpire 2007-12-08
- 打赏
- 举报
-->测试数据1: @T
declare @T table (smallid int,smallbigid int,small1 int,small2 int,small3 int,small4 int,small5 int,small6 int,small7 int)
insert into @T
select 1,1,10,11,15,16,17,null,12 union all
select 2,1,5,8,11,14,12,11,11 union all
select 3,1,2,14,13,18,20,14,17 union all
select 4,2,5,12,10,11,14,17,18 union all
select 5,2,6,45,42,12,17,13,19 union all
select 6,3,7,8,5,20,14,18,18 union all
select 7,3,8,12,14,13,18,19,16 union all
select 8,3,10,14,11,15,16,17,19
select a.* from @T a inner join
(
select smallid,small=min(isnull(small,-2147483648))
from
(
select smallid,small=small1 from @T union all
select smallid,small=small2 from @T union all
select smallid,small=small3 from @T union all
select smallid,small=small4 from @T union all
select smallid,small=small5 from @T union all
select smallid,small=small6 from @T union all
select smallid,small=small7 from @T
) t
group by smallid
) b
on a.smallid=b.smallid
order by b.small
/*
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 10 11 15 16 17 NULL 12
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
2 1 5 8 11 14 12 11 11
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
*/
declare @T table (smallid int,smallbigid int,small1 int,small2 int,small3 int,small4 int,small5 int,small6 int,small7 int)
insert into @T
select 1,1,10,11,15,16,17,null,12 union all
select 2,1,5,8,11,14,12,11,11 union all
select 3,1,2,14,13,18,20,14,17 union all
select 4,2,5,12,10,11,14,17,18 union all
select 5,2,6,45,42,12,17,13,19 union all
select 6,3,7,8,5,20,14,18,18 union all
select 7,3,8,12,14,13,18,19,16 union all
select 8,3,10,14,11,15,16,17,19
select a.* from @T a inner join
(
select smallid,small=min(isnull(small,-2147483648))
from
(
select smallid,small=small1 from @T union all
select smallid,small=small2 from @T union all
select smallid,small=small3 from @T union all
select smallid,small=small4 from @T union all
select smallid,small=small5 from @T union all
select smallid,small=small6 from @T union all
select smallid,small=small7 from @T
) t
group by smallid
) b
on a.smallid=b.smallid
order by b.small
/*
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 10 11 15 16 17 NULL 12
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
2 1 5 8 11 14 12 11 11
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
*/
wangxuelid 2007-12-08
- 打赏
- 举报
如果行数多的话，那用union all 我想都会疯，，，
中国风 2007-12-08
- 打赏
- 举报
--只排序不用显示：
create table T(smallid int,smallbigid int, small1 int, small2 int,
small3 int, small4 int, small5 int, small6 int, small7 int)
insert T select 1, 1, 10, 11,15, 16, 17, 12,12 --空值
insert T select 2, 1, 5, 8, 11, 14, 12, 11, 11 --空值
insert T select 3, 1, 2, 14, 13, 18, 20, 14, 17
insert T select 4, 2, 5, 12, 10, 11, 14,17, 18
insert T select 5, 2, 6, 45, 42, 12, 17, 13, 19
insert T select 6, 3, 7, 8, 5 ,20, 14, 18, 18
insert T select 7, 3, 8, 12, 14, 13, 18, 19, 16
insert T select 8, 3, 10, 14, 11, 15, 16, 17, 19
go
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7
from
T
order by (select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp) asc
--truncate table T
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
2 1 5 8 11 14 12 11 11
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
1 1 10 11 15 16 17 12 12
（所影响的行数为 8 行）
----------
create table T(smallid int,smallbigid int, small1 int, small2 int,
small3 int, small4 int, small5 int, small6 int, small7 int)
insert T select 1, 1, 10, 11,15, 16, 17, 12,12 --空值
insert T select 2, 1, 5, 8, 11, 14, 12, 11, 11 --空值
insert T select 3, 1, 2, 14, 13, 18, 20, 14, 17
insert T select 4, 2, 5, 12, 10, 11, 14,17, 18
insert T select 5, 2, 6, 45, 42, 12, 17, 13, 19
insert T select 6, 3, 7, 8, 5 ,20, 14, 18, 18
insert T select 7, 3, 8, 12, 14, 13, 18, 19, 16
insert T select 8, 3, 10, 14, 11, 15, 16, 17, 19
go
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7,
[MIN]=(select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp)
from
T
order by [MIN] asc
--truncate table T
smallid smallbigid small1 small2 small3 small4 small5 small6 small7 MIN
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
3 1 2 14 13 18 20 14 17 2
4 2 5 12 10 11 14 17 18 5
2 1 5 8 11 14 12 11 11 5
6 3 7 8 5 20 14 18 18 5
5 2 6 45 42 12 17 13 19 6
7 3 8 12 14 13 18 19 16 8
8 3 10 14 11 15 16 17 19 10
1 1 10 11 15 16 17 12 12 10
（所影响的行数为 8 行）
Limpire 2007-12-08
- 打赏
- 举报
还有NULL值算最小还是最大？
wangxuelid 2007-12-08
- 打赏
- 举报
理解错误：以为对每行的数据都需要从小到大排序呢，，，看来想复杂了，
Limpire 2007-12-08
- 打赏
- 举报
6 3 7 8 5 20 14 18 18 (min=5)
4 2 5 12 10 11 14 17 18 (min=5)
2 1 5 8 11 14 12 11 11 (min=5)
1 1 10 11 15 16 17 12 12 (min=10)
8 3 10 14 11 15 16 17 19 (min=10)
min相等时排序依据是什么？
duanzhi1984 2007-12-08
- 打赏
- 举报
roy_88 你真是太快了啊。哎!!!!!
Limpire 2007-12-08
- 打赏
- 举报
-->测试数据1: @T
declare @T table (smallid int,smallbigid int,small1 int,small2 int,small3 int,small4 int,small5 int,small6 int,small7 int)
insert into @T
select 1,1,10,11,15,16,17,12,12 union all
select 2,1,5,8,11,14,12,11,11 union all
select 3,1,2,14,13,18,20,14,17 union all
select 4,2,5,12,10,11,14,17,18 union all
select 5,2,6,45,42,12,17,13,19 union all
select 6,3,7,8,5,20,14,18,18 union all
select 7,3,8,12,14,13,18,19,16 union all
select 8,3,10,14,11,15,16,17,19
select a.* from @T a inner join
(
select smallid,small=min(small)
from
(
select smallid,small=small1 from @T union all
select smallid,small=small2 from @T union all
select smallid,small=small3 from @T union all
select smallid,small=small4 from @T union all
select smallid,small=small5 from @T union all
select smallid,small=small6 from @T union all
select smallid,small=small7 from @T
) t
group by smallid
) b
on a.smallid=b.smallid
order by b.small
/*
smallid smallbigid small1 small2 small3 small4 small5 small6 small7
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
3 1 2 14 13 18 20 14 17
4 2 5 12 10 11 14 17 18
2 1 5 8 11 14 12 11 11
6 3 7 8 5 20 14 18 18
5 2 6 45 42 12 17 13 19
7 3 8 12 14 13 18 19 16
8 3 10 14 11 15 16 17 19
1 1 10 11 15 16 17 12 12
*/
wangxuelid 2007-12-08
- 打赏
- 举报
思路：认为需要游标，
1：先做一个多少行循环
2：在对每个行获取列数据插入到临时表（这里需要游标处理），再order by（获取每行的排序）获取数据，利用游标循环数据，再跟新到临时表
3：select * from 临时表
1：先做一个多少行循环
2：在对每个行获取列数据插入到临时表（这里需要游标处理），再order by（获取每行的排序）获取数据，利用游标循环数据，再跟新到临时表
3：select * from 临时表
中国风 2007-12-08
- 打赏
- 举报
有空值时显示空值:
create table T(smallid int,smallbigid int, small1 int, small2 int,
small3 int, small4 int, small5 int, small6 int, small7 int)
insert T select 1, 1, 10, 11,15, 16, 17, 12,null --空值
insert T select 2, 1, 5, 8, 11, 14, 12, 11, null --空值
insert T select 3, 1, 2, 14, 13, 18, 20, 14, 17
insert T select 4, 2, 5, 12, 10, 11, 14,17, 18
insert T select 5, 2, 6, 45, 42, 12, 17, 13, 19
insert T select 6, 3, 7, 8, 5 ,20, 14, 18, 18
insert T select 7, 3, 8, 12, 14, 13, 18, 19, 16
insert T select 8, 3, 10, 14, 11, 15, 16, 17, 19
go
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7,
[MIN]=(select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp)
from
T
--truncate table T
smallid smallbigid small1 small2 small3 small4 small5 small6 small7 MIN
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 10 11 15 16 17 12 NULL 10
2 1 5 8 11 14 12 11 NULL 5
3 1 2 14 13 18 20 14 17 2
4 2 5 12 10 11 14 17 18 5
5 2 6 45 42 12 17 13 19 6
6 3 7 8 5 20 14 18 18 5
7 3 8 12 14 13 18 19 16 8
8 3 10 14 11 15 16 17 19 10
（所影响的行数为 8 行）
警告: 聚合或其它 SET 操作消除了空值。
中国风 2007-12-08
- 打赏
- 举报
create table T(smallid int,smallbigid int, small1 int, small2 int,
small3 int, small4 int, small5 int, small6 int, small7 int)
insert T select 1, 1, 10, 11,15, 16, 17, 12, 12
insert T select 2, 1, 5, 8, 11, 14, 12, 11, 11
insert T select 3, 1, 2, 14, 13, 18, 20, 14, 17
insert T select 4, 2, 5, 12, 10, 11, 14,17, 18
insert T select 5, 2, 6, 45, 42, 12, 17, 13, 19
insert T select 6, 3, 7, 8, 5 ,20, 14, 18, 18
insert T select 7, 3, 8, 12, 14, 13, 18, 19, 16
insert T select 8, 3, 10, 14, 11, 15, 16, 17, 19
go
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7,
[MIN]=(select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp)
from
T
smallid smallbigid small1 small2 small3 small4 small5 small6 small7 MIN
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 10 11 15 16 17 12 12 10
2 1 5 8 11 14 12 11 11 5
3 1 2 14 13 18 20 14 17 2
4 2 5 12 10 11 14 17 18 5
5 2 6 45 42 12 17 13 19 6
6 3 7 8 5 20 14 18 18 5
7 3 8 12 14 13 18 19 16 8
8 3 10 14 11 15 16 17 19 10
（所影响的行数为 8 行）
中国风 2007-12-08
- 打赏
- 举报
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7,
[MIN]=(select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp)
from
T
中国风 2007-12-08
- 打赏
- 举报
create table T(smallid int,smallbigid int, small1 int, small2 int,
small3 int, small4 int, small5 int, small6 int, small7 int)
select
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7,
[MIN]=select min(COL)
from
(select [COL]=small1 union all
select [COL]=small2 union all
select [COL]=small3 union all
select [COL]=small4 union all
select [COL]=small5 union all
select [COL]=small6 union all
select [COL]=small7)Tmp
from
T
