MySQL联盟查询重复/按问题分组 [英] MySQL Union query duplicate/group by problem

问题描述

只需了解UNION查询-节省时间-但在分组时遇到了麻烦.

Just learning about UNION queries - what a timesaver - but having trouble with grouping.

我有以下声明:

(SELECT `shops`.shpUserAdded AS userName, count(`shops`.shpID) AS shpCount, "" AS prdCount FROM `shops` GROUP BY shpUserAdded ASC)
UNION
(SELECT `products`.prdUserAdded AS userName, "" AS shpCount, count(`products`.prdID) AS prdCount FROM `products` GROUP BY prdUserAdded ASC)

但这给了我第一个查询的结果，然后是第二个查询的结果，而没有组合用户名:

but this gives me the results from the first query followed by the results of the second query, without combining the usernames:

userName|merCount|prdCount 
        |       8|
Jane    |       1|
Derek   |       1|
James   |      22|
        |        |       3
Jane    |        |       4  
Derek   |        |       5
James   |        |      21

我尝试过(但显然得到了#1248 - Every derived table must have its own alias):

I tried (but obviously got #1248 - Every derived table must have its own alias):

SELECT userName, shpCount, prdCount FROM
((SELECT `shops`.shpUserAdded AS userName, count(`shops`.shpID) AS shpCount, "" AS prdCount FROM `shops` GROUP BY shpUserAdded ASC)
UNION
(SELECT `products`.prdUserAdded AS userName, "" AS shpCount, count(`products`.prdID) AS prdCount FROM `products` GROUP BY prdUserAdded ASC))
GROUP BY userName ASC

我似乎无法在不从shpCount或prdCount列中擦除数据的情况下命名派生表.我可能会觉得很愚蠢，但是塞拉耶戈！！

I can't seem to figure out naming the derived table(s) without wiping out the data from either the shpCount or prdCount column - any suggestions? I'm probably going to feel stupid but thereyago!

解决了(这次是真的)！ :)

Solved it (really this time)! :)

SELECT Users.userName, Products.prdCount, Shops.shpCount FROM 
(
    (
        (SELECT `Shops`.shpAddU AS userName FROM `Shops` GROUP BY shpAddU ASC)
        UNION
        (SELECT `Products`.prdAddU AS userName FROM `Products` GROUP BY prdAddU ASC)
    ) AS Users
) 
LEFT JOIN 
(SELECT prdAddU AS userName, count(*) AS prdCount FROM `Products` GROUP BY prdAddU) as Products ON Users.userName = Products.userName
LEFT JOIN 
(SELECT shpAddU AS userName, count(*) AS shpCount FROM `Shops` GROUP BY shpAddU) as Shops ON Users.userName = Shops.userName

推荐答案

并非所有内容都是一个联合-在这种情况下，它是子查询上的一个联接:

Not everything is a union -- in this case it's a join on the subqueries:

SELECT shpUserAdded AS userName, merCount, prdCount
FROM (SELECT shpUserAdded, count(*) AS shpCount FROM shops GROUP BY shpUserAdded ASC) AS s
JOIN (SELECT prdUserAdded, count(*) AS prdCount FROM products GROUP BY prdUserAdded ASC) AS p
ON (shpUserAdded = prdUserAdded);

这篇关于MySQL联盟查询重复/按问题分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！