I want to display all duplicate records from my table, rows are like this
我想从我的表中显示所有重复的记录，行是这样的
uid planet degree
1 1 104
1 2 109
1 3 206
2 1 40
2 2 76
2 3 302
I have many different OR statements with different combinations in subquery and I want to count every one of them which matches, but it only displays the first match of each planet and degree.
我在子查询中有许多不同的OR语句和不同的组合，我想计算每个匹配的OR，但它只显示每个行星和度数的第一个匹配。
Query:
查询：
SELECT DISTINCT
p.uid,
(SELECT COUNT(*)
FROM Params AS p2
WHERE p2.uid = p.uid
AND(
(p2.planet = 1 AND p2.degree BETWEEN 320 - 10 AND 320 + 10) OR
(p2.planet = 7 AND p2.degree BETWEEN 316 - 10 AND 316 + 10)
...Some more OR statements...
)
) AS counts FROM Params AS p HAVING counts > 0 ORDER BY p.uid DESC
any solution folks?
任何解决方案的人？
1 个解决方案
#1
1
updated
更新
So, the problem most people have with their counting-joined-sub-query-group-queries, is that the base query isn't right, and the following may seem like a complete overkill for this question ;o)
因此，大多数人对其计数加入子查询组查询的问题是基本查询不正确，以下看起来似乎对此问题完全过分; o）
base data
in this particular example what you would want as a data basis is at first this:
在这个特定的例子中，您首先想要的是数据基础：
(uidA, planetA, uidB, planetB) for every combination of player A and player B planets. that one is quite simple (l is for left, r is for right):
（uidA，planetA，uidB，planetB）适用于玩家A和玩家B行星的每个组合。一个很简单（l代表左，r代表右）：
SELECT l.uid, l.planet, r.uid, r.planet
FROM params l, params r
first step done.
第一步完成。
filter data
now you want to determine if - for one row, meaning one pair of planets - the planets collide (or almost collide). this is where the WHERE comes in.
现在你要确定 - 对于一行，即一对行星 - 行星是否碰撞（或几乎碰撞）。这就是WHERE的用武之地。
WHERE ABS(l.degree-r.degree) < 10
would for example only leave those pairs of planet with a difference in degrees of less than 10. more complex stuff is possible (your crazy conditional ...), for example if the planets have different diameter, you may add additional stuff. however, my advise would be, that you put some additional data that you have in your query into tables.
例如，只留下那些行星的度数小于10的行星。更复杂的东西是可能的（你的疯狂条件......），例如如果行星有不同的直径，你可以添加额外的东西。但是，我的建议是，您将查询中的一些其他数据放入表中。
for example, if all 1st planets players have the same size, you could have a table with (planet_id, size). If every planet can have different sizes, add the size to the params table as a column.
例如，如果所有第一行星玩家具有相同的大小，则可以使用（planet_id，size）的表格。如果每个行星都有不同的大小，请将该大小作为列添加到params表中。
then your WHERE clause could be like:
那么你的WHERE子句可能是这样的：
WHERE l.size+r.size < ABS(l.degree-r.degree)
if for example two big planets with size 5 and 10 should at least be 15 degrees apart, this query would find all those planets that aren't.
例如，如果两个大小为5和10的大行星应至少相隔15度，则此查询将找到所有那些不是的行星。
we assume, that you have a nice conditional, so at this point, we have a list of (uidA, planetA, uidB, planetB) of planets, that are close to colliding or colliding (whatever semantics you chose). the next step is to get the data you're actually interested in:
我们假设你有一个很好的条件，所以在这一点上，我们有一个行星的（uidA，planetA，uidB，planetB）列表，它们接近碰撞或碰撞（无论你选择什么语义）。下一步是获取您真正感兴趣的数据：
limit uidA to a specific user_id (the currently logged in user for example)
add l.uid = <uid> to your WHERE.
将l.uid =
count for every planet A, how many planets B exist, that threaten collision
add GROUP BY l.uid, l.planet,
添加GROUP BY l.uid，l.planet，
replace r.uid, r.planet with count(*) as counts in your SELECT clause
将r.unet，r.planet替换为count（*）作为SELECT子句中的计数
then you can even filter: HAVING counts > 1 (HAVING is the WHERE for after you have GROUPed)
然后你甚至可以过滤：HAVING count> 1（拥有GROUPed后HAVING是WHERE）
and of course, you can
当然，你可以
filter out certain players B that may not have planetary interactions with player A
add to your WHERE
添加到您的WHERE
r.uid NOT IN (1)
find only self collisions
WHERE l.uid = r.uid
find only non-self collisions
WHERE l.uid <> r.uid
find only collisions with one specific planet
WHERE l.planet = 1
conclusion
a structured approach where you start from the correct base data, then filter it appropriately and then group it, is usually the best approach. if some of the concepts are unclear to you, please read up on them online, there are manuals everywhere
一种结构化的方法，从正确的基础数据开始，然后适当地过滤它然后分组，通常是最好的方法。如果您不清楚某些概念，请在线阅读，到处都有手册
final query could look something like this
最终查询可能看起来像这样
SELECT l.uid, l.planet, count(*) as counts
FROM params l, params r
WHERE [ collision-condition ]
GROUP BY l.uid, l.planet
HAVING counts > 0
if you want to collide a non-planet object, you might want to either make a "virtual table", so instead of FROM params l, params r you do (with possibly different fields, I just assume you add a size-field that is somehow used):
如果你想要碰撞一个非行星物体，你可能想要制作一个“虚拟表”，所以不要使用FROM params l，而是使用params（可能有不同的字段，我只是假设你添加一个大小字段，以某种方式使用）：
FROM params l, (SELECT 240 as degree, 2 as planet, 5 as size) r
multiple:
多：
FROM params l, (SELECT 240 as degree, 2 as planet, 5 as size
UNION
SELECT 250 as degree, 3 as planet, 10 as size
UNION ...) r
#1
1
updated
更新
So, the problem most people have with their counting-joined-sub-query-group-queries, is that the base query isn't right, and the following may seem like a complete overkill for this question ;o)
因此，大多数人对其计数加入子查询组查询的问题是基本查询不正确，以下看起来似乎对此问题完全过分; o）
base data
in this particular example what you would want as a data basis is at first this:
在这个特定的例子中，您首先想要的是数据基础：
(uidA, planetA, uidB, planetB) for every combination of player A and player B planets. that one is quite simple (l is for left, r is for right):
（uidA，planetA，uidB，planetB）适用于玩家A和玩家B行星的每个组合。一个很简单（l代表左，r代表右）：
SELECT l.uid, l.planet, r.uid, r.planet
FROM params l, params r
first step done.
第一步完成。
filter data
now you want to determine if - for one row, meaning one pair of planets - the planets collide (or almost collide). this is where the WHERE comes in.
现在你要确定 - 对于一行，即一对行星 - 行星是否碰撞（或几乎碰撞）。这就是WHERE的用武之地。
WHERE ABS(l.degree-r.degree) < 10
would for example only leave those pairs of planet with a difference in degrees of less than 10. more complex stuff is possible (your crazy conditional ...), for example if the planets have different diameter, you may add additional stuff. however, my advise would be, that you put some additional data that you have in your query into tables.
例如，只留下那些行星的度数小于10的行星。更复杂的东西是可能的（你的疯狂条件......），例如如果行星有不同的直径，你可以添加额外的东西。但是，我的建议是，您将查询中的一些其他数据放入表中。
for example, if all 1st planets players have the same size, you could have a table with (planet_id, size). If every planet can have different sizes, add the size to the params table as a column.
例如，如果所有第一行星玩家具有相同的大小，则可以使用（planet_id，size）的表格。如果每个行星都有不同的大小，请将该大小作为列添加到params表中。
then your WHERE clause could be like:
那么你的WHERE子句可能是这样的：
WHERE l.size+r.size < ABS(l.degree-r.degree)
if for example two big planets with size 5 and 10 should at least be 15 degrees apart, this query would find all those planets that aren't.
例如，如果两个大小为5和10的大行星应至少相隔15度，则此查询将找到所有那些不是的行星。
we assume, that you have a nice conditional, so at this point, we have a list of (uidA, planetA, uidB, planetB) of planets, that are close to colliding or colliding (whatever semantics you chose). the next step is to get the data you're actually interested in:
我们假设你有一个很好的条件，所以在这一点上，我们有一个行星的（uidA，planetA，uidB，planetB）列表，它们接近碰撞或碰撞（无论你选择什么语义）。下一步是获取您真正感兴趣的数据：
limit uidA to a specific user_id (the currently logged in user for example)
add l.uid = <uid> to your WHERE.
将l.uid =
count for every planet A, how many planets B exist, that threaten collision
add GROUP BY l.uid, l.planet,
添加GROUP BY l.uid，l.planet，
replace r.uid, r.planet with count(*) as counts in your SELECT clause
将r.unet，r.planet替换为count（*）作为SELECT子句中的计数
then you can even filter: HAVING counts > 1 (HAVING is the WHERE for after you have GROUPed)
然后你甚至可以过滤：HAVING count> 1（拥有GROUPed后HAVING是WHERE）
and of course, you can
当然，你可以
filter out certain players B that may not have planetary interactions with player A
add to your WHERE
添加到您的WHERE
r.uid NOT IN (1)
find only self collisions
WHERE l.uid = r.uid
find only non-self collisions
WHERE l.uid <> r.uid
find only collisions with one specific planet
WHERE l.planet = 1
conclusion
a structured approach where you start from the correct base data, then filter it appropriately and then group it, is usually the best approach. if some of the concepts are unclear to you, please read up on them online, there are manuals everywhere
一种结构化的方法，从正确的基础数据开始，然后适当地过滤它然后分组，通常是最好的方法。如果您不清楚某些概念，请在线阅读，到处都有手册
final query could look something like this
最终查询可能看起来像这样
SELECT l.uid, l.planet, count(*) as counts
FROM params l, params r
WHERE [ collision-condition ]
GROUP BY l.uid, l.planet
HAVING counts > 0
if you want to collide a non-planet object, you might want to either make a "virtual table", so instead of FROM params l, params r you do (with possibly different fields, I just assume you add a size-field that is somehow used):
如果你想要碰撞一个非行星物体，你可能想要制作一个“虚拟表”，所以不要使用FROM params l，而是使用params（可能有不同的字段，我只是假设你添加一个大小字段，以某种方式使用）：
FROM params l, (SELECT 240 as degree, 2 as planet, 5 as size) r
multiple:
多：
FROM params l, (SELECT 240 as degree, 2 as planet, 5 as size
UNION
SELECT 250 as degree, 3 as planet, 10 as size
UNION ...) r