数据表TA
customer checkin
A 12000
B 10000
数据表TB
customer checkout
A 1000
B 2000
A 1000
A 3000
B 5000表TA代表储户存款金额,表TB代码储户取款记录,用户当前余额 = 储户存款金额 - 储户取款金额
请用一条SQL语句列出当前金额 > 5000的用户
customer checkin
A 12000
B 10000
数据表TB
customer checkout
A 1000
B 2000
A 1000
A 3000
B 5000表TA代表储户存款金额,表TB代码储户取款记录,用户当前余额 = 储户存款金额 - 储户取款金额
请用一条SQL语句列出当前金额 > 5000的用户
customer
from
(select customer,checkin as m from ta
union all
select customer,-checkout from tb
) t
group by
customer
having
sum(m)>5000
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tA') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta(customer char(1),checkin int )
go
insert into ta
select 'a',12000 union all
select 'b',10000
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(customer char(1),checkout int )
go
insert into tb select
'A' , 1000 union all select
'B' , 2000 union all select
'A' , 1000 union all select
'A' , 3000 union all select
'B' , 5000
go
select ta.customer,当前金额=sum(distinct ISNULL(checkin,0))-SUM(ISNULL(checkout,0))
from ta left join tb on ta.customer=tb.customer
group by ta.customer
having sum(distinct ISNULL(checkin,0))-SUM(ISNULL(checkout,0))>5000/*------------
-------- -----------
customer 当前金额
-------- -----------
a 7000
-------*/
IF OBJECT_ID('tA') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta(customer char(1),checkin int )
go
insert into ta
select 'a',12000 union all
select 'b',10000
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(customer char(1),checkout int )
go
insert into tb select
'A' , 1000 union all select
'B' , 2000 union all select
'A' , 1000 union all select
'A' , 3000 union all select
'B' , 5000
go
select * from
(
select ta.customer,当前金额=checkin-sum(checkout)
from ta join tb on ta.customer=tb.customer
group by ta.customer,ta.checkin
) tmp
where 当前金额>5000
select distinct * from
(
select ta.customer ,当前金额=checkin-sum(checkout) over(partition by ta.customer)
from ta join tb on ta.customer=tb.customer
) tmp
where 当前金额>5000/*
customer 当前金额
-------- -----------
a 7000(1 行受影响)*/
IF OBJECT_ID('tA') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta(customer char(1),checkin int )
go
insert into ta
select 'a',12000 union all
select 'b',10000
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(customer char(1),checkout int )
go
insert into tb select
'A' , 1000 union all select
'B' , 2000 union all select
'A' , 1000 union all select
'A' , 3000 union all select
'B' , 5000
go select a.customer,a.checkin-sum(checkout) as 当前金额
from ta a ,tb b
where
a.customer=b.customer
group by a.customer,a.checkin
having sum(distinct checkin)-sum(checkout)>5000
/*customer 当前金额
-------- -----------
a 7000(所影响的行数为 1 行)*/
IF OBJECT_ID('tA') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta(customer char(1),checkin int )
go
insert into ta
select 'a',12000 union all
select 'b',10000
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(customer char(1),checkout int )
go
insert into tb select
'A' , 1000 union all select
'B' , 2000 union all select
'A' , 1000 union all select
'A' , 3000 union all select
'B' , 5000
select a.customer,(a.checkin-b.checkout) ye from ta a inner join (select customer,sum(checkout) checkout from tb group by customer) b
on a.customer=b.customer
where (a.checkin-b.checkout)>5000
declare @TA table( customer nvarchar(10), checkin decimal(18,4) )
declare @TB table( customer nvarchar(10), checkout decimal(18,4) )insert into @TA
select 'A', 12000 union all
select 'B', 10000 union all
select 'C', 50000 union all
select 'A', 10000insert into @TB
select 'A', 1000 union all
select 'B', 2000 union all
select 'A', 1000 union all
select 'A', 3000 union all
select 'B', 5000/*** CODE CREATE BY BANLAO 2009.7.15 ***/-- 方法1select
customer,
checkin
from
(
select
TA.customer,
( checkin - isnull(checkout,0) )as checkin
from ( select customer, sum(checkin) as checkin from @TA group by customer ) as TA
left join ( select customer, sum(checkout) as checkout from @TB group by customer ) as TB
on TA.customer = TB.customer
) as A
where checkin > 5000
-------- -----------
A 17000
B 50000
customer
from
(select customer,checkin as m from ta
union all
select customer,-checkout from tb
) t
group by
customer
having
sum(m)>5000喜欢高人