Mysql怎么查询一个字段同一内容时,另一字段各内容出现的次数 5C


字段A 字段B
1 2
1 3
1 3
2 4
3 1
3 1
统计结果是:
字段A 字段B=1 字段B=2 字段B=3 字段B=4
1 NULL 1 2 NULL

2 NULL NULL NULL 1
3 2 NULL NULL NULL
如果能将第一列的显示成字段A的值出现次数就更好了,如:
字段A 字段B=1 字段B=2 字段B=3 字段B=4
1(2) NULL 1 2 NULL

1(1) NULL NULL NULL 1
2(2) 2 NULL NULL NULL

小白求教。谢谢各位大佬

6个回答


 select A , B , count(*) from 表明  group by A,B

 SELECT
    A AS 字段A,
    SUM( CASE WHEN B=1 THEN 1 ELSE 0 END) AS 字段B1,
    SUM( CASE WHEN B=2 THEN 1 ELSE 0 END) AS 字段B2,
    SUM( CASE WHEN B=3 THEN 1 ELSE 0 END) AS 字段B3,
    SUM( CASE WHEN B=4 THEN 1 ELSE 0 END) AS 字段B4,
FROM TABLE_T
GROUP BY A
weixin_39428076
weixin_39428076 SELECT A AS 字段A, SUM( CASE WHEN B=1 THEN 1 ELSE 0 END) AS 字段B1, SUM( CASE WHEN B=2 THEN 1 ELSE 0 END) AS 字段B2, SUM( CASE WHEN B=3 THEN 1 ELSE 0 END) AS 字段B3, SUM( CASE WHEN B=4 THEN 1 ELSE 0 END) AS 字段B4, FROM TABLE_T GROUP BY A
一年多之前 回复
hh_xcn
hh_xcn 第一条是我看错题目了,后面两条的效果你可以各自看一下,不好意思哈我新注册的号不能回答只能评论T_T
一年多之前 回复
hh_xcn
hh_xcn SELECT CONCAT(A,'(',COUNT(A),')') AS 字段A, SUM( CASE WHEN B=1 THEN 1 ELSE 0 END) AS 字段B1, SUM( CASE WHEN B=2 THEN 1 ELSE 0 END) AS 字段B2, SUM( CASE WHEN B=3 THEN 1 ELSE 0 END) AS 字段B3, SUM( CASE WHEN B=4 THEN 1 ELSE 0 END) AS 字段B4 FROM test GROUP BY A,B
一年多之前 回复
hh_xcn
hh_xcn SELECT CONCAT(A,'(',COUNT(A),')') AS 字段A, SUM( CASE WHEN B=1 THEN 1 ELSE 0 END) AS 字段B1, SUM( CASE WHEN B=2 THEN 1 ELSE 0 END) AS 字段B2, SUM( CASE WHEN B=3 THEN 1 ELSE 0 END) AS 字段B3, SUM( CASE WHEN B=4 THEN 1 ELSE 0 END) AS 字段B4 FROM test GROUP BY A
一年多之前 回复
hh_xcn
hh_xcn SELECT A AS 字段A, SUM( CASE WHEN B=1 THEN 1 ELSE 0 END) AS 字段B1, SUM( CASE WHEN B=2 THEN 1 ELSE 0 END) AS 字段B2, SUM( CASE WHEN B=3 THEN 1 ELSE 0 END) AS 字段B3, SUM( CASE WHEN B=4 THEN 1 ELSE 0 END) AS 字段B4 FROM test GROUP BY A,B
一年多之前 回复
fsy351
fsy351 回复weixin_41780334: 我下面评论里写了
一年多之前 回复
weixin_41780334
weixin_41780334 大佬,是这样的,‘字段B4’后面多个逗号
一年多之前 回复
qq_40429151
进击的流氓 没毛病,建议采纳
一年多之前 回复
fsy351
fsy351 多了一个逗号哈
一年多之前 回复

select B from 表名 where A=1;

Z979797
Z979797 select A , B , count(*) from 表明 group by A,B
一年多之前 回复
nuanchenmi
nuanchenmi 回复weixin_38941602: 一次性统计所有可能,还是每个地方统计一个A=?
一年多之前 回复
weixin_38941602
weixin_38941602 是统计A值下B值出现的次数呢,而且不止显示A=1啊,2、3的时候呢
一年多之前 回复

对,count就可以

我记得是用group by 吧

select s.b, count(*) c from table as s group by s.b

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!