我有这样的看法:
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = 2;
我想让它更通用,它意味着把2变成一个变量.我试过这个:
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = @MyVariable;
但MySQL不允许这样.
我发现了一个丑陋的解决方法:
CREATE FUNCTION GetMyVariable() RETURNS INTEGER DETERMINISTIC NO SQL
BEGIN RETURN @MyVariable; END|
然后是:
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = GetMyVariable();
但它看起来真的很糟糕,而且用法也很糟糕——我必须在每次使用视图之前设置@MyVariable.
有没有一个解决方案,我可以这样使用:
SELECT Column FROM MyView(2) WHERE (...)
具体情况如下:
CREATE TABLE Denial
(
Id INTEGER UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(Id),
DateTime DATETIME NOT NULL,
FeatureId MEDIUMINT UNSIGNED NOT NULL,
FOREIGN KEY (FeatureId)
REFERENCES Feature (Id)
ON UPDATE CASCADE ON DELETE RESTRICT,
UserHostId MEDIUMINT UNSIGNED NOT NULL,
FOREIGN KEY (UserHostId)
REFERENCES UserHost (Id)
ON UPDATE CASCADE ON DELETE RESTRICT,
Multiplicity MEDIUMINT UNSIGNED NOT NULL DEFAULT 1,
UNIQUE INDEX DenialIndex (FeatureId, DateTime, UserHostId)
) ENGINE = InnoDB;
多重性是在同一秒钟内记录的多个相同请求.我想显示一个拒绝列表,但有时,当应用程序被拒绝时,它会重试几次以确保.因此,通常情况下,当同一用户在几秒钟内对同一功能进行三次拒绝时,实际上是一次拒绝.如果我们还有一个资源来满足这个请求,接下来的两次拒绝就不会发生了.因此,我们希望在报告中对拒绝进行分组,允许用户指定拒绝分组的时间跨度.例如,如果我们在时间戳1,2,24,26,27,45中有拒绝(对于功能1上的用户1),并且用户想要将彼此距离较近的拒绝分组到4秒之外,他应该得到如下结果:1(x2),24(x3),45(x1).我们可以假设,真实否认之间的空间比重复之间的空间大得多.我用以下方法解决了这个问题:
CREATE FUNCTION GetDenialMergingTime()
RETURNS INTEGER UNSIGNED
DETERMINISTIC NO SQL
BEGIN
IF ISNULL(@DenialMergingTime) THEN
RETURN 0;
ELSE
RETURN @DenialMergingTime;
END IF;
END|
CREATE VIEW MergedDenialsViewHelper AS
SELECT MIN(Second.DateTime) AS GroupTime,
First.FeatureId,
First.UserHostId,
SUM(Second.Multiplicity) AS MultiplicitySum
FROM Denial AS First
JOIN Denial AS Second
ON First.FeatureId = Second.FeatureId
AND First.UserHostId = Second.UserHostId
AND First.DateTime >= Second.DateTime
AND First.DateTime - Second.DateTime < GetDenialMergingTime()
GROUP BY First.DateTime, First.FeatureId, First.UserHostId, First.Licenses;
CREATE VIEW MergedDenials AS
SELECT GroupTime,
FeatureId,
UserHostId,
MAX(MultiplicitySum) AS MultiplicitySum
FROM MergedDenialsViewHelper
GROUP BY GroupTime, FeatureId, UserHostId;
然后每5秒显示一次用户1和用户2对功能3和功能4的拒绝,您需要做的就是:
SET @DenialMergingTime := 5;
SELECT GroupTime, FeatureId, UserHostId, MultiplicitySum FROM MergedDenials WHERE UserHostId IN (1, 2) AND FeatureId IN (3, 4);
我使用视图是因为在视图中可以很容易地过滤数据,并在jQuery网格中显式地使用它,自动排序,限制记录数等等.
但这只是一个丑陋的解决办法.有没有合适的方法?