count与distinct的联合使⽤
遇到⼀个问题 :
题⽬:
=========================
⽤户名商品数量
A 甲 1
B ⼄ 2
C 丙 2
B 甲 1
A 丙 2
C 丙 1
写sql语句
要求查出购买商品两种类型以上(含)的⽤户集合
数据表中的数据如下:
⼀:
id name goodsname num
百合花照片
1 A JI 1
2 B JK 2
3 C J 3
4 B JKL 4
5 A JKLM 5
6 C J 6
同事们给出的意见是:
Sql代码
1. SELECT temp.name
2. FROM (短款指甲美甲图案
3. SELECT dsname) as nums,
龟兔赛跑绘本筹资活动4. tempone.name
5. FROM tempone
6. GROUP BY tempone.name
7. ) temp
8. WHERE temp.nums > 1
SELECT temp.name
FROM (
SELECT dsname) as nums,
tempone.name
FROM tempone
GROUP BY tempone.name
) temp
WHERE temp.nums > 1
查出来的结构如下
⼆
name
A
丰胸频道
Sql代码
1. SELECT temp.name
2. FROM (
3. SELECT count(dsname) as nums,
4. tempone.name
5. FROM tempone
6. GROUP BY tempone.name
7. ) temp
8. WHERE temp.nums > 1
SELECT temp.name
FROM (
SELECT count(dsname) as nums, tempone.name
FROM tempone
GROUP BY tempone.name
) temp
WHERE temp.nums > 1
查出来的结构如下
屑读音三
name
A
B
如三表⽰符合了该要求
还有⼀种查询⽅式是
lect temp.name
from temp
group by temp.name
一场疫情一场感悟
having COUNT(distinct goodsname) > 1
也可以达到相同的效果尘卷风
主要总结count 与 distinct的联合使⽤
===
总结才是记住东西的根本
