请教查询高手！关于一个从来没见过的查询类型！

[yoki]

选择表TABLE中满足下面条件
1：2002-01-02<DATE<202-1-6
2：在字段A1，A2，A3，A4，A5，A6，A7，A8中选择在10~30范围内的最小的一个值作为新表(临时表#TEMP)的第三个字段ALETER

如下表：
ID DATE A1 A2 A3 A4 A5 A6 A7 A8
1 2002-01-02 11 13 21 32 36 38 45 51
1 2002-01-03 14 16 27 30 34 39 65 71
1 2002-01-04 31 33 41 42 46 48 55 61
2 2002-01-02 15 16 21 52 56 58 65 81
2 2002-01-03 3 7 9 22 26 38 45 51
2 2002-01-04 11 23 26 32 36 48 55 91
得到临时表#TEMP
ID DATE ALETER
1 2002-01-02 11
1 2002-01-03 14
2 2002-01-02 15
2 2002-01-03 22
2 2002-01-04 11
这种类型的问题好象从来没看到过，那位高手能帮忙解决一下？一定给分。

[falaly]

晕都是些啥啊。。。
谁要把这种表给我处理我就让谁去SI(拼音)

[yelook]

declare @temp table(ID int,date datetime,aleter int)
declare MyCursor Cursor
global scroll static
for select * from table where date between '2002-01-02' and '2002-01-06'
declare @ID int,@date datetime,@a1 int,@a2 int,@a3 int,@a4 int,@a5
declare @temp int
int,@a6 int,@a7 int,@a8 int
fetch next from MyCursor into @ID,@date,@a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8
while (@@fetch_status=0)
begin
select @temp=0
if @a1<=30 and @a1>=10 and @a1>temp
select @temp=@a1

if @a2<=30 and @a2>=10 and @a2>temp
select @temp=@a2

if @a3<=30 and @a3>=10 and @a3>temp
select @temp=@a3

if @a4<=30 and @a4>=10 and @a4>temp
select @temp=@a4

if @a5<=30 and @a5>=10 and @a5>temp
select @temp=@a5

if @a6<=30 and @a6>=10 and @a6>temp
select @temp=@a6

if @a7<=30 and @a7>=10 and @a7>temp
select @temp=@a7

if @a8<=30 and @a8>=10 and @a8>temp
select @temp=@a8
insert into @temp values (@id,@date,@temp)
fetch next from MyCursor into @ID,@date,@a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8
end
close MyCursor
deallocate MyCursor

[CSDNM]

建立函数：

CREATE FUNCTION GETMIN(@X INT,@Y INT)
RETURNS INT
AS
BEGIN
DECLARE @RET INT
IF @X<@Y
BEGIN
SET @RET=@X
IF @X<10 AND @Y>=10 AND @Y<=30
SET @RET=@Y
END
ELSE
BEGIN
SET @RET=@Y
IF @Y<10 AND @X>=10 AND @X<=30
SET @RET=@X
END
RETURN @RET
END
GO

查询：
SELECT ID,[DATE],DBO.GETMIN(DBO.GETMIN(DBO.GETMIN(DBO.GETMIN(DBO.GETMIN(DBO.GETMIN(DBO.GETMIN(A1,A2),A3),A4),A5),A6),A7),A8) AS ALETER FROM TABLENAME

[yoki]

感谢两位~，不过好象有点问题，第2个执行后2002-01-04 的31也被选出来了。
对于第1个我想用单一的SELECT语句查询出来，请问还有没有更简便的方法，因为第2个如果我有30个字段、40万条记录要判断的话速度明显不合意。

[pengdali]

select id,date,0 aleter into #temp from 表 where '2002-01-02'<DATE and date<'2002-1-6'

update #temp set
aleter=cast when A1>=10 and A1<=30 then A1 else aleter end,
aleter=cast when A2>=10 and A2<=30 and A2<aleter then A2 else aleter end,
aleter=cast when A3>=10 and A3<=30 and A3<aleter then A3 else aleter end,
aleter=cast when A4>=10 and A4<=30 and A4<aleter then A4 else aleter end,
aleter=cast when A5>=10 and A5<=30 and A5<aleter then A5 else aleter end,
aleter=cast when A6>=10 and A6<=30 and A6<aleter then A6 else aleter end,
aleter=cast when A7>=10 and A7<=30 and A7<aleter then A7 else aleter end,
aleter=cast when A8>=10 and A8<=30 and A8<aleter then A8 else aleter end

select * from #temp
drop table #temp

[pengdali]

select *,0 aleter into #temp from 表 where '2002-01-02'<DATE and date<'2002-1-6'

update #temp set
aleter=cast when A1>=10 and A1<=30 then A1 else aleter end,
aleter=cast when A2>=10 and A2<=30 and A2<aleter then A2 else aleter end,
aleter=cast when A3>=10 and A3<=30 and A3<aleter then A3 else aleter end,
aleter=cast when A4>=10 and A4<=30 and A4<aleter then A4 else aleter end,
aleter=cast when A5>=10 and A5<=30 and A5<aleter then A5 else aleter end,
aleter=cast when A6>=10 and A6<=30 and A6<aleter then A6 else aleter end,
aleter=cast when A7>=10 and A7<=30 and A7<aleter then A7 else aleter end,
aleter=cast when A8>=10 and A8<=30 and A8<aleter then A8 else aleter end

select id,date,aleter from #temp
drop table #temp

[yoki]

大力，你这样写的话A1，A2，A3......从何而来呢？

[pengdali]

select *,0 aleter into #temp from 表 where '2002-01-02'<DATE and date<'2002-1-6'

[yoki]

无效！因为在结果列的列表中多次出现列名 'aleter'。

[samuelpan]

哈

[titanicliu]

select Date,min((case when (case when min1<min2 then min1 else min2 end)<min3 then (case when min1<min2 then min1 else min2 end) else min3 end ))
from
(select Date,
(case when (case when a1<a2 then a1 else a2 end)<a3 then (case when a1<a2 then a1 else a2 end) else a3 end ) min1,
(case when (case when a1<a5 then a4 else a5 end)<a6 then (case when a4<a5 then a4 else a5 end) else a6 end ) min2
,(case when a7<a8 then a7 else a8 end) min3
from TABLE) t1
group by Date

[j9988]

select ID,DATE,min(A1) from (
select ID,DATE,A1 from table
union all
select ID,DATE,A2 from table
union all
....
select ID,DATE,A8 from table
) A
where date between 2002-01-02 and 202-1-5
and A1 between 10 and 30
group by ID,DATE
order by ID,DATE

[yoki]

很感谢大家的积极参与，不过我觉得都缺乏效率，请问一下还有没有更高效的方法，因为我要处理的数据量非常大。最好别用光标。我打算再放一天，明天再给大家加分~！

[oldsky]

up

[samuelpan]

看来你没有去调试我的方法了。悲......
不过其中有小小的语法错误是我的过失。

我认为：
还是用我的方法，肯定速度快，你可以将我的连接表通过一个固定表来完成，做一个存储过程，

1、建立table(id,date,a)
2、将符合条件的id,date,a1放入table
3、将符合条件的id,date,a2添加到table
4、类推,一直将a8的数据放入table
5、select id,date2,min(a1) as ALETER into #TEMP
from table group by id,date2
6、删除表table

[yoki]

TO：samuelpan(欧乐)
我明白你的意思，你是通过改变表的结构来实现对吧？
其实我法这个贴子的本意是想看看到底采取那种结构查询更新效率更高。所以你这种方法本质上还是对另外一种结构操作。所以没有可比性，不过还是要谢谢你的参与。

[ysjxjf]

點網頁下面的管理﹐ Then you will see !
do not forget me !!!

[yoki]

谢谢大家了

[yoki]

怎么给分啊，请告诉我

[yoki]

结贴