SQL 求帮助。。。既包含又包含

[sky191122]

想求出只要含有1和2状态用户的所有的金额。
结果是
001 640
002 2300

[刘熙]

where 状态 = 1 or 状态 = 2

[卖水果的net]

create table test(u varchar(10), status int, num int)
go
insert into test values
('001', 1, 240),
('002', 2, 1000),
('001', 3, 400),
('002', 4, 1300),
('003', 4, 60),
('004', 5, 120),
('005', 3, 700)
go
select u, SUM(num) num from test
where exists(select * from test x where x.status in(1,2) and test.u = x.u)
group by u
go
drop table test
go


(7 行受影响)
u          num
---------- -----------
001        640
002        2300

(2 行受影响)

[咸鱼小码]

select 用户,sum(金额) from 表 where 用户 in(
select 用户 from 表 where 状态 in(1,2)) group by 用户

[zjcxc]

from tb a, tb b where a.用户=b.用户 and a.status=1 and b.status=2

[诸葛睫1874]

select 用户,sum(金额)
from table
where 用户 in(
select 用户 from table where 状态 in ('1','2')
)
group by 用户

[ZL小屁孩]

使用in和like字段不行吗

[yeminqiang_2008]

USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
create table test(u varchar(10), status int, num int)
go
insert into test values
('001', 1, 240),
('002', 2, 1000),
('001', 3, 400),
('002', 4, 1300),
('003', 4, 60),
('004', 5, 120),
('005', 3, 700)
go
select a.U,SUM(num) as num from test as a,
(select U from test where status =1 or status =2) as b
where a.u=b.u group by a.u

(7 行受影响)
U num
---------- -----------
001 640
002 2300

(2 行受影响)

[weixin_42686440]

select `状态`,SUM(`金额`) as num FROM 表名 WHERE `状态` = '001' or `状态`='002' group by `状态`

[weixin_42686440]

[weixin_42686440]