子查询

离开你，地球照样转，可惜我不是地球。 --zhu
案例
（1）关羽的银行卡号为'6225547854125656',查询出余额比关羽多的银行卡信息，显示卡号，身份证，姓名，余额。
--方案一

declare @gyBalance money
select @gyBalance =  (select CardMoney from BankCard where CardNo ='6225547854125656')
select CardNo 卡号,AccountCode 身份证,RealName 姓名, CardMoney 余额 from BankCard
left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId 
where CardMoney > @gyBalance

--方案二：

select CardNo 卡号, AccountCode 身份证, RealName 姓名, CardName 余额  from BankCard 
left join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId 
where CardMoney >
(select CardMoney from BankCard where CardNo ='6225547854125656')

（2）从所有账号信息中查询出余额最高的交易明细（存钱取钱信息）
--方案一：

select * form CardExchange where CardNo in 
(select CardNo from  BankCard where CardMoney = (select Max(CardMoney) from BankCard ))

--方案二：（如果有多个银行卡余额想等并且最高，此方案只能求出其中一人的明细）

select * form CardExchange where CardNo =
（select top 1 CardNo from BankCard order by CardMoney desc）

（3）查阅有取款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号, AccountInfo 身份证,  RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId
where CardNo in (select CardNo from CardExchange where MoneyOutBank > 0)

（4）查询出没有存款记录的银行卡及账号信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号, AccountInfo 身份证,  RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId
where CardNo not in (select CardNo from CardExchange where MoneyOutBank > 0)

（5）关羽银行卡号为”6225547854125656，查询当天是否有收到转账。

if exists(select * from CardTransfer where CardNoInfo ='6225547854125656'
and CONVERT(VARCHAR(22),GETDATE(),23) = CONVERT(VARCHAR(22),TransferTime,23))
  begin
    print '有收到转账'
  end
else
  begin 
   print '没有收到转账'
  end

（6）查询出交易次数（存款取款操作）最多的银行卡账户信息。
--显示：卡号，身份证，姓名，余额，交易次数

select top 1 BankCard.CardNo 卡号, AccountCode 身份证, RealName 姓名, CardName 余额,Temp.myCount 交易次数  from BankCard 
left join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId
inner join 
(select CardNo,count(*) myCount from CardExchange group by CardNo) Temp
on BankCard.CardNo  = Temp.CardNo
order by  Temp.myCount desc

select BankCard.CardNo 卡号, AccountCode 身份证, RealName 姓名, CardName 余额,Temp.myCount 交易次数  from BankCard 
left join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId
inner join 
(select CardNo,count(*) myCount from CardExchange group by CardNo) Temp
on BankCard.CardNo  = Temp.CardNo
where Temp.myCount = (select max(Temp.myCount) from  (select CardNo,count(*) myCount from CardExchange group by CardNo) Temp)

（7）查询出没有转账交易记录的银行卡账户信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号, AccountInfo 身份证,  RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo  on BankCard.AccountId = AccountInfo.AccountId
where CardNo not in (select CardNoOut from CardTransfer)
and CardNo not in (select CardNoIn from CardTransfer)