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语句得到我要的结果,不要借助函数,临时表来处理!!
解决方案 »
- 视图中创建INSTEAD OF UPDATE触发器的小问题
- 在发布订阅中什么时候才创建那些替换INSERT UPDATE DELETE命令的存储过程呀??
- 备份数据库时如何判断覆盖还是追加?在线等,在线给分
- 【求教】用navicat for mysql 的console 输入数据第一条不乱码,后面的乱码
- 一个简单建库的问题,什么错误??
- 关于联合查询出的数据有重叠数据问题
- 求教一個寫法較簡煉的SQL
- 完整备份后的第一次日志备份很大
- 愿出高分, 请教一条SQL语句:
- sql6.5问题。
- 如何查询类型image是空值
- 使用ssis 将 access2000 导入SQL2005时候。 选择哪个数据源??
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
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
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 goselect
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 行)
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 goselect
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 操作消除了空值。
1:先做一个多少行循环
2:在对每个行获取列数据插入到临时表(这里需要游标处理),再order by(获取每行的排序)获取数据,利用游标循环数据,再跟新到临时表
3:select * from 临时表
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,19select 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
*/
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相等时排序依据是什么?
--只排序不用显示:
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 goselect
smallid,smallbigid,small1,small2,small3,small4,small5,small6,small7from
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 goselect
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 行)
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,19select 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,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
*/
NULL,表示"无",既不作最大,也不作最小
遇到字段为NULL时就不参与比较,后面的顶上.