求一个SQL 语句，在线等

[JB-Zhang]

有一个表如下
字段: id set_id bind_type qty
值: 1 s1 N 12
2 s2 N 10
3 s3 N 0
4 s4 M 34
5 S4 M 0
6 S4 M 0

当字段bind_type为N时,则只读取字段qty为非零的值,当bind_type字段为M时刚把set_id字段有相同值的加总即sum(qty),如果该值为非零,则读取这个set_id的这几记录,一般情况下同一个set_id,且bind_type为M的字段只有第一条的值为非零.
我想得到的结果如下:
字段: id set_id bind_type qty
值: 1 s1 N 12
2 s2 N 10
4 s4 M 34
5 S4 M 0
6 S4 M 0

[jhwcd]

[Quote=引用 14 楼 yinqi025 的回复:]
真快呀.....你们抢得
[/Quote]

[JB-Zhang]

高手不少,谢了,放分!

[wzy_love_sly]

if object_id('tb') is not null
drop table tb
go
create table tb(id int,set_id varchar(20),bind_type varchar(20),qty int)
insert into tb select 1,'s1','N',12
insert into tb select 2,'s2','N',10
insert into tb select 3,'s3','N',0
insert into tb select 4,'S4','M',34
insert into tb select 5,'S4','M',0
insert into tb select 6,'S4','M',0

select * from tb t
where not exists(
select 1 from tb where set_id=t.set_id group by set_id having sum(qty)=0
)

[wzy_love_sly]

id set_id bind_type qty
1 s1 N 12
2 s2 N 10
4 S4 M 34
5 S4 M 0
6 S4 M 0

[nzperfect]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,set_id varchar(2),bind_type varchar(1),qty int)
insert into #T
select 1,'s1','N',12 union all
select 2,'s2','N',10 union all
select 3,'s3','N',0 union all
select 4,'s4','M',34 union all
select 5,'S4','M',0 union all
select 6,'S4','M',0

select * from (
    select id,set_id,bind_type,
    case when  bind_type ='N' and qty <> '0' then qty
    when bind_type ='N' and qty = '0' then null
    when bind_type='M'  then
        case when id=(select min(id) from #t where set_id=a.set_id) then (select sum(qty) from #t where set_id=a.set_id)
        else 0 
        end
    end as qty
    from #T a
) a where qty is not null
/*
------------------
1	s1	N	12
2	s2	N	10
4	s4	M	34
5	S4	M	0
6	S4	M	0
*/

[nzperfect]

改变下数据：

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,set_id varchar(2),bind_type varchar(1),qty int)
insert into #T
select 1,'s1','N',12 union all
select 2,'s2','N',10 union all
select 3,'s3','N',0 union all
select 4,'s4','M',34 union all
select 5,'S4','M',10 union all
select 6,'S4','M',9

