mysql - 合并两个 MySQL 查询并替换结果

Question

我想找到一种方法来使用一个查询，当 LineUps.DIG 输出 = Y 时，我将其替换。我想在 html 中标记为星号。也许在前端执行此操作比在 sql 查询上执行此操作更好。

拳头查询有效并返回数据

$stmt = $conn->prepare("SELECT distinct Channel_LineUps.channel, Channel_LineUps.description, Channel_LineUps.Tier, LineUps.HD, LineUps.DIG FROM Channel_LineUps, LineUps WHERE (LineUps.Market_ID = Channel_LineUps.Market_ID AND Channel_LineUps.Market_ID = 28) ORDER BY Channel_LineUps.Tier ASC"); 

第二个查询是我进行替换的地方，但不确定如何与上面的查询合并。

SELECT DIG,REPLACE(DIG,"Y","*") as output FROM LineUps WHERE DIG="Y");

Answer 1

我认为CASE WHEN是您正在寻找的:

$sql = <<<SQL
SELECT DISTINCT Channel_LineUps.channel, Channel_LineUps.description,
Channel_LineUps.Tier, LineUps.HD,
CASE LineUps.DIG WHEN "Y" THEN REPLACE(LineUps.DIG, "Y", "*") ELSE LineUps.DIG END
FROM Channel_LineUps, LineUps
WHERE (LineUps.Market_ID = Channel_LineUps.Market_ID
AND Channel_LineUps.Market_ID = 28)
ORDER BY Channel_LineUps.Tier ASC
SQL;

$stmt = $conn->prepare($sql);

您也可以直接将上面第 4 行替换为:

CASE LineUps.DIG WHEN "Y" THEN "*" ELSE LineUps.DIG END

因为您肯定知道它的值(value)。