成果表(产业标签为中文)

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)

文章评论

    共有0条评论

    验证码:

文章目录