本文介绍了MySQL使用if进行多次查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我现在正在编写一些MySQL命令，这使我能够生成更好的统计信息。我现在的问题是，在特殊情况下应该调用一些查询，而在其他情况下则不会。
I am right now writing a few MySQL commands, which allow me to generate better statistics. My problem is now that under special circumstances a few queries should be called and under other circumstances not.
我当前的尝试:(我做了它）
my current attempt: (I shorted it)
SET @var1=0, @var2=0;
SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
IF @var1 > @var2 THEN (
TRUNCATE `sg_playerranking`;
SET
@Rank := 0;
INSERT INTO
`sg_playerranking`
SELECT
@Rank := @Rank + 1 AS `Rank`,
t.*,
@var1 AS `Last Updated`
FROM
(
SELECT
`player` AS `Player`,
FROM
`sg_playerstats`
GROUP BY
`player`
ORDER BY
`Score` DESC, ...
) t; )
END IF
SELECT `Rank`, ... FROM `sg_playerranking` WHERE 1
If语句不起作用（语法错误。每个查询部分本身都在工作！）
我做了一些研究，但除了那个找不到任何东西！
The If statement is not working (Syntax error. Every single query part itself is working!)I did some research but could not find anything but that!
括号没有区别！
编辑：
现在我真的很困惑！
我的代码是：
CREATE PROCEDURE generate_statistics (OUT param1 INT)
BEGIN
SET @var1=0, @var2=0;
SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
IF @var1 > @var2 THEN
TRUNCATE `sg_playerranking`;
SET
@Rank := 0;
INSERT INTO
`sg_playerranking`
SELECT
@Rank := @Rank + 1 AS `Rank`,
t.*,
@var1 AS `Last Updated`
FROM
(
SELECT
`player` AS `Player`,
SUM(1) AS `Games`,
SUM(`points`) AS `Points`,
SUM(`points`) / SUM(1) AS `Points per Game`,
SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
SUM(`kills`) AS `Kills`,
SUM(`kills`) / SUM(1) AS `Kills per Game`,
SUM(`death`) AS `Deaths`,
SUM(`death`) / SUM(1) AS `Deaths per Game`,
SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
FROM
`sg_playerstats`
GROUP BY
`player`
ORDER BY
`Score` DESC,
`Points per Game` DESC,
`Wins per Game` DESC,
`Loses per Game` ASC,
`Kills per Game` DESC,
`Deaths per Game` ASC,
`Points` DESC,
`Wins` DESC,
`Loses` ASC,
`Kills` DESC,
`Deaths` ASC,
`Games` DESC,
`Time played` DESC,
`Player` DESC
) t;
END IF;
SELECT `Rank`, `Player`, `Games`, `Points`, `Points per Game`, `Wins`, `Wins per Game`, `Loses`, `Loses per Game`, `Kills`, `Kills per Game`, `Deaths`, `Deaths per Game`, `Time played`, `Score` INTO param1 FROM `sg_playerranking` WHERE 1
END;
这是我得到的错误：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
好吧，如果我拿到DECLARE就没有区别了！
Well it makes no difference if I take DECLARE!
（对不起，长时间查询！）
(Sorry for the long query!)
EDIT2 ：
我终于解决了！此代码正常运行！
I finally solved it! This code is working!
DELIMITER //
CREATE PROCEDURE gamestats ()
BEGIN
SET
@var1 = 1,
@var2 = 0;
SELECT
@var1 := `id`
FROM
`sg_playerstats`
ORDER BY
`id` DESC
LIMIT
0,
1;
SELECT
@var2 := `Last Updated`
FROM
`sg_playerranking`
LIMIT
0,
1;
IF
@var1 > @var2
THEN
TRUNCATE
`sg_playerranking`;
SET
@Rank := 0;
INSERT INTO
`sg_playerranking`
SELECT
@Rank := @Rank + 1 AS `Rank`,
t.*,
@var1 AS `Last Updated`
FROM
(
SELECT
`player` AS `Player`,
SUM(1) AS `Games`,
SUM(`points`) AS `Points`,
SUM(`points`) / SUM(1) AS `Points per Game`,
SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
SUM(`kills`) AS `Kills`,
SUM(`kills`) / SUM(1) AS `Kills per Game`,
SUM(`death`) AS `Deaths`,
SUM(`death`) / SUM(1) AS `Deaths per Game`,
SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
FROM
`sg_playerstats`
GROUP BY
`player`
ORDER BY
`Score` DESC,
`Points per Game` DESC,
`Wins per Game` DESC,
`Loses per Game` ASC,
`Kills per Game` DESC,
`Deaths per Game` ASC,
`Points` DESC,
`Wins` DESC,
`Loses` ASC,
`Kills` DESC,
`Deaths` ASC,
`Games` DESC,
`Time played` DESC,
`Player` DESC
) t;
END IF;
END;
//
DELIMITER ;
推荐答案
在Mysql中，if语句仅适用于存储程序。它在常规sql批次中不可用。
In Mysql, the if statement is only applicable to stored procedures. It is not available in regular sql batches.
这篇关于MySQL使用if进行多次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！