求高人指点数据分组问题

Question

有如下数据:
No     Remain     ModelNo       
1      12         Null
1      1          Null
2      10         Null
2      -1         Mo1
2      -4         Mo8
2      21         Null
说明:即Remain>=0则modelNo=Null,Remain<0则ModelNo为一型号
要实现结果如下:
No     ModelNo 
1      Null
2      Mo1
2      Mo8
我自己的思路(效率太低):
1.先将Group时用Having去掉ModelNo不为Null的No(如上即去掉No=2的)剩下ModelNo全Null的Group成一条
2.Union上一步去掉的,但只要它ModelNo不为 Null的,为Null不考滤
这个太笨,求高人指教

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (No INT,Remain INT,ModelNo VARCHAR(3))
INSERT INTO @T
SELECT 1,12,null UNION ALL
SELECT 1,1,null UNION ALL
SELECT 2,10,null UNION ALL
SELECT 2,-1,'Mo1' UNION ALL
SELECT 2,-4,'Mo8' UNION ALL
SELECT 2,21,null--SQL查询如下:SELECT
    CASE WHEN Remain>=0 AND ModelNO IS NULL 
             AND Remain<0 AND ModelNo IS NOT NULL
           THEN 'Null'
         ELSE ModelNO
    END AS ModelNO,
    MIN(NO) AS NO
FROM @T
GROUP BY 
    CASE WHEN Remain>=0 AND ModelNO IS NULL 
             AND Remain<0 AND ModelNo IS NOT NULL
           THEN 'Null'
         ELSE ModelNO
    END/*
ModelNO NO
------- -----------
NULL    1
Mo1     2
Mo8     2(3 行受影响)*/

Answer 2

有人回答,怎么看不到?狗日的CSDN

Answer 3

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([No] int,[Remain] int,[ModelNo] varchar(3))
insert [tb]
select 1,12,null union all
select 1,1,null union all
select 2,10,null union all
select 2,-1,'Mo1' union all
select 2,-4,'Mo8' union all
select 2,21,null
 
---查询---
select 
  distinct
  t.No,
  case when t.Remain<0 then t.ModelNo end as ModelNo 
from tb t
where not exists(select * from tb where no=a.no and remain<0 and a.remain>0)---结果---
No          ModelNo 
----------- ------- 
1           NULL
2           Mo1
2           Mo8（所影响的行数为 3 行）
这样？

Answer 4

declare @t table(No int,Remain int,ModelNo varchar(10))
insert into @t select 1,12,Null 
insert into @t select 1, 1,Null 
insert into @t select 2,10,Null 
insert into @t select 2,-1,'Mo1' 
insert into @t select 2,-4,'Mo8' 
insert into @t select 2,21,Nullselect
    distinct t.No,t.ModelNo
from
    @t t
where
    not exists(select 1 from @t where No=t.No and ModelNo is not null)
    or
    t.Remain<0/*
No          ModelNo    
----------- ---------- 
1           NULL
2           Mo1
2           Mo8
*/

Answer 5

好像不能满足,如果把测试数据改为如下
SELECT 1,12,null UNION ALL
SELECT 1,1,null UNION ALL
SELECT 2,10,null UNION ALL
SELECT 2,-1,'Mo1' UNION ALL
SELECT 2,-4,'Mo8' UNION ALL
SELECT 3,-1,Null UNION ALL
SELECT 3,-4,null UNION ALL
SELECT 2,21,null
就不会出现NO=3的了
而我要的结果是
1   Null
2   Mo1
2   Mo2
3   Null

Answer 6

create TABLE #T(No INT,Remain INT,ModelNo VARCHAR(3))
INSERT INTO #T
SELECT 1,12,null UNION ALL
SELECT 1,1,null UNION ALL
SELECT 2,10,null UNION ALL
SELECT 2,-1,'Mo1' UNION ALL
SELECT 2,-4,'Mo8' UNION ALL
SELECT 2,21,nullselect distinct NO,
(case when Remain>0 then null else ModelNo end) 'ModelNo' from #T group by NO,(case when Remain>0 then null else ModelNo end)
NO          ModelNo
----------- -------
1           NULL
2           NULL
2           Mo1
2           Mo8

