› henry-J › 日志

henry-J

http://www.javakc.com

根据教材中的user表、card表、transInfo表进行多表查询

2021-01-08 12:55:58 查看(297) 回复(0)

完成以下功能；

查询展示交易时间、银行卡号、客户姓名、交易类型、交易金额
查询交易次数最多的银行卡信息
查询交易次数最多的客户信息
统计每个银行卡的交易金额，展示银行卡号、银行卡余额，交易总金额
统计每个客户的交易次数，展示客户姓名、交易次数
统计每个客户的交易金额，展示客户姓名、交易金额，按交易金额降序排列
查询北京地区2019年7月的总交易金额
查询哪个城市消费总金额最大（消费：支取和转出）
查询哪个城市最不爱花钱

sELECT `cardNo`,COUNT(type) from transinfo GROUP BY cardNo

SELECT * from ( SELECT `name`,COUNT(type),`transDate` from card c,user u,transinfo f where c.userId=u.userId and f.cardNo=c.cardNo GROUP BY name ORDER BY COUNT(type) DESC)c,card g LIMIT 1

SELECT money,c.cardNo,c.yue from ( SELECT cardNo ,sum(money)yue from transinfo f GROUP BY cardNo )c,card g
WHERE c.cardNo=g.cardNo

SELECT money,c.cardNo,c.yue from ( SELECT cardNo ,sum(money)yue from transinfo f GROUP BY cardNo )c,card g
WHERE c.cardNo=g.cardNo

SELECT name,fa from (sELECT `cardNo`,COUNT(type)fa from transinfo GROUP BY cardNo)c ,user f,card z
where f.userId=z.userId and c.cardNo=z.cardNo

SELECT name ,c.money from (sELECT `cardNo`,`money` from transinfo GROUP BY cardNo)c ,user f,card z
where f.userId=z.userId and c.cardNo=z.cardNo ORDER BY money DESC

SELECT SUM(c.money) FROM (SELECT transDate,money FROM transinfo WHERE transDate>'2019-06-30' and transDate<'2019-08-01')c

SELECT city,SUM(c.money) FROM(SELECT money,type FROM transinfo WHERE type='支取' or type='转出')c,user v GROUP BY city ORDER BY SUM(c.money) DESC LIMIT 1

SELECT city,SUM(c.money) FROM(SELECT money,type FROM transinfo WHERE type='支取' or type='转出')c,user v GROUP BY city ORDER BY SUM(c.money) LIMIT 1

收藏分享邀请编辑| 删除|

用户名			找回密码
密码			立即注册

学习平台

henry-J

根据教材中的user表、card表、transInfo表进行多表查询

评论 (0 个评论)

全部作者的其他最新日志