时间戳

首页 > 解决方案 > mysql父/子加入

mysql - mysql父/子加入

问题描述

我需要一个 mysql 查询来组合来自两个表的数据。其中 prijzen2.Kortinggroep 是父级，而 Kortingsgroepen_webshop.Kortinggroep 是子级。

表 1：prijzen2

Relatie;Prijsgroep;Kortinggroep;Tarief_code;Waardefunctie;Waarde;Ingangsdatum;Einddatum;Omschrijving
13073; 13073 ;; 3250174; Price per price unit; 0.98 ;;; Black hose guided bend 14-17mm 3250174
13073; 13073 ;; 3250175; Price per price unit; 1.2 ;;; Black hose guided bend 18-23mm 3250175
13008; HJ; 102201; Discount%; 28; 21-02-17 ;; Daalderop boilers
13551; 13551; 102201; Discount%; 28; 08-02-17 ;; Daalderop boilers
13669; 13669; 102201; Discount%; 28; 10-01-17 ;; Daalderop boilers
13189; 13189; 102203; Discount%; 27.5 ;;; Inventum water heaters
13086; 13086; 102203; Discount%; 35 ;;; Inventum water heaters

带有额外数据的子表：Kortingsgroepen_webshop

Kortinggroep;Tarief_code
102201;07.02.26.631
102201;07.02.26.634
102201;07.02.86.632
102201;07.02.10.636
102201;07.02.26.031
102203;40140520_1
102203;40221020_1
102203;40221004_1
102203;40141020_1
102203;40141004_1
102203;40231020_1
102203;40231004_1
102203;40141520_1
102203;40231520_1
102203;40122020
102203;15198706_1
102203;15201000_1
102203;1240536
102203;1240560
102203;42185006
102203;43188010
102203;44182015
102203;41183116
102203;44182132
102203;48181535
102203;41183020
102203;42185024
102203;43188024
102203;44182030
102203;44042033
102203;44042063
102203;43048033
102203;43048063
102203;35220080
102203;15036080
102203;35220050
102203;35220120
102203;35220154
102203;35220124
102203;35220150
102203;35220100

需要查询结果：

Relatie;Prijsgroep;Kortinggroep;Tarief_code;Waardefunctie;Waarde;Ingangsdatum;Einddatum;Omschrijving
13073; 13073 ;; 3250174; Price per price unit; 0.98 ;;; Black hose guided bend 14-17mm 3250174
13073; 13073 ;; 3250175; Price per price unit; 1.2 ;;; Black hose guided bend 18-23mm 3250175
13008; HJ; 102201; 07.02.26.631; Discount%; 28; 21-02-17 ;; Daalderop boilers
13008; HJ; 102201; 07.02.26.634; Discount%; 28; 21-02-17 ;; Daalderop boilers
13008; HJ; 102201; 07.02.86.632; Discount%; 28; 21-02-17 ;; Daalderop boilers
13008; HJ; 102201; 07.02.10.636; Discount%; 28; 21-02-17 ;; Daalderop boilers
13008; HJ; 102201; 07.02.26.031; Discount%; 28; 21-02-17 ;; Daalderop boilers
13551; 13551; 102201; 07.02.26.631; Discount%; 28; 08-02-17 ;; Daalderop boilers
13551; 13551; 102201; 07.02.26.634; Discount%; 28; 08-02-17 ;; Daalderop boilers
13551; 13551; 102201; 07.02.86.632; Discount%; 28; 08-02-17 ;; Daalderop boilers
13551; 13551; 102201; 07.02.10.636; Discount%; 28; 08-02-17 ;; Daalderop boilers
13551; 13551; 102201; 07.02.26.031; Discount%; 28; 08-02-17 ;; Daalderop boilers
13669; 13669; 102201; 07.02.26.631; Discount%; 28; 10-01-17 ;; Daalderop boilers
13669; 13669; 102201; 07.02.26.634; Discount%; 28; 10-01-17 ;; Daalderop boilers
13669; 13669; 102201; 07.02.86.632; Discount%; 28; 10-01-17 ;; Daalderop boilers
13669; 13669; 102201; 07.02.10.636; Discount%; 28; 10-01-17 ;; Daalderop boilers
13669; 13669; 102201; 07.02.26.031; Discount%; 28; 10-01-17 ;; Daalderop boilers
13189; 13189; 102203; 40140520_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40221020_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40221004_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40141020_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40141004_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40231020_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40231004_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40141520_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40231520_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 40122020; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 15198706_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 15201000_1; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 1240536; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 1240560; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 42185006; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 43188010; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 44182015; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 41183116; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 44182132; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 48181535; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 41183020; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 42185024; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 43188024; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 44182030; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 44042033; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 44042063; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 43048033; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 43048063; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 35220080; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 15036080; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 35220050; Discount%; 27.5 ;;; Inventum water heaters
13189; 13189; 102203; 35220120; Discount%; 27.5 ;;; Invent

通过这个查询，我得到了几乎想要的结果。两个 Tarief_code 列都需要合并，但我无法管理它。有没有人有任何提示？

我现在的查询是：

SELECT
prijzen2.Relatie,
prijzen2.Prijsgroep,
Kortingsgroepen_webshop.Kortinggroep,
Kortingsgroepen_webshop.Tarief_code,
prijzen2.Tarief_code,
prijzen2.Waardefunctie,
prijzen2.Waarde
FROM
prijzen2
LEFT JOIN Kortingsgroepen_webshop
ON Kortingsgroepen_webshop.Kortinggroep=prijzen2.Kortinggroep

修复了 bij 这个查询：

SELECT
prijzen2.Relatie,
prijzen2.Prijsgroep,
Kortingsgroepen_webshop.Kortinggroep,
COALESCE( Kortingsgroepen_webshop.Tarief_code, prijzen2.Tarief_code) AS Tarief_code,
prijzen2.Waardefunctie,
prijzen2.Waarde
FROM
prijzen2
LEFT JOIN Kortingsgroepen_webshop
ON Kortingsgroepen_webshop.Kortinggroep=prijzen2.Kortinggroep

标签： mysqlsql

解决方案

如果通过合并，您的意思是将其作为一个字段：

07.02.26.631 Discount%

...然后您可以像这样使用 CONCAT() ：

SELECT
prijzen2.Relatie,
prijzen2.Prijsgroep,
Kortingsgroepen_webshop.Kortinggroep,
CONCAT(Kortingsgroepen_webshop.Tarief_code, " ", prijzen2.Tarief_code) as 'Tarief_code',
prijzen2.Waardefunctie,
prijzen2.Waarde
FROM
prijzen2
LEFT JOIN Kortingsgroepen_webshop
ON Kortingsgroepen_webshop.Kortinggroep=prijzen2.Kortinggroep

推荐阅读