I have a work for school and I have this table (not complete I simplified it):
我有一份上学的工作，我有这张桌子（不完整，我简化了）：
email region card quantidade
-------------- ---------- ----------- ----------
dummyEmail_1@box.net EU Flamestrike 2
dummyEmail_1@box.net EU Fireball 2
dummyEmail_1@box.net EU Polymorph 3
dummyEmail_1@box.net EU Water Eleme 6
dummyEmail_1@box.net EU Arcane Expl 3
dummyEmail_1@box.net EU Frostbolt 2
dummyEmail_1@box.net EU Arcane Miss 8
dummyEmail_1@box.net EU Mirror Imag 2
dummyEmail_1@box.net EU Archamage A 1
dummyEmail_1@box.net EU Pyroblast 2
dummyEmail_1@box.net US Pyroblast 1
dummyEmail_1@box.net EU Kirin Tor M 3
dummyEmail_1@box.net EU Goblin Blas 2
dummyEmail_1@box.net EU Unstable Po 11
dummyEmail_1@box.net EU Iron sensei 2
dummyEmail_1@box.net EU Shady Deale 2
dummyEmail_1@box.net EU Backstab 5
dummyEmail_1@box.net EU Deadly Pois 3
protoEmail_2@box.net EU Deathwing 2
protoEmail_2@box.net EU Nefarian 2
protoEmail_2@box.net EU Volcanic Dr 2
protoEmail_2@box.net EU Twilight Dr 2
protoEmail_2@box.net EU Cenarius 2
protoEmail_2@box.net EU King Krush 2
protoEmail_2@box.net EU Archmage An 2
protoEmail_2@box.net EU Tirion Ford 2
protoEmail_2@box.net EU Prophet Vel 2
protoEmail_2@box.net EU Edward VanC 2
protoEmail_2@box.net EU Al'Akir the 2
protoEmail_2@box.net EU Lord Jaraxx 2
protoEmail_2@box.net EU Grommash He 2
protoEmail_2@box.net EU Dr.Boom 2
protoEmail_2@box.net EU Mimiron's H 2
protoEmail_2@box.net EU Bolvar Ford 2
And want to output this from a query:
并希望从查询中输出：
email card region
-------------- ------------------- ----------
protoEmail_2@box.net Grommash Hellscream EU
protoEmail_2@box.net Grommash Hellscream US
protoEmail_2@box.net Lord Jaraxxus EU
protoEmail_2@box.net Lord Jaraxxus AS
dummyEmail_2@box.net Mind Control EU
dummyEmail_2@box.net Mind Control US
dummyEmail_2@box.net Pyroblast EU
dummyEmail_2@box.net Pyroblast US
dummyEmail_2@box.net Shady Dealer EU
dummyEmail_2@box.net Shady Dealer US
dummyEmail_2@box.net Unstable Portal EU
dummyEmail_2@box.net Unstable Portal US
protoEmail_2@box.net Varian Wrynn EU
protoEmail_2@box.net Varian Wrynn US
i.e: I want to select all the duplicates from email and card and show the region of the duplicate card/email
即：我想从电子邮件和卡中选择所有重复项，并显示重复卡/电子邮件的区域
I tried this query but it gives me a syntax error: SELECT email, card, region FROM player_card WHERE (email,card) IN (SELECT email,card FROM player_card GROUP BY email,card HAVING count(*) > 1) ORDER BY card;
我尝试了这个查询，但它给了我一个语法错误：SELECT电子邮件，卡片，区域FROM player_card WHERE（电子邮件，卡片）IN（SELECT电子邮件，卡片来自player_card GROUP BY电子邮件，卡片HAVING count（*）> 1）ORDER BY卡;
Table creation statement:
表创建语句：
CREATE TABLE player_card
(email VARCHAR(30),
region VARCHAR(2) CHECK (region IN("US", "EU", "AS")),
card VARCHAR(30),
quantidade INTEGER NOT NULL,
PRIMARY KEY(email, region, card),
FOREIGN KEY(email) REFERENCES player(email) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(region) REFERENCES player(region),
FOREIGN KEY(card) REFERENCES card(name)
);
1 个解决方案
#1
0
I could get the result by using join instead of the in operator:
我可以通过使用join而不是in运算符来获得结果：
SELECT p.email, p.card, p.region FROM player_card p join
(SELECT email,card, count(*) as cnt FROM player_card GROUP BY email,card) t
on p.email = t.email and p.card=t.card where t.cnt>1
ORDER BY p.card;
实例
#1
0
I could get the result by using join instead of the in operator:
我可以通过使用join而不是in运算符来获得结果：
SELECT p.email, p.card, p.region FROM player_card p join
(SELECT email,card, count(*) as cnt FROM player_card GROUP BY email,card) t
on p.email = t.email and p.card=t.card where t.cnt>1
ORDER BY p.card;
实例