SQL 查询无非就是select语句的使用,至于如何进行查询就要看查询条件
--从所有账户信息查询出余额最高的交易明细(存钱取钱信息)
--如果多个人余额一样,并且都是最高,下面只能查出一个
select * from CardExchange where CardNo=
(select top 1 CardNo from BankCard order by CardMoney desc)
--如果有多个人余额一样,并且最高,需要都查出来
select CardNo from BankCard where CardMoney=
(select max(CardMoney) from BankCard)
--查询有取款记录的银行卡及账户信息,显示卡号,身份证,姓名,余额
select * from CardExchange where MoneyOutBank>0
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)
--查询没有存款记录
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)
select * from CardExchange where MoneyInBank>0
select * from BankCard
update BankCard set CardMoney=CardMoney-100 where CardNo='6225547854125656'
update BankCard set CardMoney=CardMoney+100 where CardNo='6225547858741263'
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225547854125656','6225547858741263',100,getdate())
--关羽的银行卡信息'6225547858741263'是否有收到转账
if exists(select * from CardTransfer where CardNoIn='6225547858741263'
and convert(varchar(22),getdate(),23)=convert(varchar(22),TransferTime,23))
begin
print '有转账记录'
end
else
begin
print '没有转账记录'
end
--查询交易次数最多的银行卡信息
--显示:卡号,身份证,姓名,余额,交易次数
select BankCard.CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,Temp.myCount 交易次数 from BankCard
inner 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
分页操作为了让显示结果更具有可读性,一般来说主要是有两种方法:
(1)top分页
--分页方案:top方式分页
declare @PageSize int =5
declare @PageIndex int=1
select top(@PageSize) * from Student
where stuId not in(select top(@PageSize * (@PageIndex-1))
StuId from Student)
(2)使用row_number分页
select * from
(select ROW_NUMBER() over(order by stuId) RowId,* from Student) Temp
where RowId between ( @PageIndex-1)* @PageSize and @PageIndex*@PageSize
注意上面的的代码中@PageIndex代表一页里面的数据行数,@PageSize代表页数