表名为Event_Record
group_no lift_no event_flag event_value
1 1 36 20
1 2 36 30
1 3 36 26
2 4 36 50
2 5 36 60
2 6 36 56
1 1 37 120
1 2 37 130
1 3 37 126
2 4 37 150
2 5 37 160
2 6 37 156
1 1 37 80
1 2 37 90
1 3 37 86
2 4 37 70
2 5 37 80
2 6 37 76
1 1 36 60
1 2 36 50
1 3 36 42
2 4 36 36
2 5 36 38
2 6 36 40
做报表时要统计电梯开门(36)和并门(37)时的最小值,要得到如下数据:
lift_no dooropen_min doorclose_min
1 1 20 80
1 2 30 90
1 3 26 86
2 4 36 70
2 5 38 80
2 6 40 76
group_no lift_no event_flag event_value
1 1 36 20
1 2 36 30
1 3 36 26
2 4 36 50
2 5 36 60
2 6 36 56
1 1 37 120
1 2 37 130
1 3 37 126
2 4 37 150
2 5 37 160
2 6 37 156
1 1 37 80
1 2 37 90
1 3 37 86
2 4 37 70
2 5 37 80
2 6 37 76
1 1 36 60
1 2 36 50
1 3 36 42
2 4 36 36
2 5 36 38
2 6 36 40
做报表时要统计电梯开门(36)和并门(37)时的最小值,要得到如下数据:
lift_no dooropen_min doorclose_min
1 1 20 80
1 2 30 90
1 3 26 86
2 4 36 70
2 5 38 80
2 6 40 76
,lift_no,
min(event_value) as 'dooropen_min '
,min(event_value) as 'doorclose_min'
from Event_Record group by group_no,lift_no
*
from
tb t
where
not exists
(select 1 from tb where event_flag=t.event_flag and event_value <t.event_value )
event_value =(SELECT MIN(event_value ) FROM TB WHERE event_flag=T.event_flag)怎么老看不到呀
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-17 11:36:02
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([group_no] int,[lift_no] int,[event_flag] int,[event_value] int)
insert [tb]
select 1,1,36,20 union all
select 1,2,36,30 union all
select 1,3,36,26 union all
select 2,4,36,50 union all
select 2,5,36,60 union all
select 2,6,36,56 union all
select 1,1,37,120 union all
select 1,2,37,130 union all
select 1,3,37,126 union all
select 2,4,37,150 union all
select 2,5,37,160 union all
select 2,6,37,156 union all
select 1,1,37,80 union all
select 1,2,37,90 union all
select 1,3,37,86 union all
select 2,4,37,70 union all
select 2,5,37,80 union all
select 2,6,37,76 union all
select 1,1,36,60 union all
select 1,2,36,50 union all
select 1,3,36,42 union all
select 2,4,36,36 union all
select 2,5,36,38 union all
select 2,6,36,40
--------------开始查询--------------------------
select
group_no,lift_no,
min(event_value) as 'dooropen_min '
from
tb
group by
group_no,lift_no
----------------结果----------------------------
/*group_no lift_no dooropen_min
----------- ----------- -------------
1 1 20
1 2 30
1 3 26
2 4 36
2 5 38
2 6 40(所影响的行数为 6 行)
*/
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-17 11:26:06
if not object_id('[tb]') is null
drop table [tb]
Go
Create table [tb]([group_no] int,[lift_no] int,[event_flag] int,[event_value] int)
Insert [tb]
select 1,1,36,20 union all
select 1,2,36,30 union all
select 1,3,36,26 union all
select 2,4,36,50 union all
select 2,5,36,60 union all
select 2,6,36,56 union all
select 1,1,37,120 union all
select 1,2,37,130 union all
select 1,3,37,126 union all
select 2,4,37,150 union all
select 2,5,37,160 union all
select 2,6,37,156 union all
select 1,1,37,80 union all
select 1,2,37,90 union all
select 1,3,37,86 union all
select 2,4,37,70 union all
select 2,5,37,80 union all
select 2,6,37,76 union all
select 1,1,36,60 union all
select 1,2,36,50 union all
select 1,3,36,42 union all
select 2,4,36,36 union all
select 2,5,36,38 union all
select 2,6,36,40
Go
select
group_no,
lift_no,
min(case when [event_flag]=36 then [event_value] else null end)dooropen_min ,
min(case when [event_flag]=37 then [event_value] else null end)doorclose_min
from [tb] group by [lift_no],group_no
/*
group_no lift_no dooropen_min doorclose_min
----------- ----------- ------------ -------------
1 1 20 80
1 2 30 90
1 3 26 86
2 4 36 70
2 5 38 80
2 6 40 76
警告: 彙總或其他 SET 作業已刪除 Null 值。(6 個資料列受到影響)*/
--无耻地抄袭小F的数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([group_no] int,[lift_no] int,[event_flag] int,[event_value] int)
insert [tb]
select 1,1,36,20 union all
select 1,2,36,30 union all
select 1,3,36,26 union all
select 2,4,36,50 union all
select 2,5,36,60 union all
select 2,6,36,56 union all
select 1,1,37,120 union all
select 1,2,37,130 union all
select 1,3,37,126 union all
select 2,4,37,150 union all
select 2,5,37,160 union all
select 2,6,37,156 union all
select 1,1,37,80 union all
select 1,2,37,90 union all
select 1,3,37,86 union all
select 2,4,37,70 union all
select 2,5,37,80 union all
select 2,6,37,76 union all
select 1,1,36,60 union all
select 1,2,36,50 union all
select 1,3,36,42 union all
select 2,4,36,36 union all
select 2,5,36,38 union all
select 2,6,36,40SELECT * FROM TB T1
WHERE NOT EXISTS(
SELECT 1 FROM TB T2 WHERE
T2.GROUP_NO=T1.GROUP_NO AND T2.LIFT_NO=T1.LIFT_NO
AND T2.EVENT_FLAG<T1.EVENT_FLAG AND T2.EVENT_VALUE<T1.EVENT_VALUE)
AND NOT EXISTS(
SELECT 1 FROM TB T2 WHERE T2.GROUP_NO=T1.GROUP_NO AND T2.LIFT_NO=T1.LIFT_NO
AND T2.EVENT_FLAG=T1.EVENT_FLAG AND T2.EVENT_VALUE<T1.EVENT_VALUE)
/*
1 1 36 20
1 2 36 30
1 3 36 26
2 4 36 36
2 5 36 38
2 6 36 40
*/