PostgreSQL 数据库
CREATE TABLE items (
id SERIAL,
user_id INTEGER,
status SMALLINT
);
CREATE TABLE phones (
phone VARCHAR(12),
users INTEGER[]
);
其中 items.status 为 3、5 或 7
phone1, phone2... 列表需要选择下面的表格
phone1;COUNT(items.id) where status=3 ;COUNT(items.id) where status=7 phone2;COUNT(items.id) where status=3 ;COUNT(items.id) where status=7 ...
我的版本乘以重复然后总结
SELECT phones.phone as phone, COUNT(it1.id) as saled_count, COUNT(it2.id) as not_saled_count
FROM phones
INNER JOIN items it1 ON phones.users @> ARRAY[it1.user_id]::int[]
INNER JOIN items it2 ON phones.users @> ARRAY[it2.user_id]::int[]
WHERE phones.phone IN ('phone1', 'phone2')
AND it1.status = 7 AND it2.status = 3
GROUP BY phones.phone;
结果分别
phone1 5 5
phone2 6 6
...
代替
phone1 3 2
phone2 5 1
...
那么,问题本身:如何提出请求?
如果我正确理解问题