1 2 3 4 5 6 7 8 9 10 |
--知识点:row_number百万级高效分页,组合查询,判断,金额格式化,日期格式化 Select * FROM (--分页 SELECT ROW_NUMBER()Over(order by m.KeyId desc) as rowId,--分页函数row_number() s=case --case判断 when edNumber like 'IN%' then '收入' when edNumber like 'OUT%' then '支出' end,--下面是组合查询超级大招 m.KeyId,m.edNumber,CONVERT(decimal(18, 2), m.total) total,CONVERT(decimal(18, 2), m.payment) payment, CONVERT(varchar(100), m.add_time, 23) add_time,CONVERT(varchar(100), m.up_time, 23) up_time,m.note,a.accountName,a.KeyId aid ,c.KeyId cid,c.company,d.KeyId did,d.depName from NetWingCRM_FinanceMain m,NetWingCRM_BankAccount a,NetWingCRM_Company c,NetWingCRM_Dep d where m.account=a.KeyId and m.unit=c.KeyId and m.dep=d.KeyId ) as mytable --分页 WHERE rowId between 1 and 10 --分页条件 |
转载请注明:中国翼通 » sql 超级大招 知识点:row_number百万级高效分页,组合查询,判断,金额格式化,日期格式化