MySql:使用临时表时出现问题
我正在尝试使用临时表来存储查询所需的一些值。 使用临时表的原因是我不想永久存储数据,以便不同的用户可以同时修改它。 该数据仅存储一秒钟,因此我认为临时表是最好的方法。
问题是,我尝试使用它的方式似乎不正确(如果我使用永久查询,则查询有效)。
这是查询的示例:
CREATE TEMPORARY TABLE SearchMatches (PatternID int not null primary key, Matches int not null)
INSERT INTO SearchMatches (PatternID, Matches)
VALUES ('12605','1'),('12503','1'),('12587','2'),('12456','1'),
('12457','2'),('12486','2'),('12704','1'),(' 12686','1'),
('12531','2'),('12549','1'),('12604','1'),('12504','1'),
('12586','1'),('12548','1'),('12 530','1'),('12687','2'),
('12485','1'),('12705','1')
SELECT pat.id, signatures.signature, products.product, versions.version, builds.build, pat.log_file, sig_types.sig_type, pat.notes, pat.kb
FROM patterns AS pat
INNER JOIN signatures ON pat.signature = signatures.id
INNER JOIN products ON pat.product = products.id
INNER JOIN versions ON pat.version = versions.id
INNER JOIN builds ON pat.build = builds.id
INNER JOIN sig_types ON pat.sig_type = sig_types.id, SearchMatches AS sm
INNER JOIN patterns ON patterns.id = sm.PatternID
WHERE sm.Matches <> 0
ORDER BY sm.Matches DESC, products.product, versions.version, builds.build
LIMIT 0 , 50
有什么建议吗?
谢谢。
I'm trying to use a temporary tables to store some values I need for a query. The reason of using a temporary table is that I don't want to store the data permanently so different users can modify it at the same time. That data is just stored for a second, so I think a temporary table is the best approach for this.
The thing is that it seems that the way I'm trying to use it is not right (the query works if I use a permanent one).
This is an example of query:
CREATE TEMPORARY TABLE SearchMatches (PatternID int not null primary key, Matches int not null)
INSERT INTO SearchMatches (PatternID, Matches)
VALUES ('12605','1'),('12503','1'),('12587','2'),('12456','1'),
('12457','2'),('12486','2'),('12704','1'),(' 12686','1'),
('12531','2'),('12549','1'),('12604','1'),('12504','1'),
('12586','1'),('12548','1'),('12 530','1'),('12687','2'),
('12485','1'),('12705','1')
SELECT pat.id, signatures.signature, products.product, versions.version, builds.build, pat.log_file, sig_types.sig_type, pat.notes, pat.kb
FROM patterns AS pat
INNER JOIN signatures ON pat.signature = signatures.id
INNER JOIN products ON pat.product = products.id
INNER JOIN versions ON pat.version = versions.id
INNER JOIN builds ON pat.build = builds.id
INNER JOIN sig_types ON pat.sig_type = sig_types.id, SearchMatches AS sm
INNER JOIN patterns ON patterns.id = sm.PatternID
WHERE sm.Matches <> 0
ORDER BY sm.Matches DESC, products.product, versions.version, builds.build
LIMIT 0 , 50
Any suggestion?
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我猜,但我认为查询应该是:
这两行对我来说似乎不正确:
如果您需要与
patterns
表进行两个连接,我认为您应该避免将逗号分隔与显式JOIN
子句混合在一起。I´m guessing but i think the query should be:
these two lines dont seems right to me:
And if you need the two joins with
patterns
table, i think you should avoid mixing comma-separated with explicitJOIN
clauses.感谢您的回复。 这是我最终得到的解决方案:
Thanks for the replies. This is the solution I finally got: