-----------请教一统计的T-SQL----

Question

--数据表
stcd dt avz avzrcd
------------------------------------------------------------------------------------
1234 2010-05-06 10:17:44.717 57.638 axy1
1235 2010-05-06 10:17:44.717 164.574 axy2
1236 2010-05-06 10:17:44.717 41.542 axy3
1237 2010-05-06 10:17:44.717 36.436 axy4
1238 2010-05-06 10:17:44.717 428.527 axy6
1234 2010-05-06 08:20:55.827 16.654 axy7
1234 2010-05-06 09:20:55.827 83.281 axya
1235 2010-05-06 08:20:55.827 35.460 axyb
1235 2010-05-06 09:20:55.827 182.400 axyc
1236 2010-05-06 08:20:55.827 182.881 axyd
1236 2010-05-06 09:20:55.827 20.920 axye
1237 2010-05-06 08:20:55.827 160.406 axyf
1237 2010-05-06 09:20:55.827 6.982 axyg
1238 2010-05-06 08:20:55.827 328.605 axyh
1238 2010-05-06 09:20:55.827 55.712 axyi--结果
stcd dt total max max-avzrcd min min-avzrcd avg
---------------------------------------------------------------------------------------------------------------
1234 2010-05-06 157.573 83.281 axya 16.654 axy7 52.524333 
1235 2010-05-06 382.434 182.400 axyc 35.460 axyb 127.478000
1236 2010-05-06 245.343 182.881 axyd 20.920 axye 81.781000
1237 2010-05-06 203.824 160.406 axyf 6.982 axyg 67.941333
1238 2010-05-06 812.844 428.527 axy6 55.712 axyi 270.948000以上均按stcd统计
total：是SUM(avz) 的和
max：即MAX(avz)
max-avzrcd：与同一个stcd中最大avz对应的一个avzrcd
min/min-avzrcd：同理
avg：即AVG(avz)
--目前写到了这一步：
select
  stcd,
  --case CONVERT(VARCHAR(10),dt,120) 'DT',
  SUM(avz) [tol],
  max(avz) [max],
  min(avz) [min],
  avg(avz) [avg]
from 表 where datediff(d,dt,'2010-5-6')=0 group by stcd

Answer 1

max-avzrcd：与同一个stcd中最大avz对应的一个avzrcd
没懂

Answer 2

avzrcd 不是字符型的吗
不能运算啊

Answer 3

