MySQL 带有一点逻辑的多查询
有一个包含这些值的用户表（在示例中进行了精简）：
- 名称
- 城市
- 县 州
- 省 国家
- 大陆
- 强度
- 耐力
- 情报
- 我
我基本上想要一个查询，为您自己返回按地区划分的最高统计数据。如果您在该国拥有最高的力量 (67)，在您的城市拥有最高的耐力 (59)，但在任何地方都不是最高的智力，我想要一个 2 行结果：(
'力量', 67, '国家', '美国')
('Endurance', 59, 'City', 'Redmond')
请注意，如果您拥有该国最高的力量，那么您也将拥有该国最高的力量州/省、县和市（但我希望这些内容不要在结果中返回）。我可以使用多个 SELECT 查询来完成这一切，但是，我想知道如何在一个查询中执行此操作以提高效率（或者这是否是一个好主意？）。我想逻辑会是这样的（伪代码）：
(SELECT FROM Users ORDERBY Strength WHERE Country = 'MyCountry' LIMIT 1) IF Name != 'Me' (SELECT FROM Users ORDERBY Strength WHERE StateProvince = 'MyState' LIMIT 1) ...等等
，以上还需要与耐力和智力一起工作。谢谢！
I have a Users table with these values (stripped down for the example):
- Name
- City
- County
- StateProvince
- Country
- Continent
- Strength
- Endurance
- Intelligence
I basically want a query that returns the highest stat by region for yourself. If you have the highest Strength (67) in the country, highest Endurance (59) in your City, and not the highest Intelligence anywhere, I'd like a 2 row result of:
('Strength', 67, 'Country', 'United States')
('Endurance', 59, 'City', 'Redmond')
Note that if you have the highest Strength in the country, you will also have the highest strength in the state/province, county, and city (but I'd like those not to be returned in the result). I can do this all using multiple SELECT queries, however, I'd like to know how to do this in one single query for efficiency (or if that's even a good idea?). I'd imagine the logic would go something like (pseudo code):
(SELECT FROM Users ORDERBY Strength WHERE Country = 'MyCountry' LIMIT 1) IF Name != 'Me' (SELECT FROM Users ORDERBY Strength WHERE StateProvince = 'MyState' LIMIT 1) ... and so on
Furthermore, the above would also need to work with Endurance and Intelligence. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
检查此链接 MySQL control-flow-functions
Check this link MySQL control-flow-functions