汇总统计

2005-07-27 12:57:04 294 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

深入解析国家企业征信系统瑞数6vmp算法逆向实战本文深入解析了国家企业征信系统所采用的双层防护机制，重点聚焦于瑞数6代vmp算法的逆向实战。文章详细拆解了从加速乐521状态码的绕过，到瑞数412挑战的核心算法还原过程，提供了通过动态Hook、日志分析进行算法逆向，并用Python实现自动化请求链的完整技术方案，旨在为安全研究与技术学习提供参考。阅读详情

现在有
A表结构
ID,DWDM,LXCS
1，32，1
2，32，5
(ID,单位代码,来信次数)
B表结构
ID,DWDM,LFRS
1，32344，1
2，3290，3
3，326790，7
(ID,单位代码,来访人数)
关键的地方是在LFRS字段上
按照LFRS的个数分为"个人"=1,"集体"(2-4),群体(>4)
现在要求统计的结果表结构是
DWDM,来信次数,个人访次数,集体访次数,集体访人数,群体访人数,群体访次数
－－－－－－－－请大虾不吝赐教。俺刚学SQL，不懂的太多了。。。

...全文

41-dify案例分享-基于database插件实现Text2sql的数据库查询图表工作流本文介绍基于 Text2SQL 的 Dify 工作流实现过程。先阐述 Text2SQL 概念、步骤及应用领域。接着说明工作流制作，包括用 chatflow 加开场白、获取系统时间，配置 LLM 生成 SQL，用 SQL Execute 工具查询，经 LLM 汇总结果，代码生成 ECharts 数据，条件分支判断图表类型输出。最后介绍验证测试、提供体验地址，还提及借助工具生成建表和初始化数据 SQL 语句。阅读详情

dinglj1760

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

dinglj1760 2005-07-27

打赏
举报

vivianfdlpw和libin_ftsafe(子陌红尘)的方法各有千秋,给了我很大启发.
原来SQL还可以这样写的啊..呵呵
顺便问问有哪些入门的好书啊?

vivianfdlpw 2005-07-27

打赏
举报

declare @a table (ID int,DWDM int,LXCS int)
insert @a
select 1,32,1 union
select 2,32,5
declare @b table(ID int,DWDM int,LFRS int)
insert @b
select 1,32344 ,1 union
select 2,3290,3 union
select 3,326790,7

select
DWDM,
[来信次数]=sum(case when type=1 then LXCS else 0 end),
[个人访次数]=sum(case when type=2 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=2 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=2 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=2 and LXCS>4 then 1 else 0 end),
[群体访人数]=sum(case when type=2 and LXCS>4 then LXCS else 0 end)
from
(
select *,[type]=1 from @a
union all
select *,2 from @b
) t
group by DWDM

--结果
/*
DWDM 来信次数个人访次数集体访次数集体访人数群体访次数群体访人数
----------- ----------- ----------- ----------- ----------- ----------- --
32 6 0 0 0 0 0
3290 0 0 1 3 0 0
32344 0 1 0 0 0 0
326790 0 0 0 0 1 7

（所影响的行数为 4 行）
*/

子陌红尘 2005-07-27

打赏
举报

--生成测试数据
create table #A(ID int,DWDM int,LXCS int)
insert into #A select 1,32,1
insert into #A select 2,32,5
create table #B(ID int,DWDM int,LFRS int)
insert into #B select 1,32344 ,1
insert into #B select 2,3290 ,3
insert into #B select 3,326790,7

--执行查询
select
DWDM = (case when A.DWDM is null then B.DWDM else A.DWDM end),
来信次数 = isnull(A.LXCS,0),
个人访次数 = isnull(sum(case when b.LFRS = 1 then 1 else 0 end),0),
集体访次数 = isnull(sum(case when b.LFRS between 2 and 4 then 1 else 0 end),0),
集体访人数 = isnull(sum(case when b.LFRS between 2 and 4 then B.LFRS else 0 end),0),
群体访人数 = isnull(sum(case when b.LFRS > 4 then 1 else 0 end),0),
群体访次数 = isnull(sum(case when b.LFRS > 4 then B.LFRS else 0 end),0)
from
(select DWDM,LXCS=sum(LXCS) from #A group by DWDM) A
full outer join
#B B
on
A.DWDM = b.DWDM
group by
(case when A.DWDM is null then B.DWDM else A.DWDM end),isnull(A.LXCS,0)

--输出结果
DWDM 来信次数个人访次数集体访次数集体访人数群体访人数群体访次数
------- -------- ---------- ---------- ---------- ---------- ----------
3290 0 0 1 3 0 0
32344 0 1 0 0 0 0
326790 0 0 0 0 1 7
32 6 0 0 0 0 0

vivianfdlpw 2005-07-27

打赏
举报

select
DWDM,
[来信次数]=sum(case when type=1 then 1 else 0 end),
[个人访次数]=sum(case when type=2 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=2 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=2 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=2 and LXCS>4) then 1 else 0 end),
[群体访人数]=sum(case when type=2 and LXCS>4) then LXCS else 0 end)
from
(
select *，[type]=1 from A
union all
select *,2 from B
) t
group by DWDM

子陌红尘 2005-07-27

打赏
举报

create table #A(ID int,DWDM int,LXCS int)
insert into #A select 1,32,1
insert into #A select 2,32,5

create table #B(ID int,DWDM int,LFRS int)
insert into #B select 1,32344 ,1
insert into #B select 2,3290 ,3
insert into #B select 3,326790,7

select
DWDM = (case when A.DWDM is null then B.DWDM else A.DWDM end),
来信次数 = isnull(A.LXCS,0),
个人访次数 = isnull(sum(case when b.LFRS = 1 then 1 else 0 end),0),
集体访次数 = isnull(sum(case when b.LFRS between 2 and 4 then 1 else 0 end),0),
集体访人数 = isnull(sum(case when b.LFRS between 2 and 4 then B.LFRS else 0 end),0),
群体访人数 = isnull(sum(case when b.LFRS > 4 then 1 else 0 end),0),
群体访次数 = isnull(sum(case when b.LFRS > 4 then B.LFRS else 0 end),0)
from
#A A
full outer join
#B B
on
A.DWDM = b.DWDM
group by
(case when A.DWDM is null then B.DWDM else A.DWDM end),isnull(A.LXCS,0)

vivianfdlpw 2005-07-27

打赏
举报

select
DWDM,
[来信次数]=sum(case when type=1 then 1 else 0 end),
[个人访次数]=sum(case when type=1 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=1 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=1 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=1 and LXCS>4) then 1 else 0 end),
[群体访人数]=sum(case when type=1 and LXCS>4) then LXCS else 0 end)
from
(
select *，[type]=1 from A
union all
select *,2 from B
) t
group by DWDM

汇总统计

相关推荐