SQL 分组查询问题

[chuting1]

表格sellinfo

ID bh rq A1 A2 A3
1 c1 2018-07-01 5 0 0
2 c1 2018-07-01 0 10 0
3 c1 2018-07-12 0 20 0
4 c2 2018-07-01 15 0 0
5 c2 2018-07-01 5 0 0
6 c3 2018-07-01 5 0 0
7 c4 2018-07-12 5 0 0
8 c5 2018-07-01 5 0 0
9 c5 2018-07-01 0 10 0
10 c5 2018-07-01 0 0 10


要求出，每一个bh某一天内，A1、A2、A3均有值>0的bh记录，
如c1、c5满足 2018-07-01 A1有记录、A2有记录

只用分组select bh,rq from sellinfo group by bh,rq having count(id)>1不能满足要求

[chuting1]

楼上正解，两种方法都可以

[chuting1]

要求出，每一个bh某一天内，A1、A2、A3中至少两项均有值>0的bh记录，
如c1、c5满足 2018-07-01 A1有记录、A2有记录

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[bh] nvarchar(22),[rq] Date,[A1] int,[A2] int,[A3] int)
Insert #T
select 1,N'c1','2018-07-01',5,0,0 union all
select 2,N'c1','2018-07-01',0,10,0 union all
select 3,N'c1','2018-07-12',0,20,0 union all
select 4,N'c2','2018-07-01',15,0,0 union all
select 5,N'c2','2018-07-01',5,0,0 union all
select 6,N'c3','2018-07-01',5,0,0 union all
select 7,N'c4','2018-07-12',5,0,0 union all
select 8,N'c5','2018-07-01',5,0,0 union all
select 9,N'c5','2018-07-01',0,10,0 union all
select 10,N'c5','2018-07-01',0,0,10
Go
--测试数据结束
Select bh,rq from #T
GROUP BY bh,rq
HAVING SUM(
DISTINCT
CASE WHEN A1>0 THEN 1 ELSE 0 END )+SUM(
DISTINCT
CASE WHEN A2>0 THEN 1 ELSE 0 END )+SUM(
DISTINCT
CASE WHEN A3>0 THEN 1 ELSE 0 END )>1

[chuting1]

高手！真正的高手

[中国风]

C1?
同一天A5没有值，也计算成立？

写个例子，只有C5同一天，A1/A2/A3 都有值

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#sellinfo') is null
	drop table #sellinfo
Go
Create table #sellinfo([ID] int,[bh] nvarchar(22),[rq] Date,[A1] int,[A2] int,[A3] int)
Insert #sellinfo
select 1,N'c1','2018-07-01',5,0,0 union all
select 2,N'c1','2018-07-01',0,10,0 union all
select 3,N'c1','2018-07-12',0,20,0 union all
select 4,N'c2','2018-07-01',15,0,0 union all
select 5,N'c2','2018-07-01',5,0,0 union all
select 6,N'c3','2018-07-01',5,0,0 union all
select 7,N'c4','2018-07-12',5,0,0 union all
select 8,N'c5','2018-07-01',5,0,0 union all
select 9,N'c5','2018-07-01',0,10,0 union all
select 10,N'c5','2018-07-01',0,0,10
Go
SELECT [bh]
     , [rq]
FROM #sellinfo
GROUP BY [bh]
       , [rq]
HAVING MAX(SIGN(A1)) +MAX(SIGN(A2)) +MAX(SIGN(A3)) =3;
/*
bh	rq
c5	2018-07-01
*/

[中国风]

MAX(SIGN(A1)) +MAX(SIGN(A2)) +MAX(SIGN(A3)) >=2
----------
满足有两列有值时改这个条件