列表列
问题描述:
想象这些表结构列表列
组织表
=====================
| id | name |
-----+----------------+
| 1 | Organization 1 |
=====================
代替表
=================================
| id | name | organization_id |
-----+----------+-----------------+
| 1 | Place 1 | 1 |
-----+----------+-----------------+
| 2 | Place 2 | 1 |
-----+----------+-----------------+
| 3 | Place 3 | 1 |
=================================
档案表
=============
| username |
--------------
| [email protected] |
--------------
| [email protected] |
=============
Visitedplace表
================================================
| id | place_id | profile_username | visiteddate |
-----+----------+---------------- -+-------------+
| 1 | 1 | [email protected] | 2017-01-01 |
-----+----------+------------------+-------------+
| 2 | 2 | [email protected] | 2017-02-01 |
-----+----------+------------------+-------------+
| 3 | 1 | [email protected] | 2017-01-15 |
================================================
下一步是创建表和数据插入到它的SQL statments
-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS "organization";
CREATE TABLE "organization" (
"id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL
) WITH (OIDS=FALSE);
-- ----------------------------
-- Records of organization
-- ----------------------------
BEGIN;
INSERT INTO "organization" VALUES ('1', 'Organization 1');
COMMIT;
-- ----------------------------
-- Table structure for place
-- ----------------------------
DROP TABLE IF EXISTS "place";
CREATE TABLE "place" (
"id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL,
"organization_id" int4 NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of place
-- ----------------------------
BEGIN;
INSERT INTO "place" VALUES ('1', 'Place 1', '1');
INSERT INTO "place" VALUES ('2', 'Place 2', '1');
INSERT INTO "place" VALUES ('3', 'Place 3', '1');
COMMIT;
-- ----------------------------
-- Table structure for profile
-- ----------------------------
DROP TABLE IF EXISTS "profile";
CREATE TABLE "profile" (
"username" varchar(255) COLLATE "default" NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of profile
-- ----------------------------
BEGIN;
INSERT INTO "profile" VALUES ('[email protected]');
INSERT INTO "profile" VALUES ('[email protected]');
COMMIT;
-- ----------------------------
-- Table structure for visitedplace
-- ----------------------------
DROP TABLE IF EXISTS "visitedplace";
CREATE TABLE "visitedplace" (
"id" int4 NOT NULL,
"place_id" int4 NOT NULL,
"profile_username" varchar(255) COLLATE "default" NOT NULL,
"visiteddate" date NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of visitedplace
-- ----------------------------
BEGIN;
INSERT INTO "visitedplace" VALUES ('1', '1', '[email protected]', '2017-02-24');
COMMIT;
-- ----------------------------
-- Alter Sequences Owned By
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table organization
-- ----------------------------
ALTER TABLE "organization" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Primary Key structure for table place
-- ----------------------------
ALTER TABLE "place" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Primary Key structure for table profile
-- ----------------------------
ALTER TABLE "profile" ADD PRIMARY KEY ("username");
-- ----------------------------
-- Primary Key structure for table visitedplace
-- ----------------------------
ALTER TABLE "visitedplace" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Foreign Key structure for table "place"
-- ----------------------------
ALTER TABLE "place" ADD FOREIGN KEY ("organization_id") REFERENCES
"organization" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
------------------------------
-- Foreign Key structure for table "visitedplace"
-- ----------------------------
ALTER TABLE "visitedplace" ADD FOREIGN KEY ("profile_username") REFERENCES "profile" ("username") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "visitedplace" ADD FOREIGN KEY ("place_id") REFERENCES "place" ("id
") ON DELETE CASCADE ON UPDATE CASCADE;
我的问题是
当我运行这下查询
select profile.username, count(distinct place.id)
from profile profile
left outer join visitedplace visitedplace on profile.username=visitedplace.profile_username
inner join place place on visitedplace.place_id=place.id
where place.organization_id=1
group by profile.username
它返回一个结果
=====================
| username | count |
-------------+-------
| [email protected] | 2 |
=====================
但我期待下一个结果
=====================
| username | count |
-------------+-------
| [email protected] | 2 |
-------------+-------
| [email protected] | 0 |
=====================
所以,我该怎么办查询返回我什么,我期待?
我使用Postgres的
答
用途使用left join
。所以,所有连接都应该是外连接(在第一个之后)。而且,你必须要小心的where
条款:
select p.username, count(distinct place.id)
from profile p left outer join
visitedplace vp
on p.username = vp.profile_username left join
place pl
on vp.place_id = pl.id and pl.organization_id = 1
group by p.username;
如果你不小心,那么外连接变成一个inner join
。
+0
是的,它现在工作,谢谢:) –
你真的使用所有这些(有点)过时的版本? –
@a_horse_with_no_name不能得到你的问题,你能澄清请,thnx –