在CentOS 6上的Postgresql 9中,pref_users表中有60000条记录：
# \d pref_users
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+--------------------
id | character varying(32) | not null
first_name | character varying(64) | not null
last_name | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
(... more columns skipped...)
# \d pref_ban2
Table "public.pref_ban2"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
city | character varying(64) |
last_ip | inet |
reason | character varying(128) |
created | timestamp without time zone | default now()
Indexes:
"pref_ban2_pkey" PRIMARY KEY,btree (id)
在PHP脚本中,我试图在jQuery-dataTable中显示来自pref_users的所有60000个用户.我想标记被禁用的用户(在pref_ban2中找到的用户).
这意味着我需要一个名为ban的列,用于查询中包含true或false的每个记录.
所以我正在尝试左外连接查询：
# select
b.id,-- how to make this column a boolean?
u.id,u.first_name,u.last_name,u.city,u.last_ip,to_char(u.login,'DD.MM.YYYY') as day
from pref_users u left outer join pref_ban2 b on u.id=b.id
limit 10;
id | id | first_name | last_name | city | last_ip | day
----+----------+-------------+-----------+-------------+-----------------+------------
| DE1 | Alex | | Bochum | 2.206.0.224 | 21.11.2014
| DE100032 | Княжна Мэри | | London | 151.50.61.131 | 01.02.2014
| DE10011 | Aлександр Ш | | Симферополь | 37.57.108.13 | 01.01.2014
| DE10016 | Semen10 | | usa | 69.123.171.15 | 25.06.2014
| DE10018 | Горловка | | Горловка | 178.216.97.214 | 25.09.2011
| DE10019 | -Дмитрий- | | пермь | 5.140.81.95 | 21.11.2014
| DE10047 | Василий | | Cумы | 95.132.42.185 | 25.07.2014
| DE10054 | Maedhros | | Чикаго | 207.246.176.110 | 26.06.2014
| DE10062 | ssergw | | москва | 46.188.125.206 | 12.09.2014
| DE10086 | Вадим | | Тула | 109.111.26.176 | 26.02.2012
(10 rows)
如您所见,上面的b.id列为空 – 因为这10个用户未被禁止.
如何在该列中获取false值而不是String？
而且我不是在一些coalesceor case表达式之后,但我正在寻找“正确”的方式来进行这样的查询.
具有外连接的CASE或COALESCE语句是执行此操作的正确方法.
原文链接：https://www.f2er.com/postgresql/192158.htmlselect
CASE
WHEN b.id IS NULL THEN true
ELSE false
END AS banned,u.id,'DD.MM.YYYY') as day
from pref_users u
left outer join pref_ban2 b
on u.id=b.id
limit 10;
