在三个表中，其中第一个表  回复  更多评论
  

抱歉！刚才测试一下本网站是否好用！现在出个让大家讨论的sql语句

a b c 三个字段有可能重复







数据如下:







a b c d e



---------- ---------- ---------- ----------- -----------



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



c c c 4 4



d d d 5 5



d d d 5 5







问题一: 现在要把三个字段有重复的数据提出来.



问题二: 合并表中的数据,比如



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



这五条纪录要合并成一条



a a a 5 10


  回复  更多评论
  

select t.name
from
(
select max(购物人) as name,count(购物人) as cnt
from chanpin
group by 商品名称
) t
where t.cnt >=2  回复  更多评论
  

select t.name,t.cnt from(
select userName as name,count(userName) as cnt
from exam1
group by userName)t
where t.cnt>=2  回复  更多评论
  

+---------+---------+----------+
| cp_name | cp_kind | cp_count |
+---------+---------+----------+
| A | a | 1 |
| A | c | 2 |
| A | t | 1 |
| B | t | 1 |
| B | a | 2 |
| C | d | 1 |
| D | c | 2 |
| C | d | 3 |
+---------+---------+----------+
上面两道答案明显错误，题目的意思是返回购买“两种或两种商品以上“的客户信息。
正确答案应该是：
select t.cp_name,t.cnt from (select cp_name,count(distinct cp_kind) as cnt from chanpin group by cp_name) t where t.cnt>=2;
  回复  更多评论
  

@畅畅畅
select * from T1
where customer_name in
(select customer_name
from T1 as b
group by customer_name
having count(distinct trade_name)>=2)  回复  更多评论
  

select * from sales as t1 where exists
(
select t2.customerName from sales as t2 where t1.customerName = t2.customerName
group by t2.customerName
having (count(t2.customerName) >= 2)
)  回复  更多评论
  

@畅畅畅
这个不是一句就够了么。
select cp_name,count(distinct cp_kind) cnt from tableName group by cp_name having cnt >= 2;  回复  更多评论
  

tableName:test
person gName scount
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
给出所有购入商品为两种或两种以上的购物人记录

select person,count(gName) from test GROUP BY person having count(gName)>=2

select * from (select person,count(gName) as gc from test GROUP BY person) as a where a.gc>=2   回复  更多评论
  

楼上回答的第一题都是错的。如果在加数据肯定不行。下边我把正确的写一下
注意了：第一题
select s.ren from (select distinct(shangpin), ren from tt group by ren , shangpin) s group by s.ren having count(s.ren)>=2
  回复  更多评论
  

For SQL SERVER:
declare @t3 table (buyer nvarchar(50),goods nvarchar(50),qty int)
insert into @t3 values('A','甲',2)
insert into @t3 values('A','甲',3)
insert into @t3 values('B','乙',4)
insert into @t3 values('C','丙',1)
insert into @t3 values('C','丙',2)
insert into @t3 values('A','丁',2)
insert into @t3 values('B','丙',5)
select * from @t3

select t1.buyer,t1.goods,SUM(t1.qty) from @t3 t1 inner join @t3 t2 on t1.buyer=t2.buyer and t1.goods <> t2.goods
group by t1.buyer,t1.goods  回复  更多评论