[SQL筆記] 子查詢

模擬銀行業務

範例1

(1)李四的銀行卡號為”999998888801”

• 查出餘額比李四多的銀行卡信息、顯示卡號、身份証、姓名、餘額

方案一：使用變量

Step1: 先查出要顯示的資訊欄位(inner join)
Step2: 再查出李四的卡餘額(使用變量declare, select =(查詢條件))
Step3: 再將Step1加上where條件

--(1)李四的銀行卡號為"999998888801"
--查出餘額比李四多的銀行卡信息、顯示卡號、身份証、姓名、餘額
declare @balance money
select @balance = 
(select CardMoney from BankCard where CardNo = '999998888801')

select CardNo 卡號,AccountCode 身份証, RealName 姓名, CardMoney 餘額 from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardMoney > @balance

方案二：子查詢

select CardNo 卡號,AccountCode 身份証, RealName 姓名, CardMoney 餘額 from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardMoney > (select CardMoney from BankCard where CardNo = '999998888801')

範例2

--(2)從所有帳戶信息中查詢出餘額最高的交易明細(存錢取錢信息)
--select * from CardExchange where CardNo = 餘額最高的人的卡號
--如果有多個人餘額一樣，並且都是最高的，下面查詢只能查出一個
select * from CardExchange where CardNo =
(select top 1 CardNo from BankCard order by CardMoney desc)
--如果有多個人餘額一樣，並且都是最高，需要都查出來
select * from CardExchange where CardNo in --使用in，在這裡面的卡號，都可以查詢出來
(select CardNo from BankCard where CardMoney =
(select max(CardMoney) from BankCard))

範例3

--(3)查詢出取款記錄的銀行卡及帳戶信息、顯示卡號、身份証、姓名、餘額
--先找出取款記錄的卡號：
select CardNo from CardExchange where MoneyOutBank > 0
--再透過子查詢 where 卡號 in (取款記錄的卡號)
select CardNo 卡號, AccountCode 身份証, RealName 姓名, CardMoney 餘額 
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardNo in (select CardNo from CardExchange where MoneyOutBank > 0)

範例4: in, not in

--(4)查詢出沒有存款記錄的銀行卡及帳戶信息、顯示卡號、身份証、姓名、餘額
--先查詢出有存款記錄的銀行卡號
select CardNo from CardExchange where MoneyInBank > 0
--透過子查詢 where 卡號 not in (存過錢，有存款記錄的銀行卡號)
select CardNo 卡號, AccountCode 身份証, RealName 姓名, CardMoney 餘額
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardNo not in (select CardNo from CardExchange where MoneyInBank > 0)

範例5: if exists

• if exists 判斷
• 當天日期convert(varchar(22),GETDATE(),23) = convert(varchar(22),TransferTime,23) 或是
• convert(date, GETDATE()) = convert(date, TransferTime)

  --(5)李四的銀行卡號為"999998888801"，查詢當天是否有收到轉帳
  if exists(select * from CardTransfer where CarNoIn = '999998888801' and convert(date, GETDATE()) = convert(date, TransferTime))
    begin
        print '有收到轉帳'
    end
  else
    begin
        print '沒有收到轉帳'
    end
  

  假資料(測試用)

  update BankCard set CardMoney = CardMoney - 1000 where CardNo = '999998888802'
  update BankCard set CardMoney = CardMoney + 1000 where CardNo = '999998888801'
  insert into CardTransfer(CardNoOut,CarNoIn,TransferMoney,TransferTime)
  values('999998888802','999998888801',1000,GETDATE())
  

範例6:

Step1：先inner join 要查出的資訊
Step2：再查出每張卡的交易次數
select count(*) 交易次數, CardNo from CardExchange group by CardNo
Step3：再將Step1和Step2inner join在一起，所以目前有3張表，2個inner join連結在一起
Step4：再order by 交易次數 desc，降序排序
Step5：最後，在select 加上 top 1 取第一筆資料

但，如果有相同的次數，top 1 只能查出一個。

所以 top 1 要拿掉，order by也不要了，改成where Temp.交易次數 = 最大的交易次數

--(6)查出交易次數(存款取款操作)多最的銀行卡帳戶信息
--顯示：卡號、身分証、姓名、餘額、交易次數
--但，如果有相同的次數，top 1 只能查出一個
select top 1 BankCard.CardNo 卡號, AccountCode 身份証, RealName 姓名, CardMoney 餘額, Temp.交易次數
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
inner join (
select count(*) 交易次數, CardNo from CardExchange group by CardNo) Temp on BankCard.CardNo = Temp.CardNo
order by Temp.交易次數 desc

--所以 `top 1` 要拿掉，改成`where Temp.交易次數 = 最大的交易次數`
--可以把所有相同次數的人都查出來
select BankCard.CardNo 卡號, AccountCode 身份証, RealName 姓名, CardMoney 餘額, Temp.交易次數
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
inner join (
select count(*) 交易次數, CardNo from CardExchange group by CardNo) Temp on BankCard.CardNo = Temp.CardNo
where Temp.交易次數 =
(select max(交易次數) from
(select count(*) 交易次數, CardNo from CardExchange group by CardNo) Temp)

範例7:

Step1：先將要顯示的資訊查詢出來inner join
Step2：再加上條件：
where CardNo not in (轉出的卡號列表)
and CardNo not in (轉入的卡號列表)

--(7)查詢出沒有轉帳交易記錄的銀行卡帳戶信息，顯示卡號、身份証、姓名、餘額
--Step1：先將要顯示的資訊查詢出來`inner join` 
--Step2：再加上條件： 
--where CardNo not in (轉出的卡號列表)
--and CardNo not in (轉入的卡號列表)
select top 1 BankCard.CardNo 卡號, AccountCode 身份証, 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 CarNoIn from CardTransfer)