Answer 7

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([No] int,[Remain] int,[ModelNo] varchar(3))
insert [tb]
SELECT 1,12,null UNION ALL 
SELECT 1,1,null UNION ALL 
SELECT 2,10,null UNION ALL 
SELECT 2,-1,'Mo1' UNION ALL 
SELECT 2,-4,'Mo8' UNION ALL 
SELECT 3,-1,Null UNION ALL 
SELECT 3,-4,null UNION ALL 
SELECT 2,21,null 
---查询---select 
  distinct
  t.No,
  case when t.Remain<0 then t.ModelNo end as ModelNo 
from tb t
where not exists(select * from tb where no=t.no and remain<0 and t.remain>0)---结果---
No          ModelNo 
----------- ------- 
1           NULL
2           Mo1
2           Mo8
3           NULL（所影响的行数为 4 行）

Answer 8

我理解的是当大于的时候
就显示null
小于的时候就是型号

Answer 9

declare @t table(No int,Remain int,ModelNo varchar(10))
insert into @t 
SELECT 1,12,null UNION ALL 
SELECT 1,1,null UNION ALL 
SELECT 2,10,null UNION ALL 
SELECT 2,-1,'Mo1' UNION ALL 
SELECT 2,-4,'Mo8' UNION ALL 
SELECT 3,-1,Null UNION ALL 
SELECT 3,-4,null UNION ALL 
SELECT 2,21,null select
    distinct t.No,t.ModelNo
from
    @t t
where
    not exists(select 1 from @t where No=t.No and ModelNo is not null)
    or
    t.Remain<0/*No          ModelNo    
----------- ---------- 
1           NULL
2           Mo1
2           Mo8
3           NULL
*/

Answer 10

select distinct NO,
(case when Remain>0 then null else ModelNo end) 'ModelNo' from #T group by NO,(case when Remain>0 then null else ModelNo end)NO          ModelNo
----------- -------
1           NULL
2           NULL
2           Mo1
2           Mo8(4 行受影响)

Answer 11

create table #T1(No int,Remain int,ModelNo varchar(10))
insert into #T1
SELECT 1,12,null UNION ALL 
SELECT 1,1,null UNION ALL 
SELECT 2,10,null UNION ALL 
SELECT 2,-1,'Mo1' UNION ALL 
SELECT 2,-4,'Mo8' UNION ALL 
SELECT 3,-1,Null UNION ALL 
SELECT 3,-4,null UNION ALL 
SELECT 2,21,null select distinct NO,
(case when Remain>0 then null else ModelNo end) 'ModelNo' from #T1 group by NO,(case when Remain>0 then null else ModelNo end)
NO          ModelNo
----------- ----------
1           NULL
2           NULL
2           Mo1
2           Mo8
3           NULL(5 行受影响)

Answer 12

你的多了一行
2    Null

Answer 13

那就用
钻钻的
select
    distinct t.No,t.ModelNo
from
    @t t
where
    not exists(select 1 from @t where No=t.No and ModelNo is not null)
    or
    t.Remain<0

Answer 14

create index IDX_NO on tb(No,ModelNo)
select 
    distinct t.No,t.ModelNo 
from 
    tb t 
where 
    not exists(select 1 from tb where No=t.No and ModelNo is not null) 
    or 
    t.Remain <0 
这样看看会不会快一点

Answer 15

估计可优化的余地不大，在No字段上建个索引试试：
select t.No,t.ModelNo from @t t where not exists(select 1 from @t where No=t.No and ModelNo is not null)
union
select No,ModelNo from @t where Remain<0