sql查询唯一/不同的行
sql查询唯一/不同的行
提问于 2011-04-05 04:37:50
我有一张基于邮政编码的城市和州的表格。因此，如果有人搜索芝加哥，他们将得到芝加哥的30份清单，因为邮编的数量。我只想得到芝加哥的一份清单..。但是我也想返回zip字段(因为我不需要它的精确性)。下面将返回一行“芝加哥，伊利诺伊”，但也不会通过拉链.它怎么也能通过拉链。
SELECT DISTINCT City, State FROM Zips where City like 'chicago' ORDER BY State拉链表
*Zip | City | State | Lat | LongStack Overflow用户
回答已采纳
发布于 2011-04-05 04:42:41
SELECT City, State, MIN(Zip) AS ZIP
FROM Zips
WHERE City = 'chicago'
GROUP BY City, State
ORDER BY State;或者，也许更有信息性：
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = 'chicago'
GROUP BY City, State
ORDER BY State;是否也可以允许输入搜索获取邮政编码和/或城市？
在邮政编码上搜索：
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE Zip = '01234'
GROUP BY City, State
ORDER BY State;搜索拉链和城市：
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = 'chicago'
AND Zip = '01234'
GROUP BY City, State
ORDER BY State;搜索拉链或城市：
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = 'chicago'
OR Zip = '01234'
GROUP BY City, State
ORDER BY State;使用单个查询搜索zip (除非为null)或city (除非为null)要复杂得多。它通常会使用问号、占位符等，而不是完全跨DBMS可移植(而上面的查询都是可移植到任何SQL DBMS的)。
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE (? IS NOT NULL AND City = ?)
OR (? IS NOT NULL AND Zip = ?)
GROUP BY City, State
ORDER BY State;在这里，如果有效的话，您将提供两次城市参数(前两个问号各一次)和zip参数两次(最后两个问号各一次)。这也进入了讨论哪种编程语言的领域。有些DBMS允许您编写每个参数一次，使用“:n”这样的符号来标识不同的参数：
SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE (:1 IS NOT NULL AND City = :1)
OR (:2 IS NOT NULL AND Zip = :2)
GROUP BY City, State
ORDER BY State;我不知道MySQL在这个领域提供了什么。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5547263
复制相关文章
相似问题
腾讯云开发者
