SQL 查询以获取总行数和不同行数之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33326872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
query to get the difference between total number of rows and distinct rows
提问by prabhakar Reddy G
I'm new to SQL, wasn't able to write the correct SQL. Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.
我是 SQL 新手，无法编写正确的 SQL。给定一个表 STATION，其中包含五个字段的数据，即 ID、CITY、STATE、NORTHERN LATITUDE 和 WESTERN LONGITUDE。
+-------------+------------+
| Field | Type |
+-------------+------------+
| ID | INTEGER |
| CITY | VARCHAR(21)|
| STATE | VARCHAR(2) |
| LAT_N | NUMERIC |
| LONG_W | NUMERIC |
+-------------+------------+
Let NUMbe the number of cities and NUMuniquebe the number of unique cities, then write a query to print the value of NUM- NUMunique.
让NUM是城市的数量，NUMunique是唯一的城市数量，然后编写一个查询打印的价值NUM- NUMunique。
I tried:
我试过：
select (count(CITY)- distinct count(CITY)) from STATION;
回答by scaisEdge
You can use the select distinct inside the count and try this way
您可以在计数内使用 select distinct 并尝试这种方式
select (count(CITY)- count(distinct CITY)) from STATION;
回答by Claire Hu
select (count(CITY)- count(distinct CITY)) from STATION;
Footnote: you can review on the following sql function Calculating Mathematical Values: count, avg, sum, +, -, *, %
脚注：您可以查看以下 sql 函数计算数学值：count、avg、sum、+、-、*、%
回答by Joydeep Sarkar
SELECT COUNT(*) - COUNT(DISTINCT CITY) FROM STATION
从车站选择 COUNT(*) - COUNT(DISTINCT CITY)
回答by Pranav
SELECT (COUNT(CITY)- COUNT(DISTINCT(CITY))) AS diff
FROM STATION
回答by Legend
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) AS N FROM STATION;
Query result aliased as N.
查询结果别名为N.
