成果表（产业标签为中文）
DogJay
2018-07-24
【数据库】
166人已围观
版本v1.0
```sql
SELECT
trdc.id 成果表id,
trdc.name 成果名称,
trdc.publisher_id 发布者,
trdc.belongs_to_type 成果权属,
trdc.belongs_to_name 个人单位名称,
trdc.technical_field 所属领域,
trdc.district 区,
--trdc.industry_label 产业标签,
dm3.name 产业标签,
trdc.technical_status 技术状态,
trdc.applicable_field 适用领域,
trdc.technical_remarks 技术介绍,
trdcp.patent_category 专利类别,
trdcp.patent_no 专利号,
trdcp.application_date 授权日,
trdc.cooperation_type 合作方式,
trdc.price_type 价格类别,
trdc.price 价格,
trdc.attachment_id 附件,
trdc.has_demo 样品情况,
trdc.infor_category 信息类别,
trdc.effective_end_day 信息有效时间,
trdc.linkman 联系人,
trdc.telephone 联系电话,
trdc.email 邮箱,
trdc.label 自定义标签,
trdc.attachment_id 图片id,
trdc.create_date 创建时间,
trdc.approve 审核结果,
trdc.status 状态,
trdc.is_available 逻辑删除flag,
trdc.data_source 信息来源
FROM
t_results_details_common trdc
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
LEFT JOIN t_dynamic_menu dm3 ON trdc.industry_label = ','+dm3.code
WHERE
trdc.data_source != 'leading-in-2016-11-4'
```
版本v2.0
```sql
SELECT
trdc.id 成果表id,
trdc.name 成果名称,
trdc.publisher_id 发布者,
trdc.belongs_to_type 成果权属,
trdc.belongs_to_name 个人单位名称,
trdc.technical_field 所属领域,
trdc.district 区,
trdc.industry_label AS 产业标签Code,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.industry_label, 2, 5)
) 产品标签一,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.industry_label, 8, 8)
) 产业标签二,
LEN(trdc.industry_label) 产业标签长度,
trdc.technical_status 技术状态,
trdc.applicable_field 适用领域,
trdc.technical_remarks 技术介绍,
trdcp.patent_category 专利类别,
trdcp.patent_no 专利号,
trdcp.application_date 授权日,
trdc.cooperation_type 合作方式,
trdc.price_type 价格类别,
trdc.price 价格,
trdc.attachment_id 附件,
trdc.has_demo 样品情况,
trdc.infor_category 信息类别,
trdc.effective_end_day 信息有效时间,
trdc.linkman 联系人,
trdc.telephone 联系电话,
trdc.email 邮箱,
trdc.label 自定义标签,
trdc.attachment_id 图片id,
trdc.create_date 创建时间,
trdc.approve 审核结果,
trdc.status 状态,
trdc.is_available 逻辑删除flag,
trdc.data_source 信息来源
FROM
t_results_details_common trdc
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
WHERE
trdc.data_source != 'leading-in-2016-11-4'
OR trdc.data_source IS NULL
```
版本v2.1
```sql
SELECT
trdc.id 成果表id,
trdc.name 成果名称,
trdc.publisher_id 发布者,
trdc.belongs_to_type 成果权属,
trdc.belongs_to_name 个人单位名称,
trdc.technical_field 技术领域Code,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.technical_field, 2, 4)
) 技术领域一,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.technical_field, 7, 4)
) 技术领域二,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.technical_field, 12, 4)
) 技术领域三,
trdc.district 区,
trdc.industry_label AS 产业标签Code,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.industry_label, 2, 5)
) 产品标签一,
(
SELECT
tdm.name
FROM
t_dynamic_menu tdm
WHERE
tdm.code = SUBSTRING (trdc.industry_label, 8, 8)
) 产业标签二,
LEN(trdc.industry_label) 产业标签长度,
trdc.technical_status 技术状态,
trdc.applicable_field 适用领域,
trdc.technical_remarks 技术介绍,
trdcp.patent_category 专利类别,
trdcp.patent_no 专利号,
trdcp.application_date 授权日,
trdc.cooperation_type 合作方式,
trdc.price_type 价格类别,
trdc.price 价格,
trdc.attachment_id 附件,
trdc.has_demo 样品情况,
trdc.infor_category 信息类别,
trdc.effective_end_day 信息有效时间,
trdc.contact 联系人,
trdc.telephone 联系电话,
trdc.email 邮箱,
trdc.label 自定义标签,
trdc.attachment_id 图片id,
trdc.create_date 创建时间,
trdc.approve 审核结果,
trdc.status 状态,
trdc.is_available 逻辑删除flag,
trdc.data_source 信息来源
FROM
t_results_details_common trdc
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
WHERE
trdc.data_source != 'leading-in-2016-11-4'
OR trdc.data_source IS NULL
```吐槽(0)