针对如下表格: 代码 值 单号 a 12 1 b 100 1 a 96 1 d 11 2 b 10 2 a 5 2 现在我要得到:在单号基础上:(代码是a并且值>10)并且(代码是b并且值>30)所有记录 这样说清楚不,我在描述下 譬如针对单号1的我要查询:单号=1 并且(代码是a并且值>10)并且(代码是b并且值>30) 其中这个1是不确定的,就是所有的
declare @t table
(
CODE varchar(5),
VAL int,
BILL int
)
insert into @t
select 'a',12,1 union all
select 'b',100,1 union all
select 'a',96,1 union all
select 'd',111,2 union all
select 'b',10,2 union all
select 'a',5,2 union all
select 'a',23,3 union all
select 'b',50,3 union all
select 'a',3,4 union all
select 'b',100,4
SELECT DISTINCT B.BILL
FROM (SELECT BILL FROM @T WHERE CODE = 'A' AND VAL > 10) A
JOIN (SELECT BILL FROM @T WHERE CODE = 'B' AND VAL > 30) B
ON A.BILL=B.BILL
create table #t
(
code varchar(5),
num int,
id int
)
insert into #t
select 'a',12,1 union all
select 'b',100,1 union all
select 'a',96,1 union all
select 'd',111,2 union all
select 'b',10,2 union all
select 'a',5,2 union all
select 'a',23,3 union all
select 'b',50,3 union all
select 'a',3,4 union all
select 'b',100,4
SELECT id
FROM #t a
WHERE (SELECT COUNT(CASE WHEN code = 'a' AND num > 10 THEN id END)
FROM #t b
WHERE b.id = a.id) > 0 AND
(SELECT COUNT(CASE WHEN code = 'b' AND num > 30 THEN id END)
FROM #t b
WHERE b.id = a.id) > 0
GROUP BY id