select * from (
    select id,set_id,bind_type,
    case when  bind_type ='N' and qty <> '0' then qty
    when bind_type ='N' and qty = '0' then null
    when bind_type='M'  then
        case when id=(select min(id) from #t where set_id=a.set_id) then (select sum(qty) from #t where set_id=a.set_id)
        else 0 
        end
    end as qty
    from #T a
) a where qty is not null
/*
------------------
1	s1	N	12
2	s2	N	10
4	s4	M	53
5	S4	M	0
6	S4	M	0
*/

[lff642]

create table tb (id int,set_id varchar(10),bind_type varchar(10) ,qty int)

insert into tb select        1  ,      's1'     ,         'N'     ,     12 
insert into tb select        2   ,     's2'     ,         'N'     ,     10 
insert into tb select        3   ,     's3'     ,         'N'      ,    0 
insert into tb select        4   ,     's4'     ,         'M'     ,     34 
insert into tb select        5   ,     'S4'     ,         'M'     ,       0 
insert into tb select        6   ,     'S4'      ,       'M'     ,       0 


select id,set_id,bind_type,qty from tb where bind_type = 'N'and qty<>0
union all  
select id,set_id,bind_type,sum(qty) qty from tb where bind_type = 'M'
group by id,set_id,bind_type

/*
   id        set_id        bind_type      qty 
   1        s1              N          12 
      2        s2              N          10 
      4        s4              M          34 
      5        S4              M            0 
      6        S4              M            0
*/

drop table tb

[hery2002]

-->生成测试数据
 
DECLARE @TB TABLE([ID] INT,[SET_ID] NVARCHAR(2),[BIND_TYPE] NVARCHAR(1),[QTY] INT)
INSERT @TB
SELECT 1,N'S1',N'N',N'12' UNION ALL
SELECT 2,N'S2',N'N',N'10' UNION ALL
SELECT 3,N'S3',N'N',N'0'  UNION ALL
SELECT 4,N'S4',N'M',N'34' UNION ALL
SELECT 5,N'S4',N'M',N'0'  UNION ALL
SELECT 6,N'S4',N'M',N'0' 
SELECT * FROM @TB T
WHERE NOT EXISTS(
        SELECT 1 FROM @TB WHERE [SET_ID]=T.[SET_ID] GROUP BY SET_ID HAVING SUM([QTY])=0
        )

[wzy_love_sly]

if object_id('tb') is not null
drop table tb
go
create table tb(id int,set_id varchar(20),bind_type varchar(20),qty int)
insert into tb select 1,'s1','N',12
insert into tb select 2,'s2','N',10
insert into tb select 3,'s3','N',0
insert into tb select 7,'s3','N',1
insert into tb select 4,'S4','M',34
insert into tb select 5,'S4','M',0
insert into tb select 6,'S4','M',0

select * from tb t
where not exists(
select 1 from tb where set_id=t.set_id and t.bind_type='m' group by set_id having sum(qty)=0
) and not (bind_type='n' and qty=0) 

id set_id bind_type qty
1 s1 N 12
2 s2 N 10
7 s3 N 1
4 S4 M 34
5 S4 M 0
6 S4 M 0

看了完美的才知道我又错了

[wgzaaa]

1楼加条件，其他都未考虑："如果该值为非零,则读取这个set_id的这几记录"
select * from tb t where (bind_type='N' and qty<>0) or
(bind_type='M' and not exists (select 1 from tb where set_id=t.set_id group by set_id having sum(qty)=0 ))

[wgzaaa]

sorry,又出来这么多，刚才看到5楼

[nzperfect]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,set_id varchar(2),bind_type varchar(1),qty int)
insert into #T
select 1,'s1','N',12 union all
select 2,'s2','N',10 union all
select 3,'s3','N',0 union all
select 4,'s4','M',34 union all
select 5,'S4','M',10 union all
select 6,'S4','M',9 union all
select 6,'S5','M',0 union all
select 6,'S5','M',0 union all
select 6,'S5','M',0 

select * from (
    select id,set_id,bind_type,
    case when  bind_type ='N' and qty <> '0' then qty
    when bind_type ='N' and qty = '0' then null
    when bind_type='M'  then
        case when (select isnull(sum(qty),0) from #t where set_id=a.set_id) =0 then null
        else
            case when id=(select min(id) from #t where set_id=a.set_id) then (select sum(qty) from #t where set_id=a.set_id)
            else 0 
            end
        end
    end as qty
    from #T a
) a where qty is not null
/*
------------------
1	s1	N	12
2	s2	N	10
4	s4	M	53
5	S4	M	0
6	S4	M	0
*/

[nzperfect]

楼主有一个地方交待的不清楚。。
就是这样的数据：
4 s4 M 34
5 S4 M 10
6 S4 M 9

要显示成：
4 s4 M 34
5 S4 M 10
6 S4 M 9
还是:
4 s4 M 53
5 S4 M 0
6 S4 M 0

[hanjs]

to perfectaction

LZ说了

则读取这个set_id的这几记录,一般情况下同一个set_id,且bind_type为M的字段只有第一条的值为非零.

[JB-Zhang]

TO perfectaction
相同set_id, bind_type为M的,只有第一条是非零.

[yinqi025]

真快呀.....你们抢得

[hanjs]

LZ说如果是M的时候，只要sum（qty）〉0，就取出所有的

if object_id('tb') is not null
drop table tb
go
create table tb(id int,set_id varchar(20),bind_type varchar(20),qty int)
insert into tb select 1,'s1','N',12
insert into tb select 2,'s2','N',10
insert into tb select 3,'s3','N',0
insert into tb select 4,'S4','M',34
insert into tb select 5,'S4','M',0
insert into tb select 6,'S4','M',0

select * from tb
where (bind_type='N' and qty>0) or 
(exists (select 1 from tb a where a.bind_type='M' and a.set_id=tb.set_id 
group by set_id having sum(qty)>0 ))

/*
1	s1	N	12
2	s2	N	10
4	S4	M	34
5	S4	M	0
6	S4	M	0
*/