选择：select * from table1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
子查询(表名1：a 表名2：b)
中文 N'%[吖-座]%'
sql 截取字符串：
1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；
2、POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；
3、LEFT(str, length)：从左边开始截取str，length是截取的长度；
4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；
5、SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;
6、SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；
7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；
8、LENGTH(str)：计算字符串str的长度。
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
select A.GPDM 公司代码,A.ZQJC 证券简称,B.XXFBRQ 信息发布日期,B.XGSJ 修改时间,B.XGRY 姓名,C.OPERATORNAME 修改人员
from usrGSMCGGZK B
JOIN usrZQZB A ON B.IGSDM=A.IGSDM
JOIN cmdOPERATORS C ON C.OPERATORID=B.XGRY
WHERE B.XXFBRQ>='2017-2-1' AND A.ZQLB IN (1,2) and A.ZQSC IN (83,90) AND B.GKBZ<>3
order by B.XXFBRQ DESC,A.GPDM
---股东名称简称错误
select b.GPDM 股票代码,a.GDMD 股东名称
from usrGDMD a,usrZQZB b
where a.IGSDM=b.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and (a.GKBZ=3 OR a.GKBZ<>3)
and a.XXFBRQ>='2016-09-30'
and a.GDSSXZ=2
and len(a.GDMD)<=4
----限售流通股东名称简称错误
select b.GPDM 股票代码,a.GDMD 股东名称
from usrZDSXGQFZXSLT a,usrZQZB b
where a.IGSDM=b.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and (a.GKBZ=3 OR a.GKBZ<>3)
and a.XXFBRQ>='2016-09-30'
and len(a.GDMD)<=5
and len(a.GDMD)<>3
order by (a.GDMD) desc
-----股东背景表股东名称简称错误
SELECT B.GPDM 证券代码,A.GDMC 股东名称
FROM usrZYGDBJJS A,usrZQZB B
WHERE A.IGSDM=B.IGSDM
and B.ZQSC IN (83,90)
and B.ZQLB in (1,2)
AND len(A.GDMC)<=5
AND A.GDXZ=2
AND A.XXFBRQ>'2010-01-01'
AND A.CZYF=1
SELECT A.TABLENAME,A.TABLENAMECHN,B.*
FROM cmdTABLES A
JOIN cmdCOLUMNS B ON B.TABLEID = A.TABLEID
WHERE A.TABLENAME='usrZDSXGFJCYHG'
查表名字段
select b.GPDM 股票代码,b.ZQJC,a.JZRQ 截止日期 FROM usrGSGBJG a
join usrZQZB b on a.IGSDM=b.IGSDM
where b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
限售流通总数查错
SELECT A.GDMD,B.ABC,B.GBBDYYLB,A.ZGB/10000 FROM usrZDSXGQFZXSLT A
JOIN (SELECT IGSDM,GBBDYYLB,GDMD,SUM(A.XZKSGF)/10000 ABC FROM usrZDSXGQFZXSLT A GROUP BY IGSDM,GBBDYYLB,GDMD) B
ON A.IGSDM=B.IGSDM
AND A.GDMD=B.GDMD
AND A.GBBDYYLB=B.GBBDYYLB
AND A.ZGB!=B.ABC*10000
JOIN usrZQZB C ON A.IGSDM=C.IGSDM AND C.ZQLB IN (1,2) AND C.ZQSC IN (83,90)
WHERE C.GPDM='代码'
select [公告类型]='1上午公告',
[公告日期]=A.XXFBRQ,
[代码]=B.GPDM, [简称]=B.ZQJC,
[公告标题]= A.BT,[公告入库时间]=A.XGSJ
from usrGGLJDZ A with (nolock)
left join usrZQZB B ON A.INBBM = B.INBBM AND B.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=B.IGSDM ORDER BY ZQLB) AND B.ZQLB IN (1,2)
left join usrGSCWZYZB C ON A.IGSDM = C.IGSDM AND (C.XXFBRQ=A.XXFBRQ)
where A.XGSJ>'2017-11-08 07:00:00' AND A.XGSJ<'2017-11-08 11:00:00' AND A.XXFBRQ='2017-11-08'
UNION ALL
select [公告类型]='2中午公告',
[公告日期]=A.XXFBRQ,
[代码]=B.GPDM, [简称]=B.ZQJC,
[公告标题]= A.BT,[公告入库时间]=A.XGSJ
from usrGGLJDZ A with (nolock)
left join usrZQZB B ON A.INBBM = B.INBBM AND B.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=B.IGSDM ORDER BY ZQLB) AND B.ZQLB IN (1,2)
left join usrGSCWZYZB C ON A.IGSDM = C.IGSDM AND (C.XXFBRQ=A.XXFBRQ)
where A.XGSJ>'2017-11-08 11:00:00' AND A.XGSJ<'2017-11-08 13:00:00' AND A.XXFBRQ='2017-11-08'
UNION ALL
select [公告类型]='3下午公告',
[公告日期]=A.XXFBRQ,
[代码]=B.GPDM, [简称]=B.ZQJC,
[公告标题]= A.BT,[公告入库时间]=A.XGSJ
from usrGGLJDZ A with (nolock)
left join usrZQZB B ON A.INBBM = B.INBBM AND B.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=B.IGSDM ORDER BY ZQLB) AND B.ZQLB IN (1,2)
left join usrGSCWZYZB C ON A.IGSDM = C.IGSDM AND (C.XXFBRQ=A.XXFBRQ)
where A.XGSJ>'2017-11-08 13:00:00' AND A.XGSJ<'2017-11-08 16:00:00' AND A.XXFBRQ='2017-11-08'
UNION ALL
select [公告类型]='4晚间公告',
[公告日期]=A.XXFBRQ,
[代码]=B.GPDM, [简称]=B.ZQJC,
[公告标题]= A.BT,[公告入库时间]=A.XGSJ
from usrGGLJDZ A with (nolock)
left join usrZQZB B ON A.INBBM = B.INBBM AND B.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=B.IGSDM ORDER BY ZQLB) AND B.ZQLB IN (1,2)
left join usrGSCWZYZB C ON A.IGSDM = C.IGSDM AND (C.XXFBRQ=A.XXFBRQ)
where A.XGSJ>'2017-11-08 16:00:00' AND A.XGSJ<'2017-11-08 23:59:59' AND A.XXFBRQ='2017-11-08'
order by '公告类型'
SELECT b.GPDM 公司代码,a.IGSDM,XM 姓名, ZWMS 职位描述 from usrGSZYLDRJS a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XH<>1
and ZWMS='董事长、董事'
SELECT b.GPDM 公司代码,a.IGSDM,XM 姓名, ZWMS 职位描述 from usrGSZYLDRJS a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XH<>4
and ZWMS='董事'
SELECT B.GPDM,GGG,上一期=(SELECT TOP 1 ISNULL(GGG,0) FROM dbo.usrGSGBJG WHERE IGSDM=A.IGSDM AND JZRQ<A.JZRQ ORDER BY JZRQ DESC) FROM dbo.usrGSGBJG A
JOIN usrZQZB B ON A.IGSDM=B.IGSDM AND B.ZQLB IN (1,2) AND B.ZQSC IN (83,90)
WHERE JZRQ='2017-12-31'
AND ISNULL(A.GGG,0)!=(SELECT TOP 1 ISNULL(GGG,0) FROM dbo.usrGSGBJG WHERE IGSDM=A.IGSDM AND JZRQ<A.JZRQ ORDER BY JZRQ DESC)
AND A.GBBDYYSM IS NULL
SELECT B.GPDM,CONVERT(FLOAT,A.YJYJLX)聚源类型,CONVERT(FLOAT,A.YJLRQS)利润小,CONVERT(FLOAT,A.YJLRJZ)利润大,CONVERT(FLOAT,A.YJFDQS)*100幅度小,CONVERT(FLOAT,A.YJFDJZ)*100幅度大 FROM usrYJYG A
JOIN usrZQZB B ON A.IGSDM=B.IGSDM AND B.ZQLB IN (1,2) AND B.ZQSC IN (83,90)
JOIN (SELECT C.IGSDM FROM usrSJCBSHB A
JOIN usrSJCBSHB_SL B ON A.ID=B.ID
JOIN usrZQZB C ON C.INBBM=A.INBBM
WHERE A.GGRQ='2018-4-10'
AND B.LB=1
AND A.GGLB=36
AND B.DM=210
UNION ALL
SELECT C.IGSDM FROM usrSJCBSHB A
JOIN usrZQZB C ON C.INBBM=A.INBBM
JOIN usrSJCBSHB_SL B ON A.ID=B.ID
WHERE A.GGRQ='2018-4-9'
AND A.ZQJC='下午公告'
AND B.LB=1
AND A.GGLB=36
AND B.DM=210) C ON C.IGSDM=A.IGSDM
WHERE A.YJYJND='2018-03-31'
AND A.YJYJDX=10
AND A.XXFBRQ>='2018-03-31'
AND A.YJYJLX!=10
ORDER BY 1
股东名单一致行动人说明漏填
SELECT b.GPDM 公司代码,a.GDGLGX 股东关联关系, GLGXSM 关联关系说明,YZXDSM 一致行动说明 from usrGDMD a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.JZRQ='2017-12-31'
and a.GLGXSM like '%一致行动人%'
and a.YZXDSM is null
order by b.GPDM
SELECT b.GPDM 公司代码,a.GDXH 序号,GDMD 股东名称,GDGLGX 股东关联关系, GLGXSM 关联关系说明,YZXDSM 一致行动说明 from usrGDMD a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XXFBRQ='2018-04-12'
and a.JZRQ='2017-12-31'
and a.GLGXSM is not null
and a.YZXDSM is null
order by (b.GPDM)
SELECT b.GPDM 公司代码,a.YJYJJGSM 业绩预计结果说明,YJYJLX 业绩预计类型,XXFBRQ 信息发布日期 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.YJYJJGSM like '%无重大%'
order by a.XXFBRQ desc
SELECT C.GPDM,A.* FROM dbo.usrYJYG A
LEFT JOIN dbo.usrYJYG_SL B ON A.ID=B.ID
JOIN usrZQZB C ON A.IGSDM=C.IGSDM AND C.ZQLB IN (1,2) AND ZQSC IN (83,90)
WHERE B.ID IS NULL
AND A.XXFBRQ>'2016-1-1'
and A.YJYJLX<>6
and A.YJYJLX<>10
order by A.XXFBRQ desc
SELECT b.GPDM 公司代码,a.XXFBRQ ,YJSRQS 预计收入起始, YJSRJZ 预计收入截止,YJYJNRMS 内容描述 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.YJYJNRMS like '%营业收入%'
and a.YJYJNRMS like '%增加约%'
and a.XXFBRQ>'2017-1-1'
order by a.XXFBRQ desc
SELECT b.GPDM 公司代码,a.XXFBRQ ,YJSRQS 预计收入起始, YJSRJZ 预计收入截止,YJYJNRMS 内容描述 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.YJYJDX='20'
and a.YJYJLX<>6
and a.YJYJND='2017-12-31'
and a.XXFBRQ>'2017-1-1'
and a.YJSRQS is not null
and a.YJSRJZ is not null
order by b.GPDM
SELECT b.GPDM 公司代码,a.XXFBRQ ,YJSRQS 预计收入起始, YJSRJZ 预计收入截止,YJYJNRMS 内容描述 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.YJYJDX='20'
and a.YJYJLX<>6
and a.YJYJND='2016-12-31'
and a.XXFBRQ>'2016-1-1'
and a.YJSRQS is not null
and a.YJSRJZ is not null
order by b.GPDM
财汇证券主表
TQ_OA_STCODE
join [10.1.2.99].WebResource.dbo.TQ_OA_STCODE B on B.COMPCODE=A.COMPCODE AND B.SETYPE IN (101,102)
证券代码
SELECT TOP 10 * FROM TQ_SK_SHARESTRUCHG A
join TQ_OA_STCODE B on B.COMPCODE=A.COMPCODE AND B.SETYPE IN (101,102)
ORDER BY PUBLISHDATE desc
SELECT B.GPDM FROM usrFHJDB A
JOIN usrZQZB B ON A.INBBM=B.INBBM
WHERE A.XXFBRQ='2018-5-15'
AND A.XXFBRQLX=30
AND A.SJJC=1004
order by GPDM DESC
持股数 有 有限售 无限售为空
SELECT b.GPDM 公司代码,a.CGS,a.YXSGS,a.WXSGS from usrGDMD a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.JZRQ='2018-03-31'
and a.XXLB='1'
and a.CGS is not null
and a.YXSGS is null
and a.WXSGS is null
order by (b.GPDM)
分红进度表与分红旧表决案数据核查
select A.GPDM 公司代码,A.ZQJC 证券简称,B.SGBL 送股,B.ZZGBL 转增,B.PX 税前,D.MS 进程,CONVERT(date,B.XXFBRQ) 信息日期,CONVERT(date,B.JZRQ) 截止日,B.BZ 备注
from usrFHJDB B
JOIN usrZQZB A
ON B.IGSDM=A.IGSDM and A.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=A.IGSDM ORDER BY ZQLB)AND A.ZQLB IN (1,2) AND A.SSBZ IN (1,2,6)
JOIN usrXTCLB D ON D.LB=1059 AND D.DM=B.SJJC
WHERE B.XXFBRQ>='2017-10-01' AND B.SJJC='1004' AND B.XXFBRQLX='30'
order by B.XXFBRQ DESC,B.SJJC,A.GPDM
select A.GPDM 公司代码,A.ZQJC 证券简称,B.SGBL 公司分红表送股,B.ZZGBL 转增,B.PX 派,B.BZ 备注,CONVERT(date,B.JAGBR)决案日期,CONVERT(date,B.JZRQ) 截止日
from usrGSFH B
JOIN usrZQZB A
ON B.IGSDM=A.IGSDM and A.ZQLB=(SELECT TOP 1 ZQLB
FROM usrZQZB WHERE IGSDM=A.IGSDM ORDER BY ZQLB)AND A.ZQLB IN (1,2) AND A.SSBZ IN (1,2,6)
WHERE B.SJJC='1004' AND B.YAGBR>='2017-10-01'
order by B.JAGBR DESC,B.SJJC,A.GPDM
SELECT * FROM dbo.usrZQZB
WHERE GPDM NOT LIKE '300%'
AND ZQLB IN (1,2)
AND SSZT=1
AND SSBZ=6
union all
SELECT * FROM dbo.usrZQZB
WHERE GPDM NOT LIKE '002%'
AND ZQLB IN (1,2)
AND SSZT=1
AND SSBZ=2
union all
SELECT * FROM dbo.usrZQZB
WHERE GPDM LIKE '000%'
AND ZQLB IN (1,2)
AND SSZT=1
AND SSBZ<>1
union all
SELECT * FROM dbo.usrZQZB
WHERE GPDM LIKE '600%'
AND ZQLB IN (1,2)
AND SSZT=1
AND SSBZ<>1
union all
SELECT * FROM dbo.usrZQZB
WHERE GPDM LIKE '603%'
AND ZQLB IN (1,2)
AND SSZT=1
AND SSBZ<>1
IF @SWFXSGDM not like '[0-9][0-9][0-9][0-9][0-9][0-9]'
SELECT b.GPDM 公司代码,a.SZGDZR 到账日,a.SZGSSR 上市日 from usrFHJDB a,usrZQZB b
where b.IGSDM=a.IGSDM
and (a.SGBL is not null or a.ZZGBL is not null)
and b.ZQSC in (83,90)
and b.ZQLB in (1,2)
and a.XXFBRQ>='2018-01-01'
and a.XXFBRQLX='40'
and a.SZGDZR is null
select GPDM 股票代码,d.GSSX 公司属属性,b.GDMC 实际控制人,b.CZYF 存在与否
from
usrZQZB a,usrQYSJQYGK d,usrZYGDBJJS b
where a.IGSDM=b.IGSDM
and a.IGSDM=d.IGSDM
and a.ZQSC in (83,90)
and a.ZQLB in (1,2)
and b.GDXH=9
and b.GDMC like '%无实际控制人%'
and b.CZYF=1
and d.GSSX<>16
查询当天业绩预告公告
SELECT b.GPDM 公司代码,a.YJYJLX 业绩预计类型,a.YJFDQS 预计幅度起始,a.YJFDJZ 预计幅度截止,
a.YJLRQS/'10000' 预计利润起始,a.YJLRJZ/'10000' 预计利润截止,a.XXFBRQ 信息发布日期 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XXFBRQ>='2018-7-13'
and a.YJYJDX='10'
and a.YJYJLX<>'10'
and a.YJYJND='2018-6-30'
order by b.GPDM
查询前天下午公告业绩预告公告
SELECT b.GPDM 公司代码,a.YJYJLX 业绩预计类型,a.YJFDQS 预计幅度起始,a.YJFDJZ 预计幅度截止,
a.YJLRQS/'10000' 预计利润起始,a.YJLRJZ/'10000' 预计利润截止,a.XXFBRQ 信息发布日期 from usrYJYG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XXFBRQ='2018-7-12'
and a.YJYJDX='10'
and a.YJYJLX<>'10'
and a.YJYJND='2018-6-30'
and a.XGSJ>='2018-7-12'
order by b.GPDM
--友商数据--
select DISTINCT B.SYMBOL 证券代码,B.SESNAME 证券简称,Q.PARENETP 净利润,E.NPCUT 扣除非经常性损益的净利润,
A.MANANETR 经营活动产生的现金流量净额,F.TOTASSET 资产总计,F.PARESHARRIGH 股东权益,
E.EPSBASIC 基本每股,E.EPSFULLDILUTED 稀释每股,E.EPSBASICEPSCUT 基本扣除,E.EPSFULLDILUTEDCUT 稀释扣除,
E.ROEDILUTED 净资产收益率摊薄,E.ROEWEIGHTED 净资产收益率加权,E.ROEDILUTEDCUT 净资产收益率扣除摊薄,E.ROEWEIGHTEDCUT 净资产收益率扣除加权,
E.OPNCFPS 每股经营活动产生的现金流量净额,E.NAPS 每股净资产,
A.PUBLISHDATE 信息发布日期,A.ENDDATE 报表日期
from [10.1.2.99].WebResource.dbo.TQ_FIN_PROCFSTATEMENTNEW A
join [10.1.2.99].WebResource.dbo.TQ_SK_BASICINFO B on B.COMPCODE=A.COMPCODE AND B.SETYPE IN (101,102)
left join [10.1.2.99].WebResource.dbo.TQ_FIN_PROFINMAININDEX E ON E.COMPCODE =A.COMPCODE AND A.ENDDATE= E.ENDDATE AND E.REPORTTYPE=3
left join [10.1.2.99].WebResource.dbo.TQ_FIN_PROBALSHEETNEW F ON F.COMPCODE =A.COMPCODE AND A.ENDDATE= F.ENDDATE AND F.REPORTTYPE=3
left join [10.1.2.99].WebResource.dbo.TQ_FIN_PROINCSTATEMENTNEW Q ON Q.COMPCODE =A.COMPCODE AND A.ENDDATE= Q.ENDDATE AND Q.REPORTTYPE=3
where A.ENDDATE='20180630' AND A.REPORTTYPE=1
order by A.PUBLISHDATE DESC,B.SYMBOL
发行审核发行申报稿预披露日有误
SELECT b.GPDM 公司代码,a.FXSBGYPLR,a.FXR,a.SHHYZKR from usrFXSH a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.SHHYZKR>'2018-1-1'
and a.FXSBGYPLR is not null
order by (a.SHHYZKR) desc
半年报股本结构漏处理
SELECT DISTINCT [股票代码]=A.GPDM,[证券简称]=A.ZQJC FROM usrZQZB A
JOIN ( SELECT IGSDM FROM usrZQZB where IGSDM IN (SELECT IGSDM FROM usrGSCWZYZB B
where B.JZRQ='2018-06-30' and B.XXFBRQ='2018-8-21'
and IGSDM NOT IN(SELECT IGSDM FROM usrGSGBJG where JZRQ=B.JZRQ and GKBZ=3)) )X
ON X.IGSDM=A.IGSDM and A.ZQLB=(SELECT TOP 1 ZQLB FROM usrZQZB where IGSDM=X.IGSDM ORDER BY ZQLB) and A.ZQLB IN (1,2,11)
股本结构总股本和财务资产负债表总股本比对
SELECT b.GPDM 公司代码,a.ZGB 总股本,c.GB 实收资本或股本,ABS(a.ZGB-c.GB) from usrGSGBJG a,usrZQZB b,usrCWBBZCFZB c
where b.IGSDM=a.IGSDM and b.IGSDM=c.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.XXFBRQ<='2018-08-27'
and a.JZRQ='2018-06-30'
and c.JZRQ='2018-06-30'
and c.XXFBRQ<='2018-08-27'
and c.HBBZ='1'
and ABS(a.ZGB-c.GB)<>0
order by b.GPDM
SELECT b.GPDM,b.ZQJC 公司代码,a.* from usrSJCBSHB a,usrZQZB b
where b.INBBM=a.INBBM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.GGRQ>='2018-8-30'
and a.CLBM='112'
and (a.HCRY is null or a.HCRY='0')
and (a.GGLB='5'or a.GGLB='7'or a.GGLB='8' or a.GGLB='9'or a.GGLB='10' or a.GGLB='47' or a.GGLB='23' or a.GGLB='29'
or a.GGLB='25' or a.GGLB='26' or a.GGLB='27' or a.GGLB='28' )
领导人持股职位比对
SELECT C.GPDM 公司代码,A.LDXM,A.ZWMS,D.MS FROM dbo.usrZYLDRCG A
JOIN dbo.usrZYLDRCG_SL B ON A.ID=B.ID
JOIN usrZQZB C ON A.IGSDM=C.IGSDM AND C.ZQLB IN (1,2) AND ZQSC IN (83,90)
JOIN usrXTCLB D ON D.LB=1182 AND D.DM=B.DM
WHERE A.ZWMS IS NOT EXIST
AND A.XXFBSJ>'2018-08-1'
AND A.JZRQ='2018-06-30'
and len(A.ZWMS)>2
order by C.GPDM
提取CH业绩预告
and not exists(select 1 from [10.102.0.99].WebResource.dbo. TQ_OA_STCODE a, [10.102.0.99].WebResource.dbo. TQ_SK_EXPTPERFORMANCE b
where a.SETYPE ='101' AND a.ISVALID='1'AND b.ISVALID='1'
and a.COMPCODE=b.COMPCODE
and c.GPDM= a.SYMBOL
and CONVERT(varchar(8), d.XXFBRQ, 112) =CONVERT(varchar(8), b.PUBLISHDATE, 112)
and CONVERT(varchar(8), SESSIONENDDATE, 112)= CONVERT(varchar(8), d.YJYJND, 112))
order by 2 desc,1
select distinct GPDM 股票代码,c.XXFBRQ 信息发布日期,c.YJYJJGSM,C.OPERATORNAME 修改人员
from
usrZQZB a,usrGSCWZYZB b,usrYJYG c
join cmdOPERATORS C on C.OPERATORID=c.XGRY
where a.IGSDM=b.IGSDM
and c.IGSDM=b.IGSDM
and a.ZQSC in (83,90)
and a.ZQLB in (1,2)
and c.YJYJJGSM like '%可能%'
and c.YJYJLX=2
and c.XXFBRQ >'2018-1-1'
每日股本比对
SELECT b.GPDM 证券代码 ,CONVERT(varchar(8), a.JZRQ, 112) ,a.YXSGF 有限售股份 from usrGSGBJG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC in (83,90)
and b.ZQLB in (1,2)
order by JZRQ DESC
主营业务与产品-经营业务与行业更改状况--------交易所行业划分
SELECT b.GPDM 公司代码,a.HYLB 行业类别 from usrGSYWYHYGG a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and b.SSZT in (1)
and a.XXFBRQ=(SELECT TOP 1 XXFBRQ
FROM usrGSYWYHYGG WHERE IGSDM=a.IGSDM ORDER BY XXFBRQ desc)
order by GPDM
SELECT b.GPDM 公司代码,c.SSHY 所属行业 from usrJYSHYHFB c,usrZQZB b
where c.IGSDM=b.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and b.SSZT in (1)
and c.HYHFBZ in (22)
and c.SFZX=1
order by GPDM
股东关联关系里的内容跟序号不符合
select GPDM 股票代码,b.XXFBRQ 信息发布日期,b.GDMD 股东名单,b.GDGLGX,b.GDXH
from usrZQZB a,usrGDMD b
where a.IGSDM=b.IGSDM
and a.ZQSC in (83,90)
and a.ZQLB in (1,2)
and b.JZRQ='2018-9-30'
and b.GDGLGX not like CONCAT('%', b.GDXH,'%')
and b.GDGLGX not like '%-%'
股东关联关系但是对于的上一个序号SQL里没记录
select GPDM 股票代码,b.XXFBRQ 信息发布日期,c.GDMD 股东名单,b.GDGLGX,c.GDXH,c.XXLB,c.GDGLGX,C.OPERATORNAME 修改人员
from usrZQZB a,usrGDMD b,usrGDMD c
join cmdOPERATORS C on C.OPERATORID=c.XGRY
where a.IGSDM=b.IGSDM
and c.IGSDM=b.IGSDM
and a.ZQSC in (83,90)
and a.ZQLB in (1,2)
and b.JZRQ='2018-9-30'
and c.JZRQ='2018-9-30'
and b.GDGLGX like CONCAT('%', c.GDXH,'%')
and c.GDGLGX is null
and c.GDXH<>10
and c.XXLB<>2
and b.GDGLGX not like '%2%'
and c.GDXH<>1
新股发行股份余额bug
WITH AAA AS
(SELECT b.GPDM 公司代码,a.FXL 发行量,a.FXL 上网发行股数,SWFXRGDW 上网发行认购单位,ZGSMSFBRQ 招股说明书发布日期,YGBXSLWSSG 余股包销数量网上申购,
CONVERT(decimal(18,4),ABS(a.FXL/a.SWFXRGDW)) 商,CONVERT(decimal(18,2),SUBSTRING(CONVERT(CHAR,ABS(a.FXL/a.SWFXRGDW)),1,CHARINDEX('.',CONVERT(CHAR,ABS(a.FXL/a.SWFXRGDW)))-1)) 整数,
CONVERT(decimal(18,4),ABS(a.FXL/a.SWFXRGDW))-CONVERT(decimal(18,2),SUBSTRING(CONVERT(CHAR,ABS(a.FXL/a.SWFXRGDW)),1,CHARINDEX('.',CONVERT(CHAR,ABS(a.FXL/a.SWFXRGDW)))-1)) 小数部分
from usrXGFXYSS a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.ZGSMSFBRQ>'2017-01-01'
and a.FRPSGS is null
and a.FXL is not null)
SELECT*
FROM AAA
WHERE 小数部分<>0
select * from cmdPROGRAMS where PROGID='10624'
-------年报主要指标提取
select A.GPDM 公司代码,A.ZQJC 证券简称,B.JBMGSY 基本每股收益,B.XSMGSY 稀释每股收益,B.JBMGSYKC 基本每股收益扣除,B.XSMGSYKC 稀释扣除,
B.JLRJZCSYLTBPL*100 净资产收益率原始披露,B.JLRJZCSYLJQ*100 净资产收益率加权,
B.KCHJLRJZCSYLTB*100 净资产收益率摊薄扣除,B.KCHJLRJZCSYLJQ*100 净资产收益率加权扣除,
B.JLR 归属母公司股东的净利润,B.FJCXSY 非经常性损益,B.KCFJYXSYHDJLR 扣除后净利,
B.JYXJLLJE 经营活动现金流量净额,B.MGJYXJLLJE 每股经营活动现金流量净额,B.ZCZE 总资产,B.GDQY 归属母公司股东权益,
B.MGJZCPL 每股净资产原始披露,
B.XXFBRQ from usrGSCWZYZB B
JOIN usrZQZB A ON B.IGSDM=A.IGSDM and A.ZQLB=(SELECT TOP 1 ZQLB FROM usrZQZB WHERE IGSDM=A.IGSDM ORDER BY ZQLB)
WHERE B.GGLB=20 AND B.XXFBRQ>='2019-01-01' AND B.JZRQ='2018-12-31' and A.ZQLB IN (1,2) AND A.SSBZ IN (1,2,6) AND B.RQBZ=3
order by B.XXFBRQ DESC,A.GPDM
-------分红比对
WITH AAA AS
(SELECT B.SYMBOL 证券代码,B.SESNAME 证券简称,A.PROBONUSRT 送股,A.TRANADDRT 转增,A.PRETAXCASHMAXDVCNY 税前派,A.AFTTAXCASHDVCNY 税后派,A.EQURECORDDATE 股权登记日,A.XDRDATE 除权除息日,A.SHARRDATE 股份到帐日,
A.LISTDATE 上市日,A.CASHDVARRBEGDATE 红利发放日,A.SHCAPBASEQTY/10000 股本基数,A.TOTCASHDV 分红金额
,CONVERT(VARCHAR(10),A.UPDATEDATE,23) 更新日期,CONVERT(VARCHAR(10),DATEADD(DAY,-11,GETDATE()),23) 相隔11天
FROM [10.102.0.99].WebResource.dbo.TQ_SK_DIVIDENTS A
JOIN
(SELECT COMPCODE,SYMBOL,SESNAME FROM [10.102.0.99].WebResource.dbo.TQ_OA_STCODE WHERE SETYPE IN (101,102)) B ON A.COMPCODE=B.COMPCODE AND A.XDRDATE <>19000101
WHERE CONVERT(VARCHAR(10),A.UPDATEDATE,23)>=CONVERT(VARCHAR(10),DATEADD(DAY,-11,GETDATE()),23)),
BBB AS
(SELECT GPDM 股票代码JY,ZQJC 证券简称JY,XXFBRQ 信息发布日期,SGBL 送股JY,ZZGBL 转增JY,PX 税前JY,SP 税后JY,
股权登记日JY=convert(char(10),GQDJR,23),
除权除息日JY=convert(char(10),CQCXR,23),
送转股到帐日JY=convert(char(10),SZGDZR,23),
送转股上市日JY=convert(char(10),SZGSSR,23),
红利发放日JY=convert(char(10), GXDZRQ,23),
FHGBJS/10000 分红基数JY,HJPXJE/10000 合计派现JY ,CONVERT(VARCHAR(10),DATEADD(DAY,-10,GETDATE()),23) 相隔10天
FROM usrFHJDB C
JOIN
(SELECT IGSDM,GPDM,ZQJC FROM dbo.usrZQZB WHERE ZQSC IN(83,90) AND ZQLB IN(1,2) AND SSBZ IN(1,2,6))D ON C.IGSDM=D.IGSDM AND SJJC=3131
WHERE C.XXFBRQ>=CONVERT(VARCHAR(10),DATEADD(DAY,-10,GETDATE()),23))
SELECT 证券代码,证券简称,信息发布日期,送股,送股JY,送股-送股JY 送股差异,转增,转增JY,转增-转增JY 转增差异,股本基数,
税前派,税前JY,税前派-税前JY 税前差异,税后派,税后JY,税后派-税后JY 税后差异,股权登记日,股权登记日JY,DATEDIFF(DAY,股权登记日,股权登记日JY) 登记日差异,
除权除息日,除权除息日JY,DATEDIFF(DAY,除权除息日,除权除息日JY) 除权日差异,股份到帐日,送转股到帐日JY,DATEDIFF(DAY,股份到帐日,送转股到帐日JY) 到账日差异,上市日,送转股上市日JY,DATEDIFF(DAY,上市日,送转股上市日JY) 上市日差异,
红利发放日,红利发放日JY,DATEDIFF(DAY,红利发放日,红利发放日JY) 红利发放日差异,股本基数,分红基数JY,股本基数-分红基数JY 基数差异,分红金额,合计派现JY,分红金额-合计派现JY 派现金额差异
FROM AAA JOIN BBB ON AAA.证券代码=BBB.股票代码JY
WHERE (送股-送股JY<>0 OR 转增-转增JY<>0 OR 税前派-税前JY<>0 OR 税后派-税后JY<>0 OR DATEDIFF(DAY,股权登记日,股权登记日JY)<>0 OR DATEDIFF(DAY,除权除息日,除权除息日JY)<>0 OR DATEDIFF(DAY,股份到帐日,送转股到帐日JY)<>0 OR
DATEDIFF(DAY,上市日,送转股上市日JY)<>0 OR DATEDIFF(DAY,红利发放日,红利发放日JY)<>0 OR 股本基数-分红基数JY<>0 OR 分红金额-合计派现JY<>0)
order by 信息发布日期 desc
股东名单导入错误代码
SELECT 股票代码,股东名单,计数,截止日期
from
(select distinct c.GPDM 股票代码,a.GDMD 股东名单,COUNT(a.GDMD) OVER(PARTITION BY c.GPDM,a.GDMD) 计数,a.JZRQ 截止日期
from usrGDMD a,usrZQZB c,usrGDMD b
where a.IGSDM=c.IGSDM and a.XXLB<>4 and a.GDXH<20
and b.IGSDM=c.IGSDM
and b.GDMD<>a.GDMD
and c.ZQSC in (83,90)
and c.ZQLB in (1,2)
and b.GDXH=a.GDXH
and a.XXFBRQ>'2018-01-01') A
WHERE 计数<2
主要指标更正公告查漏
select b.XXFBRQ,a.IGSDM 企业编号,a.GPDM 股票代码,a.ZQJC 股票简称,b.JZRQ 截止日期,'1-资产负债表' as 相关库表 from usrZQZB a
join usrCWBBZCFZB b on b.IGSDM = a.IGSDM
where not exists (select * from usrGSCWZYZB c where c.IGSDM = a.IGSDM and c.JZRQ = b.JZRQ and b.XXFBRQ=c.XXFBRQ)
and b.XXFBRQ>'2019-01-1'
and a.ZQLB in (1,2)
and a.SSBZ <> 3
and a.GPDM not like 'X%'
union
select b.XXFBRQ,a.IGSDM 企业编号,a.GPDM 股票代码,a.ZQJC 股票简称,b.JZRQ 截止日期,'1-利润分配表' as 相关库表 from usrZQZB a
join usrCWBBLRFPB b on b.IGSDM = a.IGSDM
where not exists (select * from usrGSCWZYZB c where c.IGSDM = a.IGSDM and c.JZRQ = b.JZRQ and b.XXFBRQ=c.XXFBRQ)
and b.XXFBRQ>'2019-01-1'
and a.ZQLB in (1,2)
and a.SSBZ <> 3
and a.GPDM not like 'X%'
union
select b.XXFBRQ,a.IGSDM 企业编号,a.GPDM 股票代码,a.ZQJC 股票简称,b.JZRQ 截止日期,'1-现金流量表' as 相关库表 from usrZQZB a
join usrCWBBXJLLB b on b.IGSDM = a.IGSDM
where not exists (select * from usrGSCWZYZB c where c.IGSDM = a.IGSDM and c.JZRQ = b.JZRQ and b.XXFBRQ=c.XXFBRQ)
and b.XXFBRQ>'2019-01-20'
and a.ZQLB in (1,2)
and a.SSBZ <> 3
and a.GPDM not like 'X%'
order by a.IGSDM,相关库表
蛇皮抓取的使用境外会计准则里面披露的每股净资产！！！
SELECT b.GPDM 公司代码,b.ZQJC 中文名称,a.MGJZCPL 每股净资产原始披露,a.MGJZC 每股净资产,
abs(a.GDQY/a.ZGB)计算值,abs(a.MGJZCPL-a.MGJZC)差异 from usrGSCWZYZB a,usrZQZB b
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.JZRQ='2018-12-31'
and a.MGJZCPL is not null
and a.GGLB=20
and abs(a.MGJZCPL-a.MGJZC)>0.01 or abs(a.MGJZCPL-a.MGJZC)<-0.01
order by 差异 desc
and b.ZWMC not like '%银行%'
企业概况和公司概况，成立日期不同
SELECT b.GPDM 公司代码,CONVERT(VARCHAR(10),a.CLRQ,120) 成立日期,CONVERT(VARCHAR(10),c.GSCLRQ,120) 公司成立日期 ,
CONVERT(VARCHAR(10),c.SCZCDJRQ ,120)首次注册登记日期,CONVERT(VARCHAR(10),c.BGSLRQ,120) 变更设立日期
from usrQYSJQYGK a,usrZQZB b,usrGSGK c
where b.IGSDM=a.IGSDM
and b.ZQSC IN (83,90)
and b.ZQLB in (1,2)
and a.IGSDM=c.IGSDM
and a.CLRQ<>c.GSCLRQ
营业部名称相同，营业部代码不同
WITH AAA AS
(SELECT YYBMC,YYBDM
FROM usrJYXW
WHERE JYRQ >'2016-01-01' AND YYBMC<>'机构专用')
SELECT DISTINCT B.YYBMC,B.YYBDM
FROM
(SELECT YYBMC,YYBDM
FROM usrJYXW
WHERE JYRQ >'2016-01-01' AND YYBMC<>'机构专用')B
LEFT JOIN AAA ON B.YYBMC=AAA.YYBMC
WHERE B.YYBDM<>AAA.YYBDM
--------T-2期主要指标比对
SELECT GPDM,ZQJC,A.XXFBRQ,A.JZRQ,B.XXFBRQ,B.JZRQ,ABS(A.JBMGSY-B.JBMGSY)基本每股收益差异,ABS(A.JLR-B.JLR) 净利润差异,ABS(A.ZCZE-B.ZCZE)资产总计差异,ABS(A.FJCXSY-B.FJCXSY)非经常性损益差异
FROM(SELECT IGSDM,JZRQ,XXFBRQ,ROW_NUMBER() OVER (PARTITION BY IGSDM,JZRQ ORDER BY XXFBRQ DESC) 排位,
JBMGSY,XSMGSY,JBMGSYKC,XSMGSYKC,JLRJZCSYLTBPL,JLRJZCSYLJQ,KCHJLRJZCSYLTB,KCHJLRJZCSYLJQ,
JLR,FJCXSY,KCFJYXSYHDJLR,JYXJLLJE,MGJYXJLLJE,ZCZE,GDQY,MGJZCPL FROM usrGSCWZYZB)A
LEFT JOIN (SELECT IGSDM,JZRQ,XXFBRQ,ROW_NUMBER() OVER (PARTITION BY IGSDM,JZRQ ORDER BY XXFBRQ DESC)-1 排位,
JBMGSY,XSMGSY,JBMGSYKC,XSMGSYKC,JLRJZCSYLTBPL,JLRJZCSYLJQ,KCHJLRJZCSYLTB,KCHJLRJZCSYLJQ,
JLR,FJCXSY,KCFJYXSYHDJLR,JYXJLLJE,MGJYXJLLJE,ZCZE,GDQY,MGJZCPL FROM usrGSCWZYZB)B
ON A.IGSDM=B.IGSDM AND A.JZRQ=B.JZRQ AND A.排位=B.排位
JOIN(SELECT IGSDM,GPDM,ZQJC FROM dbo.usrZQZB
WHERE ZQSC IN(83,90)
AND ZQLB IN(1,2)
AND SSBZ IN(1,2,6))C
ON A.IGSDM=C.IGSDM
WHERE B.IGSDM IS NOT NULL AND A.XXFBRQ>='2019-03-23' AND A.JZRQ='2016-12-31'
ORDER BY GPDM
查询调整后分红方案
select B.GPDM,A.XXFBRQ from usrFHJDB A
join usrZQZB B on A.IGSDM=B.IGSDM AND B.ZQSC IN (83,90) AND B.ZQLB IN (1,2)
JOIN usrGSGBJG C ON A.IGSDM=C.IGSDM and C.JZRQ=(SELECT TOP 1 JZRQ FROM usrGSGBJG WHERE IGSDM=C.IGSDM ORDER BY JZRQ DESC)
WHERE C.ZGB<>A.FHGBJS AND A.XXFBRQ>='2019-07-05'
AND A.SJJC=3131
ORDER BY B.GPDM
法人配售查漏
SELECT GPDM 股票代码,b.TZZMC 投资者名称 FROM usrXGXJMX b ,usrZQZB c
WHERE
c.IGSDM=b.IGSDM
AND b.ZSJSGGS IS NOT null
and c.ZQSC in (83,90)
and c.ZQLB in (1,2)
AND NOT exists(select 1 from usrGPPS a where a.IGSDM=b.IGSDM AND a.PSYY=2 )
