一、报表
一、报表需求：
今日 ：评估完成 ，待下户 / 已下户 ，待审批 / 已审批 ，已签约 / 已放款 ，应收利息 ，逾期客户
今日 ，昨日 ；
本周 ，上周 ；
本月 ，上月 ；
本季度 ，上季度
今年 ，去年
二、进件表和进件日志表分析
此进件（包含进件表、和进件状态日志表）
等同于 ，那个数据库 订单表、对应订单状态日志表，怎么关联查询出具有订单日志记录的订单信息
--查询有效性来自进件日志的进件数量（最终数据是有效合法的进件数量） select count(*) from Tbl_Loan WHERE ID in (select LoanID FROM Tbl_LoanLog WHERE dealflag=0 ) AND dealflag=0 --查询有效性来自进件数量的进件日志（最终数据是有效合法的进件日志） select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0
日期
SELECT * FROM Tbl_LoanLog where DATEDIFF(day,CreateTime,GETDATE())=0 --查询今日 SELECT * FROM Tbl_LoanLog where DATEDIFF(day,CreateTime,GETDATE())=1 --查询昨日 select * from Tbl_LoanLog where datediff(week, CreateTime,getdate())=0 --本周数据 --Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 0 --本周数据 Select * From Tbl_LoanLog Where DateDiff(wk, CreateTime, GetDate()) = 1 --上周 select * from Tbl_LoanLog where datediff(month, CreateTime,getdate())=0 --本月 --Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 0--本月 SELECT * FROM Tbl_LoanLog where DATEDIFF(month,CreateTime,GETDATE())=1 --查询上月 select * from Tbl_LoanLog where datediff(quarter, CreateTime,getdate())=0 --本季度 select * from Tbl_LoanLog where datediff(quarter, CreateTime,getdate())=1 --上季度 select * from Tbl_LoanLog where datediff(year, CreateTime,getdate())=0 --今年 select * from Tbl_LoanLog where datediff(year,CreateTime,getdate())=1 --去年
form子查询
SELECT * -- 语句1 FROM ( -- 语句2 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句3
进一步对子查询再次筛选
SELECT * -- 语句1 ,(SELECT COUNT(ID) from Tbl_LoanLog where DATEDIFF(day,CreateTime,GETDATE())=0 ) as 子查询昨日统计 -- 语句2 FROM ( -- 语句3 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句4
缺点：form查询就是查询是数据集，然而需要的则是返回统计列
所以一开始 In 查询的就是结果集-多条，*则是列无关 ，然后 from的子查询仅是从之前in的结果集中筛选而已，
（要么一开始就count，只有一列，需要多个sql union all ）,(要么查询多个结果集，然后在top 或者count )
最终解决：
SELECT -- 语句1 TOP 1 (SELECT COUNT(ID) from Tbl_LoanLog where DATEDIFF(day,CreateTime,GETDATE())=0 ) as 子查询昨日统计 -- 语句2 FROM ( -- 语句3 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句4
续
SELECT -- 语句1 TOP 1 (SELECT COUNT(ID) from Tbl_LoanLog where Status='评估完成' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日评估完成统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='待下户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日待下户统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已下户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已下户统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='待审批' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日待审批统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已审批' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已审批统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已签约' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已签约统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已放款' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已放款统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='应收利息' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日应收利息统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='逾期客户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日逾期客户统计 -- 语句2 FROM ( -- 语句3 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句4
存在bug
SELECT -- 语句1 (SELECT COUNT(*) from Tbl_LoanLog ) as aa --此句未关联 子查询FROM FROM ( -- 语句2 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句3
第二种
SELECT -- 语句1 TOP 1 (SELECT COUNT(ID) from Tbl_LoanLog where Status=a.Status) as 今日评估完成统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='待下户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日待下户统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已下户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已下户统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='待审批' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日待审批统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已审批' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已审批统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已签约' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已签约统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='已放款' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日已放款统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='应收利息' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日应收利息统计 -- 语句2 --,(SELECT COUNT(ID) from Tbl_LoanLog where Status='逾期客户' AND DATEDIFF(day,CreateTime,GETDATE())=0 ) as 今日逾期客户统计 -- 语句2 FROM ( -- 语句3 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句4 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0
然后在页面写的
var sqlStr = "select " + " TOP 1 " + "(SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(day, CreateTime, GETDATE()) = 0) as toDayEstimateAmount " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDayHouseHold " + ",(SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDayHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDayApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDayApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDaySigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(day, CreateTime, GETDATE()) = 0 ) as toDayLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayEstimateAmount " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDaySigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(day, CreateTime, GETDATE()) = 1 ) as yesterDayLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekEstimateAmount " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekHousedHoldE " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekSigned " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(week, CreateTime, GETDATE()) = 0 ) as weekLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekEstimateAmount " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekApproval " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekSigned " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(week, CreateTime, GETDATE()) = 1 ) as lastWeekLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthEstimateAmount " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthSigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(month, CreateTime, GETDATE()) = 0 ) as monthLending " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthEstimateAmount " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthHousedHoldE " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthSigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(month, CreateTime, GETDATE()) = 1 ) as lastMonthLending " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterEstimateAmount " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterSigned " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 0 ) as quarterLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterEstimateAmount " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterHousedHoldE " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterSigned " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(quarter, CreateTime, GETDATE()) = 1 ) as lastQuarterLending " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearEstimateAmount " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearApprovaled " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearSigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(year, CreateTime, GETDATE()) = 0 ) as yearLending " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '评估完成' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearEstimateAmount " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待下户' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearHouseHold " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已下户' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearHousedHoldE " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '待审批' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearApproval " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已审批' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearApprovaled " + " , (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已签约' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearSigned " + ", (SELECT COUNT(ID) from Tbl_LoanLog where Status = '已放款' AND DATEDIFF(year, CreateTime, GETDATE()) = 1 ) as lastYearLending " + "FROM( " + "select * from Tbl_LoanLog WHERE LoanID in" + "(select ID FROM Tbl_Loan WHERE dealflag = 0)" + "AND dealflag = 0 " + ") AS a";
问题解决
SELECT -- 语句1 (SELECT COUNT(ID) from Tbl_LoanLog where Status=a.Status) as 今日评估完成统计 -- 原来入此，此语句有两个错 --1、全新查询Tbl_LoanLog （Tbl_LoanLog数据最终大于Tbl_LoanLog和Tbl_Loan联查） --2、 Status状态匹配的 --（条数取决与Tbl_LoanLog与Tbl_LoanLog和Tbl_Loan联查， --而每一行则是: --查询Tbl_LoanLog第一条数据，匹配Tbl_LoanLog与Tbl_LoanLog和Tbl_Loan联查的数据） --以此遍历第二行、三行、遍历匹配Tbl_LoanLog与Tbl_LoanLog和Tbl_Loan联查的数据 FROM ( -- 语句3 select * from Tbl_LoanLog WHERE LoanID in (select ID FROM Tbl_Loan WHERE dealflag=0 ) AND dealflag=0 )AS a -- 语句4
我觉得思路错了
我现在是情景是 有一个订单表 ，该商品订单有很多订单，我要查看买类别是鞋子的， 这周，上个周，这月，上个月，季度、上个季度的报表（支付 未支付的） SELECT TOP 1 --这里 怎么一周已支付的 --这里 怎么一月已支付的 --这里 怎么一季度已支付的 --这里 怎么一年已支付的 --这里 怎么一周未支付的 --这里 怎么一月未支付的 --这里 怎么一季度未支付的 from ( SELECT * FROM 订单表 INNER JOIN 类别表 on 订单id =类别id where 类别名字='鞋子' )a 最终展示想要的效果 这周已支付 这周未支付 上周已支付 上周未支付 这月已支付 上个月已支付的 这月未支付 上个月未支付的 0 50 40 20 40 50 30 40
思路转换（from子查询最终产生多行，还要内部必须关联（这个地方有点懵），基于这两点换下面两种）
写法1
写法2
//count括号里带统计条件
SELECT user_id,count(type=1 or null) donate_num,count(type=2 or null) rent_num FROM ot_dynamic GROUP BY user_id
点到为止
