我有一个存储条件的表
CREATE TABLE conditions (field_id TEXT, value BOOLEAN)
像这样的条目
| field_id | 价值 |
|---|---|
| f1 | 真的 |
| f2 | 错误的 |
另一个表将用户的字段值存储在 jsonb 列中
CREATE TABLE users (id BIGINT, fields JSON)
像这样的条目
| ID | 价值观 |
|---|---|
| 1 | {“f1”：真，“f2”：假} |
| 2 | {“f1”：真，“f2”：真} |
| 3 | {“f1”：假，“f2”：假} |
我想编写一个查询，在我的示例中只返回 1 的用户。
我有以下数据：
| 机构_no | 信用类型 |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
我有一个列表框，因此用户可以选择多个 Cred_type，他们希望能够选择 OR 或 AND 条件。
对于 OR 我有这样的 AND CRED_TYPE IN (1,2)
对于AND，我真的很摸不着头脑。他们的意思是他们想要一份具有 cred type 1 和 cred_type 2 的机构列表。也许我没有想清楚，但这是逐行的，所以这样做不会导致任何结果。
AND cred_type = 1 AND cred_type = 2 - 你不能让单行有两个不同的值，这将不会返回任何结果。
它们要求用户可以选择 10、20 或更多，因此为每个编写一堆代码并将它们全部组合起来真的很困难 - 但这是我迄今为止唯一的想法。会是这样
Select institution_no from table where cred_type = 1
UNION
Select institution_no from table where cred_type = 2
-- 这将结合两者并得到我想要的，但你可以想象其中 10 或 20 个的所有代码。
我的用户表中有以下行
现在我想让用户拥有所有访问类型作为查看、编辑和共享。所以我需要从中得到两行
如何使用 postgresql 语句实现这一点。
我得到了架构：
员工（Fname，Ssn，Dno）
部门（Dname，Dnumber，Mgr_ssn，Mgr_start_date）
项目（Pname，Pnumber，位置，Dno）
WORKS_ON（Essn，Pno）
小提琴：https ://www.db-fiddle.com/f/9LnJN32WLVu1HsdmG8NUAc/5
主键以粗体显示。
问题：是否可以定义一个视图来报告每个员工的以下详细信息：Ssn 和从事完全相同项目集的员工数量？解释为什么不这样做或给出一个 SQL 查询。
我的想法是找到唯一的员工对并检查第一个员工工作的项目是否等于第二个员工的项目（这是通过使用 set 操作完成的，除非 s1 除了 s2 联合 s2 除了 s1 是空的，而不是看起来像 s1 和 s2相等）
我的尝试是：
SELECT e1.ssn,count(e2.ssn)
FROM EMPLOYEE e1, EMPLOYEE e2
WHERE e1.ssn < e2.ssn
AND NOT EXISTS
(
(
(SELECT Pno
FROM WORKS_ON w1
WHERE w1.Essn = e1.Ssn)
EXCEPT ALL
(SELECT Pno
FROM WORKS_ON w2
WHERE w2.Essn = e2.Ssn)
)
UNION ALL
(
(SELECT Pno
FROM WORKS_ON w2
WHERE w2.Essn = e2.Ssn)
EXCEPT ALL
(SELECT Pno
FROM WORKS_ON w1
WHERE w1.Essn = e1.Ssn)
)
)
GROUP BY(e1.ssn)
这个查询看起来正确吗？还有一种更简洁的方法吗？
作为最后的附注，究竟是什么决定了我们是否不可能为问题构建 SQL 查询？
如果有人可以帮助我完成下面的查询，那就太好了！这个查询非常具有挑战性，我尝试自己这样做，但无法显示正确的结果......
我使用类似于“Facebook”的系统的数据库。
查询应显示在过去一年中为每个帖子撰写评论的用户的电子邮件和姓名，这些帖子由他们的每个朋友发布。
意思是，我只想显示对所有朋友的所有帖子发表评论的人。
这是我的试验，但我收到了部分结果。我无法理解我做错了什么。
select distinct U.Mail, U.FirstName + ' ' + U.LastName as FullName
from Users U
inner join FriendsList FL on U.Mail = FL.Mail1
inner join Post P on FL.Mail2 = P.UserMail
left outer join Comment C on P.ID = C.IDPost and P.UserMail <> C.Mail
where datediff(year, P.DatePosted, getdate()) <= 1
group by U.Mail, U.FirstName, U.LastName
having count(distinct P.ID) = count(distinct C.IDPost)
链接到 BAK 文件（包括测试数据）： https ://file.io/SagvM3cx
表格图：
例子：
假设我和亚当和本是朋友。
⭐️ 第一种情况：
然后，我的名字不应该显示在查询中。?
然后，我的名字应该显示在查询中。?
⭐️第二种情况：
然后，我的名字不应该显示在查询中。?
然后，我的名字应该显示在查询中。?
⭐️ 第三种情况：
然后，我的名字应该显示在查询中。?
编辑：我添加了 8 个查询 - 4create table和 4 insert。根据这个例子，上面的查询应该只显示Kelly
create table Users
(
Mail nvarchar (20) primary key check(Mail like '_%@_%._%' and (Mail like '%[0-9]%' Or Mail like '%[a-z]%'Or Mail like '%[A-Z]%')),
Password nvarchar (8) check (Password like '%[0-9]%' and Password like '%[az]%' and len(password) <= 8) not null,
FirstName nvarchar (20) not null,
LastName nvarchar (20) not null,
BirthDate date check (datediff(year,BirthDate,getdate())>=18) not null,
JoinDate date check (JoinDate<=getdate()) not null,
Gender nchar(1) check(Gender = 'F' or Gender = 'M' or Gender = 'O'),
NickName nvarchar(20),
Photo nvarchar(20),
Phone bigint check (Phone like '%[0-9]%' and len(Phone) <= 10) not null
)
INSERT INTO Users
VALUES
('[email protected]','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('[email protected]','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('[email protected]','m120121','Moshe','Ma','1995-06-03','2011-04-02','M','Moshe','MosheMa.jpg','542840111'),
('[email protected]','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567');
('[email protected]','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');
create table FriendsList
(
Mail1 nvarchar (20) references Users(Mail) not null,
Mail2 nvarchar (20) references Users(Mail) not null,
DateAdding date check (DateAdding<=getDate()) not null,
primary key (Mail1,Mail2)
)
INSERT INTO FriendsList
VALUES
('[email protected]','[email protected]','2018-04-18'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2018-04-18');
('[email protected]','[email protected]','2020-04-22'),
create table Post
(
ID int identity(1,1) primary key,
Photo nvarchar(20),
Text nvarchar(200),
Location nvarchar(50),
Video int,
DatePosted date check (datediff(month,DatePosted,getdate())<=3) not null,
UserMail nvarchar (20) references Users(Mail) on delete cascade on update
cascade not null
)
INSERT INTO Post
VALUES
('','my name is nellu','','','2020-04-18','[email protected]'),
('','hii','','','2020-05-19','[email protected]');
create table Comment
(
IDPost int references Post(ID) on delete cascade on update cascade not null,
SerialNumComment int check(SerialNumComment > 0) not null,
DateAndTimeComment Datetime check (DateAndTimeComment<=getDate()) not null,
Text nvarchar(200) not null,
Mail nvarchar (20) references Users(Mail) not null,
primary key (IDPost,SerialNumComment)
)
INSERT INTO Comment
VALUES
('1','1','2020-04-18','blabla','[email protected]'),
('2','1','2020-05-05','bhfk','[email protected]');
我有一个案例，我想提取DID在特定时间段内每天存在一次且仅存在一次的设备 ID ( )。我尝试了不同的方法和分区，但我似乎只能每天单独获取该数据（where date = X），但我需要使用where date between X and Y.
示例数据：
DID date
A 2019-01-01
A 2019-01-01
A 2019-01-02
A 2019-01-03
B 2019-01-01
B 2019-01-02
B 2019-01-03
C 2019-01-01
C 2019-01-02
C 2019-01-02
C 2019-01-03
D 2019-01-01
D 2019-01-02
D 2019-01-03
查询应该只返回B & D因为从 01 到 03 每天存在一次。
我还希望得到count，在这种情况下为2
试图弄清楚如何找出哪些用户记录至少启用了 1 个类似选项，然后显示这些。
表：
user (id, username)
thing (id,title)
uthing (id, uid, tid)
Uthing表记录了每个用户选择了哪些选项。
uid = user.id
tid = thing.id
我需要将uthing用户 ID 1 的选项 ( ) 与所有其他用户进行比较。
如何找到具有uthingUSER ID 1 的 1 个或多个 options( ) 的用户，并使用找到的选项返回这些用户？
此查询将为我提供用户 ID 1 的所有选项。
select thing.title,thing.id as thingid
from thing join uthing on uthing.tid = thing.id where uthing.uid = 1;
给定以下架构：
User
id
Group
id
Membership
group_id
user_id
(With a unique index on group_id, user_id)
我将如何选择相同的两个用户所属的组？
我现在拥有的：
SELECT "Membership"."group_id"
FROM "Membership"
WHERE "Membership"."user_id" IN (1, 2)
GROUP BY "Membership"."group_id"
HAVING COUNT("Membership"."group_id") >= 2
这似乎有效（它只返回两个用户都是其成员的组 ID），但看起来相当混乱。
有没有更好的方式来表达这个查询？
编辑
所以给出以下数据：
Users
-----
id
1
2
3
Group
-----
id
1
2
3
Membership
----------
group_id user_id
1 1
1 2
2 1
2 3
3 2
3 3
我想找到用户 1 和 2 都是其成员的组。
所以 user_id 1 和 2 的查询结果应该是组 1。对 user_id 1 和 3 的相同查询应该返回组 2。而对 user_id 2 和 3 的查询应该返回组 3。
这些是我创建的表并相应地插入了值：
CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
Customer_Name VARCHAR(30) NOT NULL
)
CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
DVD_Name VARCHAR(30)
)
CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
DVD_No INTEGER NOT NULL,
Customer_No INTEGER NOT NULL
)
INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
为了显示购买了所有 DVD 的客户的姓名，这是我尝试过的查询：
SELECT Customer_Name
FROM Customer
WHERE
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)
但不幸的是，执行此查询后，它会显示所有客户名称。但我想显示购买了所有 DVD 的特定客户名称。
有人可以向我提供正确的查询吗？
一个 Item 有很多 ItemDetails。ItemDetail 具有“type”、“value”和“item_id”字段。
当且仅当项目具有受某些可变条件限制的确切ItemDetails 时，我才需要查找所有项目。例如，我需要找到 ItemDetails 为 (type=10, value=1000) 和 (type=20 and value=2000) 的所有项目
我的第一个解决方案是这样的：
select p.*
from item p
where not exists
(
select c.id from item_detail c
where c.item_id=p.id
and (c.type<>10 or c.value<>1000)
and (c.type<>20 or c.value<>2000)
);
-- Execution Time: 17.819 ms
但我意识到它只使用一个 ItemDetail(type=10, value=1000) 获取项目。然后我发现了这个问题并改变了如下查询。
select p.*
from item p
where not exists
(
select c.id from item_detail c
where c.item_id=p.id
and (c.type<>10 or c.value<>1000)
and (c.type<>20 or c.value<>2000)
)
and 2 = (
select count(c.item_id) from item_detail c
where c.item_id=p.id);
-- Execution Time: 2426.596 ms
但是第二个子查询导致性能问题。第一次查询的执行时间是 5 毫秒，但第二次是 800 毫秒。有没有更好的方法来做到这一点？
我正在使用 PostgreSQL 9.5。
这是它的小提琴。
Edit-1：对于那些无法到达它的人来说，这是小提琴示例：
CREATE TABLE public.item
(
id integer NOT NULL,
name character varying(10) NOT NULL,
CONSTRAINT item_pkey PRIMARY KEY (id)
);
CREATE TABLE public.item_detail
(
id bigint NOT NULL,
item_id integer NOT NULL,
type integer NOT NULL,
value integer NOT NULL,
CONSTRAINT item_detail_pkey PRIMARY KEY (id),
CONSTRAINT fk_item_id FOREIGN KEY (item_id)
REFERENCES item (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uq_item_type_value UNIQUE (item_id, type, value)
);
INSERT INTO public.item VALUES (1, 'Item1'),(2, 'Item2'),(3, 'Item3'),(4, 'Item4'),(5, 'Item5');
INSERT INTO public.item_detail
VALUES
(1,1,10,1000),
(2,1,20,2000),
(3,2,10,1000),
(4,3,10,1000),
(5,3,20,2000),
(6,3,30,3000),
(7,4,10,1000),
(8,4,10,1500);
编辑 2：我通过从ypercubeᵀᴹ 给出的源中选择最适合我的选项来提出这样的解决方案。
SELECT p.* FROM item p
WHERE EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) = (10, 1000))
AND EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) = (20, 2000))
AND NOT EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) NOT IN ((10, 1000), (20,2000)));
-- Execution Time: 0.984 ms