当分组字段相同,flow为空的时候,就出问题了.
create table tb (id INT,active VARCHAR(1),[group] VARCHAR(1),region VARCHAR(1),flow INT,name VARCHAR(2),msg VARCHAR(2))
INSERT INTO tb
SELECT 22,'A','B','C',5,'aa','s' UNION ALL
SELECT 22,'A','B','C',null,'sd','t' UNION ALL
SELECT 22,'A','B','C',2,'ts','mp' UNION ALL
SELECT 28,'A','B','C',3,'xe','g' UNION ALL
SELECT 28,'A','B','C',7,'fm','r' UNION ALL
SELECT 29,'A','B','C',null,null,null
select * from tb t where flow is not null and flow = (select max(flow) from tb where flow is not null and id = t.id and active = t.active and [group] = t.[group] and region = t.region)
union all
select * from tb where flow is null
order by iddrop table tb正确的结果应该是:22 A B C 5 aa s
28 A B C 7 fm r
29 A B C NULL NULL NULL
create table tb (id INT,active VARCHAR(1),[group] VARCHAR(1),region VARCHAR(1),flow INT,name VARCHAR(2),msg VARCHAR(2))
INSERT INTO tb
SELECT 22,'A','B','C',5,'aa','s' UNION ALL
SELECT 22,'A','B','C',null,'sd','t' UNION ALL
SELECT 22,'A','B','C',2,'ts','mp' UNION ALL
SELECT 28,'A','B','C',3,'xe','g' UNION ALL
SELECT 28,'A','B','C',7,'fm','r' UNION ALL
SELECT 29,'A','B','C',null,null,null
select * from tb t where flow is not null and flow = (select max(flow) from tb where flow is not null and id = t.id and active = t.active and [group] = t.[group] and region = t.region)
union all
select * from tb where flow is null
order by iddrop table tb正确的结果应该是:22 A B C 5 aa s
28 A B C 7 fm r
29 A B C NULL NULL NULL
INSERT INTO tb
SELECT 22,'A','B','C',5,'aa','s' UNION ALL
SELECT 22,'A','B','C',null,'sd','t' UNION ALL
SELECT 22,'A','B','C',2,'ts','mp' UNION ALL
SELECT 28,'A','B','C',3,'xe','g' UNION ALL
SELECT 28,'A','B','C',7,'fm','r' UNION ALL
SELECT 29,'A','B','C',null,null,nullselect * from tb t where flow is not null and flow = (select max(flow) from tb where flow is not null and id = t.id and active = t.active and [group] = t.[group] and region = t.region)
union all
select * from tb m where flow is null and not exists (select 1 from (select * from tb t where flow is not null and flow = (select max(flow) from tb where flow is not null and id = t.id and active = t.active and [group] = t.[group] and region = t.region)) n where n.active = m.active and n.active = m.active and n.region = m.region and n.id = m.id)
order by iddrop table tb/*
id active group region flow name msg
----------- ------ ----- ------ ----------- ---- ----
22 A B C 5 aa s
28 A B C 7 fm r
29 A B C NULL NULL NULL(所影响的行数为 3 行)
*/
from tb t
where not exists(select
*
from tb
where id = t.id
and active = t.active
and [group] = t.[group]
and region = t.region
and flow>isnull(t.flow,0)
)/**
id active group region flow name msg
----------- ------ ----- ------ ----------- ---- ----
22 A B C 5 aa s
28 A B C 7 fm r
29 A B C NULL NULL NULL(所影响的行数为 3 行)
**/
select * from tb a where 1 >(select count(1) from tb where id = a.id and isnull(flow,0) > isnull(a.flow,0) )
id active group region flow name msg
----------- ------ ----- ------ ----------- ---- ----
22 A B C 5 aa s
28 A B C 7 fm r
29 A B C NULL NULL NULL(3 row(s) affected)
谢谢!
形式的方法啊? dawugui专家曾经写出5种方法,我想用第4种方法,多谢!
INSERT INTO tb
SELECT 22,'A','B','C',5,'aa','s' UNION ALL
SELECT 22,'A','B','C',null,'sd','t' UNION ALL
SELECT 22,'A','B','C',2,'ts','mp' UNION ALL
SELECT 28,'A','B','C',3,'xe','g' UNION ALL
SELECT 28,'A','B','C',7,'fm','r' UNION ALL
SELECT 29,'A','B','C',null,null,nullselect * from tb m where flow is null and not exists (select 1 from (select * from tb t where flow is not null and flow = (select max(flow) from tb where flow is not null and id = t.id and active = t.active and [group] = t.[group] and region = t.region)) n where n.active = m.active and n.active = m.active and n.region = m.region and n.id = m.id)
order by iddrop table tb
(SELECT A.ID,MAX(A.FLOW) AS FLOW,A.ACTIVE,A.[GROUP],A.REGION FROM TB A GROUP BY A.ID,A.ACTIVE,A.[GROUP],A.REGION ) A LEFT JOIN TB B ON A.ID=B.ID AND A.FLOW=B.FLOW