mysql 连接数字和字符字段

Question

我正在为 jsdatatables 显示生成数据 - 有四个站点，我需要将数据拉入每个站点的列中。大多数列只包含一个数字，但有时也有注释，我需要将它们添加到列中。

我当前无法使用的示例在这里

SELECT a.drug as drug, 
CONCAT(SUM(IF(a.idSite=1, IF(a.expiry > now(),quantity,0), 0)),' ',notes) AS 'col1', 
SUM(IF(a.idSite=2, IF(a.expiry > now(),quantity,0), 0)) AS 'col2', 
SUM(IF(a.idSite=3, IF(a.expiry > now(),quantity,0), 0)) AS 'col3', 
SUM(IF(a.idSite=4, IF(a.expiry > now(),quantity,0), 0)) AS 'col4',
1,2  
FROM items a left join sites s on a.idSite = s.id  
WHERE a.deleted_at IS NULL   
Group By drug, notes

我故意将其他三列保留为仅数字 - 并且数据当前包含 col1 和 col3 的相同数据

这给了我

+----------------------------------------------------------------------------------------------------------------------------------+-------------------+------+------+------+---+---+
| drug                                                                                                                             | col1              | col2 | col3 | col4 | 1 | 2 |
+----------------------------------------------------------------------------------------------------------------------------------+-------------------+------+------+------+---+---+
| (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml                                                                                     | 0 n/a             |    0 |    0 |    0 | 1 | 2 |
| (c) Diazepam 10mg/2ml Injection                                                                                                  | 1 to be requested |    0 |    1 |    0 | 1 | 2 |
| (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free)                                                                  | 8 n/a             |    0 |    8 |    0 | 1 | 2 |
| Acetylcysteine 20%                                                                                                               | NULL              |    0 |   70 |    0 | 1 | 2 |
| Activated Charcoal                                                                                                               | NULL              |    0 |   30 |    0 | 1 | 2 |
| Atropine 0.1mg/ml Pre Loaded Syringe                                                                                             | NULL              |    0 |  100 |    0 | 1 | 2 |
| Calcium Disodium Edetate 200mg/ml                                                                                                | NULL              |    0 |    0 |    0 | 1 | 2 |
| Calcium gluconate 10%                                                                                                            | NULL              |    0 |   20 |    0 | 1 | 2 |
| Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride | 0 Non Formulary   |    0 |    0 |    0 | 1 | 2 |

项目表

+-----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------+------------+--------+----------+-----------------+
| id  | deleted_at | drug                                                                                                                             | quantity | expiry     | idSite | barcode  | notes           |
+-----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------+------------+--------+----------+-----------------+
| 773 | NULL       | (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml                                                                                     |        0 | 2021-07-18 |      3 | 41125225 | n/a             |
| 739 | NULL       | (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml                                                                                     |        0 | 2021-07-18 |      1 | 41125225 | n/a             |
| 772 | NULL       | (c) Diazepam 10mg/2ml Injection                                                                                                  |        1 | 2020-08-01 |      3 | 41061007 | to be requested |
| 738 | NULL       | (c) Diazepam 10mg/2ml Injection                                                                                                  |        1 | 2020-08-01 |      1 | 41061007 | to be requested |
| 774 | NULL       | (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free)                                                                  |        8 | 2021-07-01 |      3 | 41992542 | n/a             |
| 740 | NULL       | (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free)                                                                  |        8 | 2021-07-01 |      1 | 41992542 | n/a             |
| 708 | NULL       | Acetylcysteine 20%                                                                                                               |       70 | 2019-11-01 |      1 | 41121031 | NULL            |
| 742 | NULL       | Acetylcysteine 20%                                                                                                               |       70 | 2019-11-01 |      3 | 41121031 | NULL            |
| 709 | NULL       | Activated Charcoal                                                                                                               |       30 | 2020-01-01 |      1 | 41125067 | NULL            |
| 743 | NULL       | Activated Charcoal                                                                                                               |       30 | 2020-01-01 |      3 | 41125067 | NULL            |
| 710 | NULL       | Atropine 0.1mg/ml Pre Loaded Syringe                                                                                             |      100 | 2021-07-01 |      1 | 41061203 | NULL            |
| 744 | NULL       | Atropine 0.1mg/ml Pre Loaded Syringe                                                                                             |      100 | 2021-07-01 |      3 | 41061203 | NULL            |
| 711 | NULL       | Calcium Disodium Edetate 200mg/ml                                                                                                |       30 | 2019-06-01 |      1 | 41121009 | NULL            |
| 745 | NULL       | Calcium Disodium Edetate 200mg/ml                                                                                                |       30 | 2019-06-01 |      3 | 41121009 | NULL            |
| 712 | NULL       | Calcium gluconate 10%                                                                                                            |       20 | 2020-11-01 |      1 | 41091003 | NULL            |
| 746 | NULL       | Calcium gluconate 10%                                                                                                            |       20 | 2020-11-01 |      3 | 41091003 | NULL            |
| 770 | NULL       | Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride |        0 | 2021-07-18 |      3 | 41002215 | Non Formulary   |
| 736 | NULL       | Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride |        0 | 2021-07-18 |      1 | 41002215 | Non Formulary   |

我的问题是，连接列仅在有注释要显示时返回一个值 - 我确信我在做一些愚蠢的事情

Answer 1

如果任何字段为 NULL，concat 将返回 NULL解决方案是我们 concat_ws 或按照 here