老平台数据导出的SQL

老平台导出数据 sql

===============================



## 1. 用户信息

-------------------------------



### 1.1. 个人

-------------------------------

```sql

/*

查询个人用户信息：包含4个状态：注册后未实名、提交个人实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.name as '姓名',p.sex as '性别',p.birthday as '出生年月',p.email as '邮箱',

	p.province as '省-代码',dm1.name as '所在地区-省',p.city as '市-代码',dm2.name as '所在地区-市',

	p.company as '所在单位',

	p.id_num as '身份证号码',

	a1.id as '身份证信息面','http://www.jstec.com.cn:81/platform/' + a1.path + a1.real_name  as '身份证信息面url',

	a2.id as '身份证国徽面','http://www.jstec.com.cn:81/platform/' + a2.path + a2.real_name  as '身份证国徽面url',

	p.personal_intro as '个人简介',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_person p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a1 on a1.id = p.id_front_img

LEFT JOIN t_attachment a2 on a2.id = p.id_back_img

WHERE lp.type is null or lp.type ='PERSON' ORDER BY lp.create_date DESC

```



### 1.2. 企业

-------------------------------

```sql

/*

查询企业用户信息：包含3个状态：提交企业实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.name as '企业名称',p.code as '企业代码',

	p.province as '省-代码',dm1.name as '企业行政区域-省',p.city as '市-代码',dm2.name as '企业行政区域-市',

	p.email as '企业邮箱',p.address as '企业联系地址',

	p.legal_person as '企业法人代表',p.contact_person as '企业联系人',p.contact_person_tel as '联系人电话',

	p.agency_intro as '企业简介',

	a.id as '营业执照或组织机构代码照片','http://www.jstec.com.cn:81/platform/' + a.path + a.real_name as '营业执照或组织机构代码照片url',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_agency p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a on a.id = p.licence_img

WHERE lp.type ='AGENCY' ORDER BY lp.create_date DESC

```



### 1.3. 机构

-------------------------------

```sql

/*

查询机构用户信息：包含3个状态：提交实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.name as '机构名称',p.type as '机构类型',

	p.province as '省-代码',dm1.name as '机构所在地-省',p.city as '市-代码',dm2.name as '机构所在地-市',

	p.email as '机构邮箱',p.address as '机构联系地址',

	p.contact_person as '机构联系人',p.contact_person_tel as '联系人电话',

	p.org_code as '组织机构代码',

	a.id as '组织机构代码照片','http://www.jstec.com.cn:81/platform/' + a.path + a.real_name as '组织机构代码照片url',

	p.org_intro as '机构简介',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_organisation p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a on a.id = p.org_code_img

WHERE lp.type in('ORGANISATION','FINANCE_ORG','AGENCY_ORG') ORDER BY lp.create_date DESC

```



### 1.4. 高校

-------------------------------

```sql

/*

查询大院大所用户信息：包含3个状态：提交企业实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.company_name as '公司名称',p.org_code as '组织机构代码', p.type as '组织机构类型',

	p.province as '省-代码',dm1.name as '企业行政区域-省',p.city as '市-代码',dm2.name as '企业行政区域-市',

	p.contact_person as '联系人',p.telephone as '电话',p.email as '邮箱',p.address as '地址',

	p.intro as '企业简介',

	'http://www.jstec.com.cn:81/platform/' + a.path + a.real_name as '组织机构代码照片',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_bcbg p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a on a.id = p.org_code_img

WHERE lp.type ='BCBG_SCHOOL_ORG' ORDER BY lp.create_date DESC

```



### 1.5. 专家

-------------------------------

```sql

/*

查询专家用户信息：包含3个状态：提交实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.name as '姓名',p.sex as '性别',p.birthday as '出生年月',p.email as '邮箱',

	p.province as '省-代码',dm1.name as '所在地区-省',p.city as '市-代码',dm2.name as '所在地区-市',

	p.company as '所在单位',p.tech_field as '技术领域',

	p.id_num as '身份证号码',

	a1.id as '身份证信息面','http://www.jstec.com.cn:81/platform/' + a1.path + a1.real_name  as '身份证信息面url',

	a2.id as '身份证国徽面','http://www.jstec.com.cn:81/platform/' + a2.path + a2.real_name  as '身份证国徽面url',

	p.professionalIntro as '专家简介',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_professional p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a1 on a1.id = p.id_front_img

LEFT JOIN t_attachment a2 on a2.id = p.id_back_img

WHERE lp.type ='PROFESSIONAL' ORDER BY lp.create_date DESC

```



