我有一个如下表格
|-----|-----|--------|
| id | num | amount |
|-----|-----|--------|
| 101 | a | 10 |
| 102 | a | 6 |
| 102 | a | 3 |
| 101 | b | 5 |
| 101 | a | 10 |
| 101 | b | 5 |
|-----|-----|--------|
我要显示的是
|---------------------|------------------|---------------------|
| id | num | amount |
|---------------------|------------------|---------------------|
| 101 | a,b | 5 |
|---------------------|------------------|---------------------|
即,如果 num 表中的两个不同值具有相同的 id,则将它们的数量相加并除以 3,其他值将被丢弃。如何使用 mysql 实现此目的?如果相同的数据也重复两次,则只取一次并首次出现并消除另一个
我试过了
SELECT id, GROUP_CONCAT(DISTINCT num) AS nums, SUM(amount) / 3 AS amount
FROM table1
GROUP BY id
HAVING COUNT(DISTINCT num) > 1
但它计算这两个值,即金额是 10 而不是 5。
这是一个复杂的问题,因为表中没有自然排序。因此,我们必须通过为表中的每个条目生成行号来创建人工排序。我们使用此查询执行此操作:
SELECT @rownum1 := @rownum1 + 1 AS rownum, id, num, amount
FROM table1
JOIN (SELECT @rownum1 := 0) r
这将给出以下输出:
rownum id num amount
1 101 a 10
2 102 a 6
3 102 a 3
4 101 b 5
5 101 a 3
6 101 b 5
完成此操作后,我们现在可以为表示表中"第一个"条目的id
和num
的每个组合选择一组值:
SELECT id, num, MIN(rownum) AS rownum
FROM (SELECT @rownum2 := @rownum2 + 1 AS rownum, id, num, amount
FROM table1
JOIN (SELECT @rownum2 := 0) r
) t
GROUP BY id, num;
输出:
id num rownum
101 a 1
101 b 4
102 a 2
现在我们有了这些信息,我们可以将排序表JOIN
到此信息,然后使用该结果集上的原始查询来获取每组值的"首次"出现的结果:
SELECT t1.id, GROUP_CONCAT(DISTINCT t1.num) AS nums, SUM(t1.amount) / 3 AS amount
FROM (SELECT @rownum1 := @rownum1 + 1 AS rownum, id, num, amount
FROM table1
JOIN (SELECT @rownum1 := 0) r
) t1
JOIN (SELECT id, num, MIN(rownum) AS rownum
FROM (SELECT @rownum2 := @rownum2 + 1 AS rownum, id, num, amount
FROM table1
JOIN (SELECT @rownum2 := 0) r
) t
GROUP BY id, num) t2
ON t1.rownum = t2.rownum
GROUP BY t1.id
HAVING COUNT(DISTINCT t1.num) > 1;
输出:
id nums amount
101 a,b 5
SQLFiddle
您可以使用非重复的子查询,并与计数> 1 的 id 连接
select t1.id, GROUP_CONCAT( t1.num) AS nums, SUM(t1.amount) / 3 AS amount
from (
select distinct id, num, amount
from table1
group by id, num
) t1
INNER JOIN (
select id, num, count(*)
from table1
group by id, num
having count(*)>1
) t2 on t2.id = t1.id