求一条查询语句

[liushiying18]

要求下面语句中表#tmp1的endtime字段有NULL时候返回NULL，没有返回max(endtime)
要怎么样做啊(sql2005)

SELECT UseNo,ctno,skno,tno,chno,sum(ErpQty) as ErpQty,min(begintime) as begintime,max(endtime) as endtime into #tmpOut1 FROM #tmp1 GROUP BY UseNo,ctno,skno,tno,chno

[發糞塗牆]

SELECT  UseNo ,
        ctno ,
        skno ,
        tno ,
        chno ,
        SUM(ErpQty) AS ErpQty ,
        MIN(begintime) AS begintime ,
        CASE WHEN endtime IS NULL THEN NULL
             ELSE MAX(endtime)
        END AS endtime
INTO    #tmpOut1
FROM    #tmp1
GROUP BY UseNo ,
        ctno ,
        skno ,
        tno ,
        chno

[liushiying18]

不行呀，endtime应该使用聚合函数的

[發糞塗牆]

这样？

 SELECT UseNo ,
        ctno ,
        skno ,
        tno ,
        chno ,
        SUM(ErpQty) AS ErpQty ,
        MIN(begintime) AS begintime ,
        MAX(CASE WHEN endtime IS NULL THEN NULL
                 ELSE endtime
            END) AS endtime
 INTO   #tmpOut1
 FROM   #tmp1
 GROUP BY UseNo ,
        ctno ,
        skno ,
        tno ,
        chno

[-Tracy-McGrady-]

case when then when then else end

[以学习为目的]

select endtime=max(case when endtime is NULL then NULL else endtime) from  #tmp1

[yiyishuitian]

给你一事例看一下吧，应该是你想要的。

with test as 
(
select 'a' name,1 as number union 
select 'b' name,2 as number union 
select 'c' name,3 as number union 
select 'd' name,4 as number union 
select 'a' name,5 as number union 
select 'b' name,6 as number union 
select 'c' name,7 as number union 
select 'd' name,null as number  
)

select name
, case when MAX(isnull(number,10000))=10000 then null else MAX(number) end as number
from Test
group by name

name number a 5 b 6 c 7 d NULL 其中10000是我随便附的一个比number列中任何一个值都大的值。

[我腫了]

SELECT 
		UseNo
		,ctno
		,skno
		,tno
		,chno
		,sum(ErpQty)	as ErpQty
		,min(begintime) as begintime
		,Case when Exists(Select 1 from #tmp1 As x
							Where x.UseNo=a.UseNo
								And x.ctno=a.ctno
								And x.skno=a.skno
								And x.tno=a.tno
								And x.chno=a.chno
								And x.endtime Is null
						)
					Then Null 
				Else max(endtime)
			End as endtime 
		into #tmpOut1 
	FROM #tmp1 As a
	GROUP BY 
		UseNo
		,ctno
		,skno
		,tno
		,chno

[-晴天]

;with test as ( select 'a' name,1 as number union select 'b' name,2 as number union select 'c' name,3 as number union select 'd' name,4 as number union select 'a' name,5 as number union select 'b' name,6 as number union select 'c' name,7 as number union select 'd' name,null as number ) select name,max(number) from test a where not exists(select 1 from test where name=a.name and number is null) group by name union all select distinct name,null from test where number is null /* name ---- ----------- a 5 b 6 c 7 d NULL (4 行受影响) */