### 1.6. 技术转移服务人才

-------------------------------

```sql

/*

查询技术转移服务人才用户信息：包含3个状态：提交个人实名认证、实名实名认证通过、实名认证未通过

*/

SELECT lp.id as 'ID',lp.login_name as '用户名',lp.password as '加密后密码',

	lp.type as '用户类型',lp.approve as '认证审核',lp.approve_result as '审核结果',

	p.name as '姓名',p.sex as '性别',p.birthday as '出生年月',

	p.province as '省-代码',dm1.name as '所在地区-省',p.city as '市-代码',dm2.name as '所在地区-市',

	p.company as '所在单位',p.tech_field as '技术领域',p.type as '人才类别',

	p.id_num as '身份证号码',

	a1.id as '身份证信息面','http://www.jstec.com.cn:81/platform/' + a1.path + a1.real_name  as '身份证信息面url',

	a2.id as '身份证国徽面','http://www.jstec.com.cn:81/platform/' + a2.path + a2.real_name  as '身份证国徽面url',

	p.talentIntro as '个人简介',

	lp.create_date as '创建时间',

	lp.lastlogintime as '最后登录时间',

	lp.is_available as '是否逻辑删除'

FROM  t_login_party lp 

LEFT JOIN  t_party_talent p on lp.id = p.login_party_id

LEFT JOIN t_dynamic_menu dm1 on p.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on p.city = dm2.code

LEFT JOIN t_attachment a1 on a1.id = p.id_front_img

LEFT JOIN t_attachment a2 on a2.id = p.id_back_img

WHERE lp.type ='TALENT' ORDER BY lp.create_date DESC

```



## 2. 成果

-------------------------------

```sql

select trd.id 成果表id,

trd.result_name 成果名称,

trd.results_details_type 成果类型,

trdc.publisher_id 发布者,

trdc.belongs_to_type 成果权属,

trdc.belongs_to_name 个人单位名称,

trd.technical_field 所属领域,

dm1.name 省,

dm2.name 市,

trd.district 区,

trdc.industry_label 产业标签,

trdc.technical_status 技术状态,

trd.suit_area 适用领域,

trdc.technical_remarks 技术介绍,

trd.intellectual_property 知识产权情况,

trd.patent_no 专利号,

trdcp.patent_category 专利类别,

trd.filing_date 申请日,

trd.patent_date 授权日,

trd.technical_cooperation_mode 合作方式,

trd.price_category 价格类别,

trd.price 价格,

trd.attachment_id 附件,

trdc.has_demo 样品情况,

trdc.infor_category 信息类别,

trdc.effective_end_day 信息有效时间,

trd.linkman 联系人,

trd.phone 联系电话,

trd.email 邮箱,

trd.label 自定义标签,

trd.attachment_id 图片id,

trd.create_date 创建时间,

trd.approve 是否审核通过,

trd.approve_result 审核结果,

trd.status 状态,

trd.publisher_id 发布人id,

trd.is_available 逻辑删除flag

from t_results_details trd left join

t_results_details_common trdc on trd.id=trdc.id 

left join t_results_details_common_patent trdcp on trdcp.results_details_common_id=trdc.id

left join t_login_party lp on lp.id = trdc.publisher_id

LEFT JOIN t_party_person pp on lp.id = pp.login_party_id

LEFT JOIN t_dynamic_menu dm1 on pp.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on pp.city = dm2.code

order by trd.create_date desc

```







## 3. 专利

-------------------------------

