老平台数据导出的SQL
DogJay
2018-06-13
【面试经验】
79人已围观
老平台导出数据 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. 资讯
-------------------------------
吐槽(0)
上一篇:编写多线程程序有几种实现方式?
下一篇:老平台需求汇总表SQL