写数据库语句遇到问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

• 请不要在回答技术问题时复制粘贴 AI 生成的内容

This topic created in 3184 days ago, the information mentioned may be changed or developed.

表有两列
a b
1 2
1 3
2 3
2 1
2 2

现在要求出所有的数字一共出出现了几列
结果应该是
1 3
2 4
3 3

用数据库写我想到的方法是分别求出两列个数，相加以后减去相同的个数

个数

数据库

语句

相加

8 replies • 2017-08-13 13:05:27 +08:00

yejinmo

Aug 8, 2017

结果应该是
1 3
2 5
3 3
吧

xxoxx

Aug 8, 2017

目测了一下，结果应该是
1 3
2 5
3 2

yejinmo

Aug 8, 2017

select a, count(a) from
(
(select a from test union all select b from test) as temp
)
group by a;

yejinmo

Aug 8, 2017

@yejinmo
1 3
2 5
3 2

yuanfnadi

Aug 8, 2017 via iPhone

重复的不算
应该是
13
24
32

33 是手抖了

akira

Aug 8, 2017

SELECT a, SUM(`t`) `count`
FROM
(
SELECT a, COUNT(a) `t`
FROM test
GROUP BY a UNION
SELECT b, COUNT(b)
FROM test
WHERE a<>b
GROUP BY b
)tt
GROUP BY a

zeraba

Aug 8, 2017 via Android

就按照你的思路把 a b 先并到一起参考 3 楼一样的随便取一个最后 group by
伪代码
select case when a = b then a else a end hebing from test
union
select case when a <> b then b end hebing

SuperMild

Aug 13, 2017

select a, sum(num) as 'count' from
(select a, count(a) as num from AandB where a <> b group by a
union all
select b, count(b) from AandB where a <> b group by b
union all
select b, count(b) from AandB where a = b group by b)
group by a;