```sql

select rdp.id as 'ID',rdp.patent_name as '专利名称',rdp.patent_type as '国内/国际专利',

	rdp.patent_no as '专利号',rdp.patent_grant_name as '专利权人',

	rdp.application_date as '申请日',rdp.grant_date as '授权日',

	rdp.legal_status as '法律状态',rdp.industry_classification as '行业分类',

	rdp.price_category as '意向价格类型: 面议/设置',rdp.price as '意向价格',

	rdp.patent_category as '专利类别',rdp.ipc_category as 'IPC分类',dm.name as 'IPC定义',

	rdp.has_pct as '是否有PCT选项',rdp.pct_remarks as 'PCT说明',

	rdp.patent_cooperation_type as '合作方式',rdp.patent_cooperation_remarks as '合作方式（其他说明）',

	rdp.patent_remarks as '专利摘要',

	rdp.infor_category as '信息类别(是否永久有效)',rdp.effective_end_day as '信息有效期时间',

	rdp.contact as '联系人',rdp.telephone as 'telephone',rdp.email as '邮箱',

	rdp.pic_id as '图片','http://www.jstec.com.cn:81/platform/' + a1.path + a1.real_name  as '图片url',

	rdp.status as '上下架状态',rdp.approve as '审核状态',rdp.trade_resourse_status as '交易资源状态',

	rdp.publisher_id as '发布人ID',rdp.is_available as '是否逻辑删除',

	rdp.data_source, rdp.original_id

FROM t_results_details_patent rdp

LEFT JOIN t_dynamic_menu dm on dm.code=rdp.ipc_category 

LEFT JOIN t_attachment a1 on a1.id = rdp.pic_id

WHERE rdp.ipc_category is null or (rdp.ipc_category is not null and dm.path like 'IPC%')

```





## 4. 需求

-------------------------------

```sql

SELECT  a.id AS '需求id',a.name AS '需求名称',

	a.description AS '需求描述',a.status AS '需求状态',

	a.is_available AS '是否逻辑删除',a.approve AS '需求是否通过',a.approve_result AS '未通过原因',

	a.label AS '标签',b.requirement_name AS '需求简介',

	b.technical_field AS '技术领域一级',c.application_field AS '应用领域',

	a.demand_type AS '需求类型',a.price AS '需求价格',c.investment_amount AS '交易金额',

	b.effective_end_day AS '信息有效期',

	c.unit_name AS '单位名称',

	a.linkman AS '联系人',a.phone AS '联系电话',a.email AS '邮箱',

	b.contact_person AS '联系人1',b.contact_phone AS '联系电话1',b.email AS '邮箱1',

	a.address AS '地址',

	a.profession_requirement AS '专业需求',a.[position]AS '职位',

	b.publisher_id AS '发布者',

	d.province AS '成果-省代码',d.city AS '成果-市代码',d.district AS '成果-区代码',

	dm1.name AS '省',dm2.name AS '市',

	c.result_details_industry_field AS '成果-产业领域',

	c.result_details_technical_phase AS '成果-技术需求所处阶段',

	c.result_details_cooperation_type AS '成果-合作方式',

	b.effective_end_day AS '成果-信息有效期',

	c.patent_legal_status AS '专利- 法律状态',

	c.patent_category AS '专利-专利类别',

	c.patent_cooperation_type AS '专利-合作方式',

	c.trademark_classification AS '商标-商标分类',

	a.create_date AS '需求创建时间',

	a.attachment_id AS '图片id',a.first_pic_id AS '需求第一张id',a.options AS '附件id'

FROM t_requirement a

LEFT JOIN t_requirement_common b ON a.id = b.id

LEFT JOIN t_requirement_property c ON a.id = c.req_id

LEFT JOIN t_results_details_common d ON a.id = d.id

LEFT JOIN t_dynamic_menu dm1 on d.province = dm1.code

LEFT JOIN t_dynamic_menu dm2 on d.city = dm2.code

WHERE a.demand_type = 'TECHNOLOGY' and b.demand_type = 'TECHNOLOGY'

ORDER BY a.create_date DESC

```





## 5. 资讯

-------------------------------

说明：本文内容由DogJay收集整理或者自己编写，如果存在侵权，请联系站长删除。

吐槽(0)