查找重复内容并返回值

[sankyqiu]

想通过查询
查询同一BILLID，并且STOREID是相同时返回1
否则返回0


S_SENDD

BILLID ITEMNO GOODSID STOREID QTY
1001 1 901 11 30
1001 2 903 11 23
1001 3 906 11 56

1002 1 901 22 34
1002 2 901 11 66

查询结果 1001 返回 1
1002 返回 0

[7761098]

select case when count(*) >1 then 1 else 0 end from S_SENDD where BILLID = '1001'

[bancxc]

select case when(count(*)-1)>0 then 1 else 0 end) from tb where billid='1001'  

[sankyqiu]

[Quote=引用 1 楼 7761098 的回复:]
select case when count(*) >1 then 1 else 0 end from S_SENDD where BILLID = '1001'
[/Quote]

这应该不行吧，我想判断同一BILLID，并且STOREID 相同时才返回值
billid=1001、BILLID=1003 时返回1
BILLID ITEMNO GOODSID STOREID QTY
1001 1 901 11 30
1001 2 903 11 23
1001 3 906 11 56

1002 1 901 22 34
1002 2 901 11 66

1003 1 801 33 44

[7761098]

不好意思啊，抄写错误

select case when count(distinct STOREID) >1 then 1 else 0 end from S_SENDD where BILLID = '1001' 

[bancxc]

select case when(count(distinct storeid)>1 then 0 else 1 end) from tb where billid='1001'

[bancxc]

sorry 括号错了
select (case whencount(distinct storeid)>1 then 0 else 1 end) from tb where billid='1001'

[sankyqiu]

这里也要返回1
BILLID ITEMNO GOODSID STOREID QTY
1003 1 801 33 44

[componment_429]

/**创建临时表**/
CREATE TABLE #tempA(
BILLID VARCHAR(20)
,ITEMN0 INT
,GOODSID VARCHAR(20)
,STOREID VARCHAR(20)
,QTY INT
)

/**向#tempA中插入测试数据**/

select * from #tempA
INSERT INTO #tempA
select '1001',1,'901','11',30 union all
select '1001',2,'903','11',23 union all
select '1001',3,'906','11',56 union all
select '1002',1,'901','22',34 union all
select '1002',2,'901','11',66 union all
select '1003',1,'801','33',33

select test,result from (select 1 as 'result',(case when tempB.cnt>1 then
(select BILLID from #tempA group by BILLID having count( distinct STOREID)>1)
else (select BILLID as cnt from #tempA group by BILLID having(COUNT(1))=1)
end) as test
from (select BILLID,COUNT(1) as cnt from #tempA group by BILLID) as tempB) as tempC
group by test,result

--------------------------------
test result
1002 1
1003 1

[componment_429]

你自己改下，看完了看下，和要的相反，见笑，我还是SQL小白

[componment_429]

select BILLID,1 test from #tempA group by BILLID having count( distinct STOREID)=1

[xiaoliaoyun]

SELECT BILLID
      ,Flag = CASE WHEN COUNT(DISTINCT STOREID) = 1 THEN 1 ELSE 0 END
FROM S_SENDD
GROUP BY BILLID