select T.*,
(select avzrcd from #T1 where avz=t.[max]) as [max-avzrcd],
(select avzrcd from #T1 where avz=t.[min]) as [min-avzrcd] 
 from 
(
select stcd,
  convert(varchar(10),dt,120) as dt, 
  SUM(avz) [tol],
  max(avz) [max],
  min(avz) [min],
  avg(avz) [avg]
from #T1 t where datediff(d,dt,'2010-5-6')=0 group by stcd,convert(varchar(10),dt,120)
) tstcd        dt         tol                                     max                                     min                                     avg                                     max-avzrcd min-avzrcd
----------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------- ----------
1234        2010-05-06 157.573                                 83.281                                  16.654                                  52.524333                               axya       axy7
1235        2010-05-06 382.434                                 182.400                                 35.460                                  127.478000                              axyc       axyb
1236        2010-05-06 245.343                                 182.881                                 20.920                                  81.781000                               axyd       axye
1237        2010-05-06 203.824                                 160.406                                 6.982                                   67.941333                               axyf       axyg
1238        2010-05-06 812.844                                 428.527                                 55.712                                  270.948000                              axy6       axyi(5 行受影响)

Answer 4

--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (stcd int,dt datetime,avz numeric(6,3),avzrcd varchar(4))
insert into #T1
select 1234,'2010-05-06 10:17:44.717',57.638,'axy1' union all
select 1235,'2010-05-06 10:17:44.717',164.574,'axy2' union all
select 1236,'2010-05-06 10:17:44.717',41.542,'axy3' union all
select 1237,'2010-05-06 10:17:44.717',36.436,'axy4' union all
select 1238,'2010-05-06 10:17:44.717',428.527,'axy6' union all
select 1234,'2010-05-06 08:20:55.827',16.654,'axy7' union all
select 1234,'2010-05-06 09:20:55.827',83.281,'axya' union all
select 1235,'2010-05-06 08:20:55.827',35.460,'axyb' union all
select 1235,'2010-05-06 09:20:55.827',182.400,'axyc' union all
select 1236,'2010-05-06 08:20:55.827',182.881,'axyd' union all
select 1236,'2010-05-06 09:20:55.827',20.920,'axye' union all
select 1237,'2010-05-06 08:20:55.827',160.406,'axyf' union all
select 1237,'2010-05-06 09:20:55.827',6.982,'axyg' union all
select 1238,'2010-05-06 08:20:55.827',328.605,'axyh' union all
select 1238,'2010-05-06 09:20:55.827',55.712,'axyi'select T.stcd,
T.[tol],
T.[max],
[max-avzrcd]=(select avzrcd from #T1 where avz=t.[max]),
T.[min],
[min-avzrcd]=(select avzrcd from #T1 where avz=t.[min]), 
T.[avg]
from 
(
select stcd,convert(varchar(10),dt,120) as dt, SUM(avz) [tol],max(avz) [max],min(avz) [min],avg(avz) [avg]
from #T1 t 
where datediff(d,dt,'2010-5-6')=0 
group by stcd,convert(varchar(10),dt,120)
) tstcd        tol                                     max                                     max-avzrcd min                                     min-avzrcd avg
----------- --------------------------------------- --------------------------------------- ---------- --------------------------------------- ---------- ---------------------------------------
1234        157.573                                 83.281                                  axya       16.654                                  axy7       52.524333
1235        382.434                                 182.400                                 axyc       35.460                                  axyb       127.478000
1236        245.343                                 182.881                                 axyd       20.920                                  axye       81.781000
1237        203.824                                 160.406                                 axyf       6.982                                   axyg       67.941333
1238        812.844                                 428.527                                 axy6       55.712                                  axyi       270.948000(5 行受影响)

Answer 5

create table #tb (stcd int,dt      datetime,avz  float,avzrcd varchar(10))
insert #tb select 1234    ,'2010-05-06 10:17:44.717',    57.638    ,'axy1'
insert #tb select 1235    ,'2010-05-06 10:17:44.717',    164.574    ,'axy2'
insert #tb select 1236    ,'2010-05-06 10:17:44.717',    41.542    ,'axy3'
insert #tb select 1237    ,'2010-05-06 10:17:44.717',    36.436    ,'axy4'
insert #tb select 1238    ,'2010-05-06 10:17:44.717',    428.527    ,'axy6'
insert #tb select 1234    ,'2010-05-06 08:20:55.827',    16.654    ,'axy7'
insert #tb select 1234    ,'2010-05-06 09:20:55.827',    83.281    ,'axya'
insert #tb select 1235    ,'2010-05-06 08:20:55.827',    35.460    ,'axyb'
insert #tb select 1235    ,'2010-05-06 09:20:55.827',    182.400    ,'axyc'
insert #tb select 1236    ,'2010-05-06 08:20:55.827',    182.881    ,'axyd'
insert #tb select 1236    ,'2010-05-06 09:20:55.827',    20.920    ,'axye'
insert #tb select 1237    ,'2010-05-06 08:20:55.827',    160.406    ,'axyf'
insert #tb select 1237    ,'2010-05-06 09:20:55.827',    6.982    ,'axyg'
insert #tb select 1238    ,'2010-05-06 08:20:55.827',    328.605    ,'axyh'
insert #tb select 1238    ,'2010-05-06 09:20:55.827',    55.712    ,'axyi'with cte as(
select *,sum(avz)over(partition by stcd) as total,max(avz) over(partition by stcd) [max],min(avz) over(partition by stcd) as [min], 
avg(avz) over(partition by stcd) as [avg]
from #tb a
)select stcd,convert(char(10),dt,120) as dt,
total,
[max],
max(case when avz=[max] then avzrcd end) as [max-avzrcd],
[min],
max(case when avz=[min] then avzrcd end) as [min-avzrcd],
cast([avg] as decimal(19,6)) as [avg]from cte a 
where avz=[max] or avz=[min]
group by  stcd,convert(char(10),dt,120) ,total,[max],[min],[avg]stcd        dt         total                  max                    max-avzrcd min                    min-avzrcd avg
----------- ---------- ---------------------- ---------------------- ---------- ---------------------- ---------- ---------------------------------------
1234        2010-05-06 157.573                83.281                 axya       16.654                 axy7       52.524333
1235        2010-05-06 382.434                182.4                  axyc       35.46                  axyb       127.478000
1236        2010-05-06 245.343                182.881                axyd       20.92                  axye       81.781000
1237        2010-05-06 203.824                160.406                axyf       6.982                  axyg       67.941333
1238        2010-05-06 812.844                428.527                axy6       55.712                 axyi       270.948000
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)