给定表:
id domain name value
----------------------------------
1 NULL a v1
2 example.com a v2
3 example.net a v3
4 NULL b v4
5 example.net b v5
6 example.com c v6
7 example.com d v7
8 NULL d v8
我如何运行一个查询,它将检索以下结果为domain = "example.com"?请注意,如果没有指定domain的value,则假定NULL
域。
name value
--------------
a v2
b v4
c v6
d v7
And for domain = "example.net":
name value
--------------
a v3
b v5
d v8
Like so
(select name, value
from <your_table_name>
where domain = 'example.com')
union
(select name, value
from <your_table_name>
where domain is null
and name not in
(select name
from <your_table_name>
where domain = 'example.com'))
超过value
的最大值:
SELECT name, MAX(value) AS max_value FROM thetable GROUP BY name;
SELECT name, MAX(value) AS max_value FROM thetable WHERE domain = "example.net" GROUP BY name;
为什么不用简单的select SELECT name,value FROM table_name WHERE domain="example.net" ORDER BY name
呢