gpt4 book ai didi

mysql - 把三个中频换成一个

转载 作者:可可西里 更新时间:2023-11-01 07:44:00 28 4
gpt4 key购买 nike

创建表语句:

CREATE TABLE `Tovar` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`idPreparat` MEDIUMINT(6) UNSIGNED NULL DEFAULT NULL,
`drugApt` VARCHAR(250) NULL DEFAULT NULL,
`countryApt` VARCHAR(250) NULL DEFAULT NULL,
`manfApt` VARCHAR(250) NULL DEFAULT NULL,
`md5` BINARY(16) NOT NULL,
PRIMARY KEY (`id`),
INDEX `ix_prep` (`idPreparat`),
INDEX `ix_md5` (`md5`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

查询:

 SELECT id,
IF(COUNT(Tovar.id)=1,Tovar.drugApt,'') AS `drugApt`,
IF(COUNT(Tovar.id)=1,Tovar.countryApt,'') AS `countryApt`,
IF(COUNT(Tovar.id)=1,Tovar.manfApt,'') AS `manfApt`
FROM Tovar
WHERE Tovar.md5=0x00000000000000000000000000000000
GROUP BY Tovar.idPreparat

如何将三个 IF 替换为一个 IF(COUNT(Tovar.id)=1,,) ?添加我的想法:

    CASE COUNT(Tovar.id)>1
WHEN true THEN CONCAT_WS(@drugApt:='Tovar.drugApt',@countryApt='Tovar.countryApt',@manfApt='Tovar.manfApt')
WHEN false THEN CONCAT_WS(@drugApt:='',@countryApt='',@manfApt='')
END

最佳答案

如果我正确理解您的请求,子查询可能有助于实现所需的结果:

SELECT  tmp.id,
COALESCE(t.drugApt, '') as drugApt,
COALESCE(t.countryApt, '') as countryApt,
COALESCE(t.manfApt, '') as manfApt
FROM (
SELECT id,
IF(COUNT(id)=1, 1, 0) as cond
FROM Tovar
WHERE md5 = 0x00000000000000000000000000000000
GROUP BY idPreparat
) tmp
LEFT JOIN Tovar t
ON t.id = tmp.id
AND tmp.cond = 1

这有帮助吗?

关于mysql - 把三个中频换成一个,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35357349/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com