高级 SQL 查询设计帮助(跨两个表、多个字段、基于一个字段的可能排除项) [英] Advanced SQL Query Design Help (Duplicates across two tables, multiple fields, possible exclusions based on one field)

问题描述

我有两个表，命名为:

1) 项目

2) 银行项目

项目具有以下字段:

ID, CharID, Name, ItemID, Count, Type, ID1, ID2, ID3, Color, Effect1, Effect2, Effect3, LifeSpan, Attribute, Equip, X, Y

ID, CharID, Name, ItemID, Count, Type, ID1, ID2, ID3, Color, Effect1, Effect2, Effect3, LifeSpan, Attribute, Equip, X, Y

Bankitem 有以下字段:

Bankitem has the following fields:

ID、CharID、名称、ItemID、计数、类型、ID1、ID2、ID3、颜色、Effect1、Effect2、Effect3、LifeSpan、属性

ID, CharID, Name, ItemID, Count, Type, ID1, ID2, ID3, Color, Effect1, Effect2, Effect3, LifeSpan, Attribute

目标:

我想从这两个具有以下共同字段的表中提取单个列表(即，不仅仅是计数):名称、ItemID、ID1、ID2 和 ID3，以便我可以扫描列表和在删除其中一个之前，调查任何重复的条目.根据数据库更新方式的性质，每个其他字段都可能不同.

I would like to pull an individual listing (i.e., not just a count) from across these two tables that have the following fields in common: Name, ItemID, ID1, ID2, and ID3 so that I can scan the listing and investigate any duplicate entries, prior to deleting one of them. Every other field could potentially be different based on the nature of how the database is updated.

此外，有时可能会有某些记录可以重复(同样，基于数据库使用方式的性质).如果名称"字段等于我可以设置的排除项之一，是否可以在脚本中构建以排除重复项(基于上述字段)?

Also, sometimes there may certain records that are okay to have duplicates (again, based on the nature of how the database is used). Is there a way to build into the script to exclude duplicates (based on the above fields) if the field "Name" equals one of the exclusions I could set up?

任何帮助将不胜感激.我对 SQL 查询比较陌生 - 我知道我想做什么，但真的不知道如何进行下一步.

Any help would be greatly appreciated. I am relatively new to SQL queries - I know what I want to do, but am really lost on how to get to the next step.

谢谢.

推荐答案

SELECT Name, ItemID, ID1, ID2,ID3 FROM item
UNION ALL
SELECT Name, ItemID, ID1, ID2,ID3 FROM bankitem

或者，如果您不想要重复项，请使用 UNION.如果您不想在表中出现重复项，只需对该列使用唯一索引.

Or use UNION if you don't want duplicates.If you dont want duplicates in the table just use an unique index on that column.

SELECT * FROM(SELECT Name, ItemID, ID1, ID2, ID3, count(*) no_of_records FROM item 
UNION 
SELECT Name, ItemID, ID1, ID2, ID3, count(*) no_of_records FROM bankitem 
GROUP BY Name, ItemID, ID1, ID2, ID3 HAVING count(*) > 1)as x 
WHERE x.Name != 'RedPotion'

或

WHERE x.Name NOT IN('blah1','blah2')

这篇关于高级 SQL 查询设计帮助(跨两个表、多个字段、基于一个字段的可能排除项)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！