sql语句统计查询结果数量怎么写?
发布网友
发布时间：2022-04-28 15:28
共2个回答
懂视网
时间：2022-04-09 11:10
select
--总注册人数
(select COUNT(*) from [YYD_Users_RegInfo]) as TotalCount,
--pc端注册人数
(select COUNT(*) from [YYD_Users_RegInfo] where regPlatform =‘pc‘) as PCTotalCount,
--手机端注册人数
(select COUNT(*) from [YYD_Users_RegInfo] where regPlatform =‘mobile‘) as MobileTotalCount,
--新增总注册人数
(select COUNT(*) from [YYD_Users_RegInfo]
where Convert(varchar(10),RegTime,120) >=Convert(varchar(10),‘2016-07-01‘,120)
and Convert(varchar(10),RegTime,120) <=Convert(varchar(10),‘2016-07-06‘,120) ) as AddTotalCount,
--新增pc端注册人数:
(select COUNT(*) from [YYD_Users_RegInfo] where regPlatform =‘pc‘
and Convert(varchar(10),RegTime,120) >=Convert(varchar(10),‘2016-07-01‘,120)
and Convert(varchar(10),RegTime,120) <=Convert(varchar(10),‘2016-07-06‘,120)) as AddPCTotalCount,
--新增手机端注册人数
(select COUNT(*) from [YYD_Users_RegInfo] where regPlatform =‘mobile‘
and Convert(varchar(10),RegTime,120) >=Convert(varchar(10),‘2016-07-01‘,120)
and Convert(varchar(10),RegTime,120) <=Convert(varchar(10),‘2016-07-06‘,120)) as AddMobileTotalCount,
--激活总人数
(select count(distinct USER_ID) from [YYD_Account_Verification] where status =1) as TotalVerification,
--实名认证人数
(select count(distinct USER_ID) from [YYD_Account_Verification] where vtype=‘ver_realname‘ and status=1) as TotalRealnameVerification,
--新增激活总人数
(select count(distinct USER_ID) from [YYD_Account_Verification] where status =1
and Convert(varchar(10),createtime,120) >=Convert(varchar(10),‘2016-07-01‘,120)
and Convert(varchar(10),createtime,120) <=Convert(varchar(10),‘2016-07-06‘,120)) as AddTotalVerification,
--新增实名认证人数
(select count(distinct USER_ID) from [YYD_Account_Verification] where vtype=‘ver_realname‘ and status=1
and Convert(varchar(10),createtime,120) >=Convert(varchar(10),‘2016-07-01‘,120)
and Convert(varchar(10),createtime,120) <=Convert(varchar(10),‘2016-07-06‘,120)) as AddTotalRealnameVerification,
--累计开户数
(select count(distinct USER_ID) from [YYD_Account_BankAccount] where status =1) as OpenAnAccount
--充值统计
select top 1 (select count(*) from (select distinct user_id from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1) a) People,
(select count(*) from (select distinct user_id from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1 and createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) a) NewPeople,
(select count(*) from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1) Number,
(select count(*) from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1 and createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewNumber,
(select sum(amount) from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1) Amount,
(select sum(amount) from YYD_Account_MoneyRecord where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and state=1 and createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewAmount,
(select COUNT(*) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and (r.recharge_type=‘0‘ or recharge_type=‘chinapay_b2c‘ or recharge_type=‘offline‘)) pcCount,
(select COUNT(*) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and r.recharge_type=‘chinapaywap‘) MobileCount,
(select SUM(m.amount) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and r.recharge_type=‘chinapaywap‘) MobileAmount,
(select SUM(m.amount) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and (r.recharge_type=‘0‘ or recharge_type=‘chinapay_b2c‘ or recharge_type=‘offline‘)) PcAmount,
(select COUNT(*) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and (r.recharge_type=‘0‘ or recharge_type=‘chinapay_b2c‘ or recharge_type=‘offline‘) and m.createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and m.createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewPcCount,
(select COUNT(*) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and r.recharge_type=‘chinapaywap‘ and m.createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and m.createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewMobileCount,
(select SUM(m.amount) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and r.recharge_type=‘chinapaywap‘ and m.createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and m.createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewMobileAmount,
(select SUM(m.amount) from YYD_Account_MoneyRecord m inner join YYD_Account_RechargeRecord r on r.ID=m.refid where (moneytype=‘充值‘ or moneytype=‘线下充值‘) and m.state=1 and (r.recharge_type=‘0‘ or recharge_type=‘chinapay_b2c‘ or recharge_type=‘offline‘) and m.createtime >=convert(varchar(100),‘2016-06-29 00:00:00‘,20) and m.createtime <=convert(varchar(100),‘2016-07-06 23:59:59‘,20) ) NewPcAmount
from YYD_Account_MoneyRecord
--投资统计
select top 1
(select COUNT(*) from YYD_Borrow_BidRecord) BidCount,
(select COUNT(*) from (select distinct bid_user_id from YYD_Borrow_BidRecord ) data)BidUsersCount,
(select sum(amount) from YYD_Borrow_BidRecord) BidAmount,
(select SUM(repay_amount) from YYD_Borrow_RepayRecord where status=0 and repaytype=‘本金‘) DHRepayAmount,
(select COUNT(*) from YYD_Borrow_BidRecord where 1=1 {0}) NewBidCount,
(select COUNT(*) from (select distinct bid_user_id from YYD_Borrow_BidRecord where 1=1 {0}) data) NewBidUsersCount,
(select sum(amount) from YYD_Borrow_BidRecord where 1=1 {0}) NewBidAmount,
(select SUM(repay_amount) from YYD_Borrow_RepayRecord where status=1 and repaytype=‘本金‘) YHRepayAmount
from YYD_Borrow_BidRecord
统计查询-sql
标签：
热心网友
时间：2022-04-09 08:18
可以通过count函数来实现。
sqlOne：select * from tablename1 where id>5;此语句查询出来多条记录，之后看做一个新的表。
sqlTwo：select conut(*) from (select * from tablename1 where id>5) as tablename2;此语句即可查询出来统计的记录条数。
备注：以上方法通用于所有的数据统计，如果是单表查询，可以直接通过：“select count( *) from tablename1 where id>5"的形式查询出结果。
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
1986年10月，美国国家标准协会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。
