34,588
社区成员
发帖
与我相关
我的任务
分享-----------请教一统计的T-SQL----
--数据表
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
...全文
请发表友善的回复…
发表回复
zpcoder 2010-05-13
- 打赏
- 举报
强，牛X 结帖
东那个升 2010-05-13
- 打赏
- 举报
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 行受影响)
youyou2404 2010-05-13
- 打赏
- 举报
--> 测试数据: #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)
) t
stcd 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 行受影响)youyou2404 2010-05-13
- 打赏
- 举报
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)
) t
stcd 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 行受影响)
sql_sf 2010-05-13
- 打赏
- 举报
avzrcd 不是字符型的吗
不能运算啊
不能运算啊
东那个升 2010-05-13
- 打赏
- 举报
max-avzrcd：与同一个stcd中最大avz对应的一个avzrcd
没懂
没懂
